如何比较两个集?先谢谢了
比如有表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的则是不满足的。如何实现？
[解决办法]
--测试环境
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
13
[解决办法]
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)
楼上的好像理解错题意了吧？？
[解决办法]
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 '
[解决办法]
--可以改为:
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)
