-->猜一个 select distinct tid from @test t where exists(select 1 from @test where t.sid<>sid and t.tid=tid)
std为等于1和等于2 的参数???
表一 id tid sid 1 1 1 2 2 1 3 3 1 4 9 1 5 1 2 6 2 2 7 3 2 8 4 2 表二 id cpid 1 a 2 b 3 c 4 d 9 e sid 为参数,参数为1时,查询结果为 cpid a b c e 参数为1,2时,查询结果为 tid a b c 用sql 怎么实现?
declare @t1 table(id int,tid int, sid varchar(1)) insert into @t1 select 1, 1, 1 union all select 2, 2, 1 union all select 3, 3, 1 union all select 4, 9, 1 union all select 5, 1, 2 union all select 6, 2, 2 union all select 7, 3, 2 union all select 8, 4, 2 declare @t2 table(id int,cpid varchar(1)) insert into @t2 select 1, 'a' union all select 2, 'b' union all select 3, 'c' union all select 4, 'd' union all select 9, 'e' declare @sid varchar(20) set @sid='1,2' select cpid from ( select b.cpid,count(1) cnt,case when charindex(',',@sid)>0 then 2 else 1 end flag from @t1 a,@t2 b where charindex(sid+',',@sid+',')>0 and a.tid=b.id group by b.cpid )t where (t.flag=2 and t.cnt>1) or (t.flag=1 and t.cnt=1)
-->猜一个
select distinct tid from @test t
where exists(select 1 from @test where t.sid<>sid and t.tid=tid)
id tid sid
1 1 1
2 2 1
3 3 1
4 9 1
5 1 2
6 2 2
7 3 2
8 4 2
表二
id cpid
1 a
2 b
3 c
4 d
9 e
sid 为参数,参数为1时,查询结果为
cpid
a
b
c
e
参数为1,2时,查询结果为
tid
a
b
c
用sql 怎么实现?
declare @t1 table(id int,tid int, sid varchar(1))
insert into @t1
select 1, 1, 1 union all
select 2, 2, 1 union all
select 3, 3, 1 union all
select 4, 9, 1 union all
select 5, 1, 2 union all
select 6, 2, 2 union all
select 7, 3, 2 union all
select 8, 4, 2
declare @t2 table(id int,cpid varchar(1))
insert into @t2
select 1, 'a' union all
select 2, 'b' union all
select 3, 'c' union all
select 4, 'd' union all
select 9, 'e'
declare @sid varchar(20)
set @sid='1,2'
select cpid from
(
select b.cpid,count(1) cnt,case when charindex(',',@sid)>0 then 2 else 1 end flag from @t1 a,@t2 b
where charindex(sid+',',@sid+',')>0 and a.tid=b.id
group by b.cpid
)t
where (t.flag=2 and t.cnt>1) or (t.flag=1 and t.cnt=1)