有一个表,存在两个字段 uid 和 bid 如:
1 1
1 2
3 1
1 4
1 5
2 5
4 4
5 1
6 1
7 1
8 1
9 1求 输入多个uid 都相同的bid的值如输入 uid = 1 和 5
那么输出的 bid = 1其他类似,谢谢
1 1
1 2
3 1
1 4
1 5
2 5
4 4
5 1
6 1
7 1
8 1
9 1求 输入多个uid 都相同的bid的值如输入 uid = 1 和 5
那么输出的 bid = 1其他类似,谢谢
select bid from
(
select distinct * from tb where uid = 1
union all
select distinct * from tb where uid = 5
) t
group by bid having count(1) = 2 --这里的2表示你输入了多少个uid
insert into tb select 1,1
insert into tb select 1,2
insert into tb select 3,1
insert into tb select 1,4
insert into tb select 1,5
insert into tb select 2,5
insert into tb select 4,4
insert into tb select 5,1
insert into tb select 6,1
insert into tb select 7,1
insert into tb select 8,1
insert into tb select 9,1
go
declare @uidlist varchar(20)
set @uidlist='1,5'
;with cte as(
select substring(@uidlist,number,charindex(',',@uidlist+',',number+1)-number)s from master..spt_values
where type='p' and number<=len(@uidlist) and substring(@uidlist,number,1)<>',' and substring(','+@uidlist,number,1)=','
)
select a.uid from tb a inner join cte b on a.bid=b.s group by a.uid having COUNT(distinct a.bid)>1
/*
uid
-----------
1(1 行受影响)*/
go
drop table tb