# 一个合并查询函数或存储过程的实现

www.myexceptions.net  网友分享于：2013-01-12  浏览：4次

ID1     ID2
1         2
1         3
1         4
1         5
2         2
2         3
2         5
3         2
3         4
3         5
ID1和ID2共同构成了表A的主键，此时有需要查询得到ID2仅等于2，3，5时候的ID1，在此例中应该只有ID1=2的纪录被选出来。

ID1       ID2
1           2,3,4,5
2           2,3,5
3           2,4,5

------解决方案--------------------
----创建测试数据
declare @t table(ID1 int, ID2 int)
insert @t
select 1, 2 union all
select 1, 3 union all
select 1, 4 union all
select 1, 5 union all
select 2, 2 union all
select 2, 3 union all
select 2, 5 union all
select 3, 2 union all
select 3, 4 union all
select 3, 5

----查询
declare @idlist varchar(20)
set @idlist = '2,3,5 '
select DISTINCT ID1 from @t as a where not exists(select 1 from @t where
ID1 = a.ID1 and charindex( ', ' + rtrim(ID2) + ', ', ', ' + @idlist + ', ') = 0)

/*结果
ID1
2
*/
------解决方案--------------------
----下面修改过的,选出id2 刚好取值 2 3 5 (不多也不少)的id1值
----创建测试数据
declare @t table(ID1 int, ID2 int)
insert @t
select 1, 2 union all
select 1, 3 union all
select 1, 4 union all
select 1, 5 union all
select 2, 2 union all
select 2, 3 union all
select 2, 5 union all
select 3, 2 union all
select 3, 5 union all
select 4, 2 union all
select 4, 3 union all
select 4, 4 union all
select 4, 5
----查询
declare @idlist varchar(20)
set @idlist = '2,3,5 '
select DISTINCT a.ID1 from @t a join (select id1,count(*) id2count from (select distinct id1,id2 from @t) t group by id1) b on a.id1=b.id1 and b.id2count = len(replace(@idlist, ', ', ' '))
and not exists(select 1 from @t where ID1 = a.ID1 and charindex( ', ' + rtrim(ID2) + ', ', ', ' + @idlist + ', ') = 0)

/*结果
ID1
2
*/