27,579
社区成员
发帖
与我相关
我的任务
分享
declare @tb table(sid int, code varchar(50), name varchar(50))
insert into @tb (sid, code, name)
select 1, '001', '1' union
select 1, '002', '2' union
select 2, '005','2' union
select 2, '006','3' union
select 2, '007','2'
declare @main table(sid int,title varchar(50))
insert into @main (sid, title )
select 1, '第一条数据' union
select 2, '第二条数据'
declare @main_z table(sid int,t_sid int, code_1 varchar(50), name_1 varchar(50))
insert into @main_z (sid, t_sid, code_1, name_1 )
select 1,1,'001','1' union
select 2,1,'002','2' union
select 3,1,'005','2' union
select 4,2,'001','1' union
select 5,2,'002','2' union
select 6,2,'005','2' union
select 7,2,'006','3' union
select 8,2,'007','2' union
select 9,3,'001','1' union
select 10,3,'005','2' union
select 11,3,'006','3'
select * from @tb
select * from @main
select * from @main_z
/*
--构造数据 若有问题请见谅
--@main a 主表 @main_z b子表 关联字段 a.sid=b.t_sid
--取@tb code name 完全包含在@main_z 表中的@main记录
--@tb表 sid=1 应该取到的
sid title
1 第一条数据
2 第二条数据
--@tb表 sid=2 应该取到的
sid title
2 第一条数据
*/
declare @tb table(sid int, code varchar(50), name varchar(50))
insert into @tb (sid, code, name)
select 1, '001', '1' union
select 1, '002', '2' union
select 2, '005','2' union
select 2, '006','3' union
select 2, '007','2'
declare @main table(sid int,title nvarchar(50))
insert into @main (sid, title )
select 1,N'第一条数据' union
select 2, N'第二条数据'
declare @main_z table(sid int,t_sid int, code_1 varchar(50), name_1 varchar(50))
insert into @main_z (sid, t_sid, code_1, name_1 )
select 1,1,'001','1' union
select 2,1,'002','2' union
select 3,1,'005','2' union
select 4,2,'001','1' union
select 5,2,'002','2' union
select 6,2,'005','2' union
select 7,2,'006','3' union
select 8,2,'007','2' union
select 9,3,'001','1' union
select 10,3,'005','2' union
select 11,3,'006','3'
DECLARE @id INT
SET @id=2
SELECT *
FROM @main
WHERE sid IN (
select t_sid
from @main_z b
where exists( select * FROM @tb a where a.code= b.code_1 and a.name= b.name_1 AND a.sid=@id )
group BY t_sid
HAVING COUNT(1)>=(SELECT COUNT(1) FROM @tb WHERE sid=@id))
/*
sid title
----------- --------------------------------------------------
2 第二条数据
*/
--@tb sid=2
select * from @main_z b
where exists( select * from @tb a where a.code= b.code_1 and a.name= b.name_1 and a.sid=2)
--数据如下
sid t_sid code_1 name_1
3 1 005 2
6 2 005 2
7 2 006 3
8 2 007 2
10 3 005 2
11 3 006 3
--全部包含的只有 t_sid=2 ,t_sid=1,3只包含部分记录