如何比较两个集?先谢谢了

[seusoftware]

比如有表a，
ID value
1,A
1,B
1,C
2,A
2,B
2,C
2,D
……省略N行

有表b
ID value
3,B
3,A
3,C
……省略N行
比如现在用到了b表中ID为3的所有行，得到了B,A,C的三个Value，那根据这些值想从a表中找出只有A,B,C或B,A,C（等等，也就是不管先后顺序只要和那三个value相等就行）的值的ID，比如a表中的ID为1的行就是满足的，ID为2的则是不满足的。如何实现？

[jacobsan]

--测试环境
create table a(ID int, [value] varchar(20))
insert into a select 1,'A' union all
select 1,'B' union all
select 1,'C' union all
select 2,'A' union all
select 2,'B' union all
select 2,'C' union all
select 2,'D'


create table b(ID int, [value] varchar(20))
insert into b select 3,'B' union all
select 3,'A' union all
select 3,'C'

--建立函数
create function combine(@id int,@tn varchar(20))
returns varchar(8000)
as
begin
declare @rs varchar(8000)
set @rs=''
if @tn='a'
select @rs=@rs+[value] from a where id=@id order by [value]
else
select @rs=@rs+[value] from b where id=@id order by [value]
return @rs
end

--查询
select aid=ta.id,bid=tb.id from (select id,dbo.combine(id,'a') as rs from a group by id) ta
,(select id,dbo.combine(id,'b') as rs from b group by id) tb
where ta.rs=tb.rs


--
aid bid
1 3

[冷箫轻笛]

select id from a where id in
(
select id from a where [value] in
(select [value] from b)
)
group by id having count(1) = (select count(*) from b)

楼上的好像理解错题意了吧？？

[冷箫轻笛]

--借用楼上的数据 ：）
create table a(ID int, [value] varchar(20))
insert into a select 1,'A' union all
select 1,'B' union all
select 1,'C' union all
select 2,'A' union all
select 2,'B' union all
select 2,'C' union all
select 2,'D'


create table b(ID int, [value] varchar(20))
insert into b select 3,'B' union all
select 3,'A' union all
select 3,'C'


select id from a where id in
(
select id from a where [value] in
(select [value] from b)
)
group by id having count(1) = (select count(*) from b)

--结果
id
-----------
1

（所影响的行数为 1 行）

[paoluo]

coolingpipe(冷箫轻笛)的語句有問題，你測試下這個看看


create table a(ID int, [value] varchar(20))
insert into a select 1,'A' union all
select 1,'B' union all
select 1,'D' union all
select 2,'A' union all
select 2,'B' union all
select 2,'C' union all
select 2,'D'

[WangZWang]

--可以改为:
Select ID from a as t where Value in (
Select Value from b)
group by ID having count(*)=(Select count(*) from b) and
count(*)=(Select count(*) from a where ID=t.ID)

[jacobsan]

恩？他要求的不是个数相等，是每项相等阿

[jacobsan]

我坚持我是对的。。。。

[seusoftware]

其实用IN的话，我就觉得效率上会有影响，当然如果表不太的话还行。
说简单点，就是前台现在传来三个值:A,B,C或者B，A，C（不分先后，反正就是这三个值），要从a表中得出value是且只是这三个值的ID(注意了，是且只是这三个值的ID)

[seusoftware]

先顶一下，要不然找不到了

[seusoftware]

也就是说现在只知道有这三个值，怎样找出他们的ID，而且这个ID只对应着这三个值

[冷箫轻笛]

paoluo(一天到晚游泳的鱼) ( ) 信誉：100 Blog 2007-3-5 17:04:49 得分: 20

coolingpipe(冷箫轻笛)的語句有問題，你測試下這個看看
================================================

确实有问题
改一下：
select id from a where id in
(
select id from a where [value] in
(select [value] from b)
)
and [value] in (select distinct [value] from b)
group by id having count(1) = (select count(*) from b)