比如有一张表
字段 A B
苹果 梨
香蕉 桃子
香蕉 西瓜
苹果 水果
梨 苹果
西瓜 香蕉
我想实现的是找出A、B两字段交叉相等的记录,并将交叉相等的两记录相邻显示出来,如下所示:
A B
苹果 梨
梨 苹果
香蕉 西瓜
西瓜 香蕉
该怎么写sql语句?
字段 A B
苹果 梨
香蕉 桃子
香蕉 西瓜
苹果 水果
梨 苹果
西瓜 香蕉
我想实现的是找出A、B两字段交叉相等的记录,并将交叉相等的两记录相邻显示出来,如下所示:
A B
苹果 梨
梨 苹果
香蕉 西瓜
西瓜 香蕉
该怎么写sql语句?
with tb as (
select a=1,b=2 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 1,3
)
select a.* from tb a,tb b where a.a=b.b and a.b=b.a
select row=row_number()over(order by getdate()),a.a a1,a.b b1,b.a a2,b.b b2 from tb a join tb b on a.a=b.b and a.b=b.a and a.a>b.a
)
select a,b from
(
select row,a1 as a,b1 as b from tc
union all
select row,a2 as a,b2 as b from tc
)t order by row
with tb(a ,b ) as (
select '1','2' union all
select '唉','压' union all
select '2','1' union all
select '5','1' union all
select '3','1' union all
select '4','1' union all
select '1','3' union all
select '1','5' union all
select '压','唉'
) select a.* from tb a,tb b where a.a=b.b and a.b=b.a
order by ascii(a.a)+ascii(a.b)
这个错了...用下面的..with tb(a ,b ) as (
select '1','2' union all
select '唉','压' union all
select '2','1' union all
select '5','1' union all
select '3','1' union all
select '4','1' union all
select '1','3' union all
select '1','5' union all
select '压','唉'
) select a.* from tb a,tb b where a.a=b.b and a.b=b.a
order by ascii(a.b)+ascii(b.b)
order by case A>B when B else A end
select * from tb as t where exists (select 1 from tb where A=t.B and B=t.A)
order by case when A>B then B else A end
with tb as (
select a='a',b='b' union all
select 'c','d' union all
select 'e','a' union all
select 'b','a' union all
select 'd','c'
)
,t1 as
(
select a.a a1, a.b b1,b.a a2,b.b b2,g=ROW_NUMBER() over(order by getdate()) from tb a,tb b where a.a=b.b and a.b=b.a
)
select a.a1,a.b1 from t1 a, t1 b where a.a1=b.a2 and a.b1=b.b2 order by case when a.g<b.g then a.g else b.g end
将第一段连表结果, 加identity列插入临时表,再操作。
select t0.a,t0.b from (select * from tb) t0,(select * from tb) t1
where t0.a=t1.b and t0.b=t1.a --假定原始表叫tb