数据集:
ID Name Month Date1 Date2
1 A 2 2004-01-01 null
2 A 3 2004-01-01 2004-02-01
3 B 2 2004-01-01 2004-03-01
4 B 3 2004-01-01 2004-05-01
5 B 4 2004-01-01 2004-06-01要求先检查Date2,如果Date2不为空,则取其最大值的那条记录,如果为空则取Date1的日期值。相同Name的Date1也相同结果如下:
ID Name Month Date1 Date2
2 A 3 2004-01-01 2004-02-01
5 B 4 2004-01-01 2004-06-01查询语句怎么写???
ID Name Month Date1 Date2
1 A 2 2004-01-01 null
2 A 3 2004-01-01 2004-02-01
3 B 2 2004-01-01 2004-03-01
4 B 3 2004-01-01 2004-05-01
5 B 4 2004-01-01 2004-06-01要求先检查Date2,如果Date2不为空,则取其最大值的那条记录,如果为空则取Date1的日期值。相同Name的Date1也相同结果如下:
ID Name Month Date1 Date2
2 A 3 2004-01-01 2004-02-01
5 B 4 2004-01-01 2004-06-01查询语句怎么写???
ID Name Month Date1 Date2
1 A 2 2004-01-01 2004-01-01
5 B 4 2004-01-01 2004-06-01
才对,不是吗?
select a.ID, a.sname, a.smonth, a.date1, [date2]=a.date1 from (select * from tbl where date2 is null) a union select b.* from
(select top 1 * from tbl where not date2 is null order by date2 desc) b
下面是结果:
1 A 2 2004-01-01 2004-01-01
5 B 4 2004-01-01 2004-06-01
from xxx a,
(select NAME,max(ISNULL(DATE2,DATE1)) as date
from xxx group by NAME) b
where ISNULL(a.date2,a.date1)=b.date结果正确,你试试