34,576
社区成员
发帖
与我相关
我的任务
分享
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,#
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,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
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