请问sql语句怎么写SQL codewith t as(select 1 id,a r1,b r2 from dualunion allselect 2 id,b r1,
请问sql语句怎么写
- SQL code
with t as( select 1 id,'a' r1,'b' r2 from dual union all select 2 id,'b' r1,'a' r2 from dual union all select 3 id,'c' r1,'d' r2 from dual union all select 4 id,'d' r1,'c' r2 from dual)select t.id,t.r1,t.r2 FROM t
查询结果：
id r1 r2
1ba
2ab
3dc
4cd
请问怎么把交叉重复的 去掉，比如1和2重复 要去掉一条，3和4重复 要去掉一条，
求高手。
[解决办法]
- SQL code
with t as( select 1 id,'a' r1,'b' r2 from dual union all select 2 id,'b' r1,'a' r2 from dual union all select 3 id,'c' r1,'d' r2 from dual union all select 4 id,'d' r1,'c' r2 from dual)select max(id), r1, r2 from(select id, greatest(r1,r2) r1, least(r1,r2) r2 FROM t)group by r1,r2;