如何用一个LEFT JOIN查询忽略重复记录？
用户表user结构如下：
id(int,主键)
username(nvarchar)
数据如下：
id username
1 张三
2 李四
3 王五
每日生产记录表detail结构如下：
id(int,主键)
userid(int,关联user表的id)
posttime(smalldatetime)
content(nvarchar)
数据如下：
id userid posttime content
1 1 2007-1-22 test1
2 2 2007-1-22 test2
3 1 2007-1-22 test3
==========================================
现在的要求是读出用户每天的记录，表detail中每天每用户可能会有多条记录，但结果只显示1条（暂不考虑合理性吧。。。）
例如查询显示的结果如下：
username posttime content
张三 2007-1-22 test1
李四 2007-1-22 test2
王五 NULL NULL
我现在的语句是：
SELECT a.username, b.posttime, b.content FROM user a LEFT JOIN detail b
ON a.id = b.userid WHERE b.id IN(SELECT id FROM detail a WHERE id IN(SELECT MAX(id) FROM detail b WHERE a.userid = b.userid))
结果如下：
==============================
username posttime content
张三 2007-1-22 test3
李四 2007-1-22 test2
==============================
上面的查询语句可以忽略重复记录，但是也去掉了其他在detail表中没有记录的用户
请问大家按照目前的情况应该怎么写查询语句？
[解决办法]
select a.username,b.posttime,b.content
from user a left join
(select n.* from detail n where n.id in
(select m.id from (select min(id) id,userid
from detail
group by userid) m) b on a.id=b.userid
[解决办法]
create table #user
(id int,
username nvarchar(50)
)
create table #detail
(id int,
userid int,
posttime smalldatetime,
content nvarchar(50)
)
insert into #user
select '1 ', '张三 ' union all select '2 ', '李四 ' union all select '3 ', '王五 '
select * from #user
insert into #detail
select '1 ', '1 ', '2007-1-22 ', 'test1 ' union all select '2 ', '2 ', '2007-1-22 ', 'test2 ' union all select '3 ', '1 ', '2007-1-22 ', 'test3 '
select * from #detail
select a.username,b.posttime,b.content
from #user a left join
(select * from #detail where id in (select id from (select min(id) id,userid from #detail group by userid)tt) ) b
on a.id=b.userid
--------------------
张三 2007-01-22 00:00:00 test1
李四 2007-01-22 00:00:00 test2
王五 NULL NULL
[解决办法]
把我刚发的那个改成这样，用exists替代in，应该更快一些
select a.username,b.posttime,b.content
from #user a left join
(select * from #detail t1
where exists (select id from (select min(id) id,userid from #detail group by userid) t2 where t2.id=t1.id) ) b
on a.id=b.userid
[解决办法]
SELECT a.username, b.posttime, b.content FROM user a LEFT JOIN
(
select * from detail t where not exists(select * from detail where userid=t.userid and id <t.id and DATEDIFF(day,posttime,t.postime)=0)
)
b
ON a.id = b.userid
order by a.username
[解决办法]
楼主的原来的sql语句.
我现在的语句是：
SELECT a.username, b.posttime, b.content FROM user a LEFT JOIN detail b
ON a.id = b.userid WHERE b.id IN(SELECT id FROM detail a WHERE id IN(SELECT MAX(id) FROM detail b WHERE a.userid = b.userid))
改为
SELECT a.username, b.posttime, b.content FROM [user] a LEFT JOIN
(SELECT * FROM detail a WHERE id IN (SELECT MAX(id) FROM detail b WHERE a.userid = b.userid)) b
on a.id=b.userid
