有如下所示的表,要求编写一条查询得到每个人所对应的Time1与Time2中最大的时间,如果有NULL值,则直接返回NULL.declare @t table(id int, name varchar(10), Time1 datetime, Time2 datetime)
insert @t select 1,'AA','2008-5-10','2009-1-1'
insert @t select 2,'AA','2008-11-5',null
insert @t select 3,'BB','2008-5-10','2009-2-14'
insert @t select 4,'BB','2008-8-23','2009-1-5'
insert @t select 5,'BB','2008-9-14','2009-4-1'
insert @t select 6,'CC','2009-2-22',null
insert @t select 7,'CC','2008-10-10','2009-1-10'
/*
id name Time1 Time2
----------- ---------- ----------------------- -----------------------
2 AA 2008-11-05 00:00:00.000 NULL
5 BB 2008-09-14 00:00:00.000 2009-04-01 00:00:00.000
6 CC 2009-02-22 00:00:00.000 NULL(3 行受影响)
*/
主要是那个NULL值的判断,我想了很久都没有解决,用聚合函数统计会被忽略掉这个,希望有哪位高手写个正确的答案看看,谢谢先了
insert @t select 1,'AA','2008-5-10','2009-1-1'
insert @t select 2,'AA','2008-11-5',null
insert @t select 3,'BB','2008-5-10','2009-2-14'
insert @t select 4,'BB','2008-8-23','2009-1-5'
insert @t select 5,'BB','2008-9-14','2009-4-1'
insert @t select 6,'CC','2009-2-22',null
insert @t select 7,'CC','2008-10-10','2009-1-10'
/*
id name Time1 Time2
----------- ---------- ----------------------- -----------------------
2 AA 2008-11-05 00:00:00.000 NULL
5 BB 2008-09-14 00:00:00.000 2009-04-01 00:00:00.000
6 CC 2009-02-22 00:00:00.000 NULL(3 行受影响)
*/
主要是那个NULL值的判断,我想了很久都没有解决,用聚合函数统计会被忽略掉这个,希望有哪位高手写个正确的答案看看,谢谢先了
WHERE NOT EXISTS (SELECT * FROM @t B WHERE A.name = B.name AND A.Time1 < B.Time1)