我有一个table1 只有两个字段sid、uid,又有一组uid比如1,2
现在我要在表中找出符合条件的sid:要求这些个sid必须同时要对应有上面的那组uid.例如
sid uid
1 1
1 2
1 3
2 1
2 3
3 1
3 2
能选出sid=3的结果来。
谁能给个思路?
现在我要在表中找出符合条件的sid:要求这些个sid必须同时要对应有上面的那组uid.例如
sid uid
1 1
1 2
1 3
2 1
2 3
3 1
3 2
能选出sid=3的结果来。
谁能给个思路?
@sid int)
returns varchar(8000)
as
begin
declare @str varchar(8000) select @str = ''
select @str = @str + ',' + convert(varchar(20), uid)
from table1 where sid = @sid order by uid
select @str = right(@str, len(@str) - 1)
return(@str)
End
goselect A.sid
from (select distinct sid, dbo.uf_comb(sid) uid from table1) A,
table2 B
where B.uid = A.uid
其中table2中有字段uid,数据格式为1,2,3
(
sid int,
uid int
)insert into table1 select 1, 1
insert into table1 select 1, 2
insert into table1 select 1, 3
insert into table1 select 2, 1
insert into table1 select 2, 3
insert into table1 select 3, 1
insert into table1 select 3, 2--语句
select sid from
(select * from table1 where '1,2' like '%'+cast(uid as varchar)+'%'
)t1
group by sid
having count(1) = len(replace('1,2',',',''))--结果
1
3
没说清楚不好意思
(
sid int,
uid int
)insert into @t select 1, 1
insert into @t select 1, 2
insert into @t select 1, 3
insert into @t select 2, 1
insert into @t select 2, 3
insert into @t select 3, 1
insert into @t select 3, 2select distinct a.sid from @t a
inner join @t b
on a.sid=b.sid
where a.uid in(1,2) and b.uid=case a.uid when 1 then 2 else 1 end
这时我想要的结果是sid=1。
create table table1
(
sid int,
uid int
)insert into table1 select 1, 1
insert into table1 select 1, 2
insert into table1 select 1, 3
insert into table1 select 2, 1
insert into table1 select 2, 3
insert into table1 select 3, 1
insert into table1 select 3, 2
insert into table1 select 5, 1
GO
Declare @S Varchar(100)
Set @S='1,2'Select sid From table1 A
Group By sid
Having Count(Distinct uid)=Len(@S)-Len(Replace(@S,',',''))+1
And Not Exists (Select 1 From table1 Where sid=A.sid And CharIndex(','+Rtrim(uid)+',',','+@S+',')=0)
Go
Drop Table table1
/*
sid
3
*/