一个表
id 对应多个时间
id sex ssTime
1 2 null
1 3 2010-01-02
1 5 2010-02-03
id ssTime
结果 1 nullid sex ssTime
1 2 2010-01-04
1 3 2010-01-02
1 5 2010-02-03
id ssTime
结果 1 2010-01-04
ssTime 只要一个是空就是空,不为空就是取最大的时间
这个SQL 文怎么写
id 对应多个时间
id sex ssTime
1 2 null
1 3 2010-01-02
1 5 2010-02-03
id ssTime
结果 1 nullid sex ssTime
1 2 2010-01-04
1 3 2010-01-02
1 5 2010-02-03
id ssTime
结果 1 2010-01-04
ssTime 只要一个是空就是空,不为空就是取最大的时间
这个SQL 文怎么写
select id,(case when exists(select 1 from tb where id = t.id and ssTime is null) then null else max(ssTime) end)ssTime
from tb t
group by id
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:POT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'POT')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE POT
GO---->建表
create table POT([id] int,[sex] int,[ssTime] datetime)
insert POT
select 1,2,'2010-01-04' union all
select 1,3,'2010-01-02' union all
select 1,5,'2010-02-03'
GO--> 查询结果
SELECT top 1 * FROM POT order by case when ssTime IS null then 1 else 0 end desc ,ssTime desc--> 删除表格
--DROP TABLE POT
select id,ssTime=(select top 1 ssTime from tb where id=a.id order by ssTime desc)
from tb a where id not in (select id from tb where ssTime is null)
from tb a where id not in (select id from tb where ssTime is null)
union all
select distinct id,ssTime from tb where ssTime is null
哪个 高人能稍微说下,order by 1 ,order by 0 和解
搞定 谢谢大家