日期:2014-05-17 浏览次数:20535 次
select id,underid,jhwgtime
from fb_clunderinfo t
where not exists (select 1 from fb_clunderinfo where xdno=t.xdno and [day]=0)
use Tempdb
go
--> -->
if not object_id(N'fb_clunderinfo') is null
drop table fb_clunderinfo
Go
Create table fb_clunderinfo([id] int,[xdno] nvarchar(28),[day] int)
Insert fb_clunderinfo
select 185,N'0000000A29-201111280520-1006',0 union all
select 186,N'0000000A29-201111280520-1006',21 union all
select 187,N'0000000A29-201111280520-1006',0 union all
select 188,N'0000000A29-201111280520-1006',0 union all
select 189,N'0000000A34-201111280521-988',45 union all
select 190,N'0000000A34-201111280521-988',0 union all
select 191,N'0000000A34-201111280521-988',0 union all
select 192,N'0000000A31-201111280522-1036',37 union all
select 193,N'0000000A31-201111280522-1036',23 union all
select 194,N'0000000A31-201111280522-1036',0 union all
select 195,N'0000000A33-201111280523-988',0 union all
select 196,N'0000000A33-201111280523-988',0 union all
select 197,N'0000000A33-201111280523-988',0 union all
select 198,N'0000000A32-201111280524-1036',9 union all
select 199,N'0000000A32-201111280524-1036',10
Go
select id,[xdno],[day] from fb_clunderinfo AS a WHERE EXISTS(SELECT 1 FROM fb_clunderinfo WHERE [xdno]=a.[xdno] AND id<>a.id AND [day]>0) AND [DAY]>0
/*
id xdno day
192 0000000A31-201111280522-1036 37
193 0000000A31-201111280522-1036 23
198 0000000A32-201111280524-1036 9
199 0000000A32-201111280524-1036 10
*/
----------------------------
-- Author :fredrickhu(小F,向高手学习)