22,209
社区成员
发帖
与我相关
我的任务
分享
create table #temp
(ysid nvarchar(10),
cfid nvarchar(10),
sourceid nvarchar(10))
insert into #temp values('101','1001','201')
insert into #temp values('101','1001','202')
insert into #temp values('101','1001','203')
insert into #temp values('101','1002','201')
insert into #temp values('101','1002','202')
insert into #temp values('101','1002','203')
insert into #temp values('101','1003','201')
insert into #temp values('101','1003','203')
insert into #temp values('101','1004','202')
select * from #temp
DECLARE @t TABLE(ysid NVARCHAR(10), cfid NVARCHAR(10), sourceid NVARCHAR(10));
INSERT INTO @t VALUES('101', '1001', '201');
INSERT INTO @t VALUES('101', '1001', '202');
INSERT INTO @t VALUES('101', '1001', '203');
INSERT INTO @t VALUES('101', '1002', '201');
INSERT INTO @t VALUES('101', '1002', '202');
INSERT INTO @t VALUES('101', '1002', '203');
INSERT INTO @t VALUES('101', '1003', '201');
INSERT INTO @t VALUES('101', '1003', '203');
INSERT INTO @t VALUES('101', '1004', '202');
WITH data AS (
SELECT DISTINCT tt.ysid, tt.cfid,
(SELECT sourceid
FROM @t t
WHERE t.ysid=tt.ysid AND t.cfid=tt.cfid
ORDER BY t.sourceid
FOR XML PATH('')) sourceid
FROM @t tt)
SELECT dd.ysid, dd.cfid, dd.sourceid
FROM data dd
WHERE (SELECT COUNT(*) FROM data d WHERE d.sourceid=dd.sourceid AND d.ysid = dd.ysid)>1
create table #temp
(ysid nvarchar(10),
cfid nvarchar(10),
sourceid nvarchar(10))
insert into #temp values('101','1001','201')
insert into #temp values('101','1001','202')
insert into #temp values('101','1001','203')
insert into #temp values('101','1002','201')
insert into #temp values('101','1002','202')
insert into #temp values('101','1002','203')
insert into #temp values('101','1003','201')
insert into #temp values('101','1003','203')
insert into #temp values('101','1004','202')
;with t as (select ysid,cfid,sourceid,count(1)over(partition by ysid,cfid)ct from #temp)
select distinct t1.ysid,t1.cfid from t t1
left join t t2
on (t1.ysid <>t2.ysid or t1.cfid <>t2.cfid) and t1.ct =t2.ct and t1.sourceid=t2.sourceid
group by t1.ysid,t1.cfid,t2.ysid,t2.cfid,t1.ct
having count(t2.sourceid)=t1.ct
drop table #temp
/*
ysid cfid
101 1001
101 1002
*/