17,377
社区成员
发帖
与我相关
我的任务
分享
select e1.ename, e2.ename, e1.sal from emp e1, emp e2 where e1.sal = e2.sal and e1.empno< e2.empno
这个是不是重复的,因为用了小于操作符,就不会有冗余了,这刚好是我最近看“SQL.Cookbook中文版.pdf”的一个收获
select e1.ename, e2.ename, e1.sal
from emp e1, emp e2
where e1.sal = e2.sal
and e1.ename||e2.name != e2.name||e1.name
;
select a.ename, b.ename, a.sal
from emp a, emp b
where a.sal = b.sal
and a.ename < b.ename
简单还能满足需求
select sal, wmsys.wm_concat(ename) ename
from emp a
where exists
(select sal
from emp b
where b.sal = a.sal
group by sal
having count(1) >= 2)
group by sal;
select a.ename, b.ename, a.sal
from emp a, emp b
where a.sal = b.sal
and a.ename < b.ename
select * from emp e where sal in (select sal from emp group by sal having count (sal)>1)
select distinct (case
when e1.ename > e2.ename then
e2.ename
else
e1.ename
end),
(case
when e1.ename > e2.ename then
e1.ename
else
e2.ename
end),
e1.sal
from scott.emp e1, scott.emp e2
where e1.sal = e2.sal
and e1.ename != e2.ename;
select regexp_substr(enames, '[^,]+', 1, 1) ename1,
regexp_substr(enames, '[^,]+', 1, 2) ename2
from (select wmsys.wm_concat(ename) enames
from scott.emp
group by sal
having count(1) > 1)
select *
from (select e1.ename,
decode(e1.sal,
lead(e1.sal) over(order by e1.sal),
lead(e1.ename) over(order by e1.sal),
null) ename2,
e1.sal
from scott.emp e1, scott.emp e2
where e1.sal = e2.sal
and e1.ename != e2.ename)
where ename2 is not null;