求个答案! sql语句
如果DATETIME 3列中包含1900-01-01 则查询不显示
如果DATETIME 3列中不包含1900-01-01 则显示DATETIME 最大值
ID type DATETIME COUNT
1 10 2013-12-08 11:59:42.640 2
2 10 2013-12-08 09:20:10.450 2
3 10 1900-01-01 00:00:00.000 0
[解决办法]
是3行吧，是否按type分组
select * from table a
where not exists(select * from table where type=a.type and [datatime]='1900-01-01')
and not exists(select * from table where type=a.type and [datetime]>a.[datetime])
create table ht
(ID int,[type] int,[DATETIME] datetime,[count] int)
-- 如果DATETIME 3列中包含1900-01-01 则查询不显示
truncate table ht
insert into ht
select 1,10,'2013-12-08 11:59:42.640',2 union all
select 2,10,'2013-12-08 09:20:10.450',2 union all
select 3,10,'1900-01-01 00:00:00.000',0
select top 1 [DATETIME] from ht
where not exists(select 1 from ht
where convert(varchar,[DATETIME],23)='1900-01-01')
order by [DATETIME] desc
/*
DATETIME
-----------------------
(0 row(s) affected)
*/
-- 如果DATETIME 3列中不包含1900-01-01 则显示DATETIME 最大值
truncate table ht
insert into ht
select 1,10,'2013-12-08 11:59:42.640',2 union all
select 2,10,'2013-12-08 09:20:10.450',2 union all
select 3,10,'1985-01-01 00:00:00.000',0
select top 1 [DATETIME] from ht
where not exists(select 1 from ht
where convert(varchar,[DATETIME],23)='1900-01-01')
order by [DATETIME] desc
/*
DATETIME
-----------------------
2013-12-08 11:59:42.640
(1 row(s) affected)
*/
