34,590
社区成员
发帖
与我相关
我的任务
分享
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 * FROM fb_clunderinfo AS a WHERE [day]>0 AND EXISTS(SELECT * FROM fb_clunderinfo WHERE [xdno]=a.[xdno] HAVING COUNT(DISTINCT SIGN([day]))=1)
/*
id xdno day
198 0000000A32-201111280524-1036 9
199 0000000A32-201111280524-1036 10
*/
select t1.* from
(select id,xdno ,day,count(xdno) as c_no
from fb_clunderinfo
where day<>0
group by xdno ) t1
where c_no=(select count(xdno) from fb_clunderinfo a where a.xdno=t1.xdno group by xdno)
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 t
where not exists (select 1 from fb_clunderinfo where xdno=t.xdno and [day]=0)
drop table fb_clunderinfo
/*********************
id xdno day
----------- ---------------------------- -----------
198 0000000a32-201111280524-1036 9
199 0000000a32-201111280524-1036 10
(2 行受影响)
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
*/
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
*/
select id,underid,jhwgtime from fb_clunderinfo AS a WHERE EXISTS(SELECT 1 FROM fb_clunderinfo WHERE underid=a.underid AND id=<>a.id AND [day]>0) AND [DAY]>0