关于取出值组合等于某个值的查询语句

[cqnucsmoon]

我有一个表，表中数据如下：
ID VAL
A 10
B 20
C 22
D 12
其中ID的值没有重复有，有没有可能得到这样的数据：将VAL值组合后得到32的数据重新生成一个表，如下：

ZHID ID VAL
1 A 10
1 C 22
2 B 20
2 D 12

A和C的VAL值相加为32，分到组1中去
B和D的VAL值相加为32，分到组2中去

谢谢大家

[快溜]

with cte as
(select row_number() over(order by getdate()) no,
   a.id as id1,a.val as val1,b.id as id2,b.val as val
  from tb a,tb b where a.val=32-b.val)
select no,id1 as id,val1 as val from cte
union
select no,id2,val2 from cte

[AcHerat]

你两个值可以用1楼，多个组合。。。

[cqnucsmoon]

自已顶下。

[cqnucsmoon]

偶使用的是SQL SERVER 2000

[-晴天]

create table tb(ID varchar(10),VAL int)
insert into tb select 'A',10
insert into tb select 'B',20
insert into tb select 'C',22
insert into tb select 'D',12
go
--如果是要求由两列的和获取32,则
select a.id,a.val,b.id,b.val
from tb a,tb b
where a.val+b.val=32 and a.val<b.val
/*
id         val         id         val
---------- ----------- ---------- -----------
D          12          B          20
A          10          C          22

(2 行受影响)

*/
go
drop table tb

[-晴天]

这样横排好像更能显示是在同一组吧.如果要竖排:

create table tb(ID varchar(10),VAL int)
insert into tb select 'A',10
insert into tb select 'B',20
insert into tb select 'C',22
insert into tb select 'D',12
go
--如果是要求由两列的和获取32,则
select a.id,a.val,b.id id1,b.val v1,n=identity(int,1,1) into #
from tb a,tb b
where a.val+b.val=32 and a.val<b.val
select n,id,val from #
union all
select n,id1,v1 from # order by n
/*
n           id         val
----------- ---------- -----------
1           D          12
1           B          20
2           C          22
2           A          10

(4 行受影响)

*/
go
drop table tb,#

[-晴天]

这样,就和你给的结果完全一样了:

create table tb(ID varchar(10),VAL int)
insert into tb select 'A',10
insert into tb select 'B',20
insert into tb select 'C',22
insert into tb select 'D',12
go
--如果是要求由两列的和获取32,则
select a.id,a.val,b.id id1,b.val v1,n=identity(int,1,1) into #
from tb a,tb b
where a.val+b.val=32 and a.val<b.val
order by a.id
select n,id,val from #
union all
select n,id1,v1 from # order by n
/*
n           id         val
----------- ---------- -----------
1           A          10
1           C          22
2           B          20
2           D          12

(4 行受影响)

*/
go
drop table tb,#