SELECT empno, ename, sal FROM (SELECT empno, ename, sal, row_number() over(ORDER BY sal DESC) rn FROM emp) WHERE rn = 3 记得好像sql server里也有分析函数,算不算标准sql?
如果上面不算的话,下面这俩应该算吧 select empno ,ename ,sal from emp a where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b where a.sal<b.sal group by a.empno ,a.ename ,a.sal having count(*)=2;
标准语句 呵呵用not in 方式 依次排除 第一高,第二高的语句 找出第三高的?
select a.* from tb2 a where exists (select * from tb2 where salary>a.salary having count(*)=2)
我明白题目的意思我是问你写的语句的意思select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b where a.sal<b.sal group by a.empno ,a.ename ,a.sal having count(*)=2;自己做关联查询,分组?
select empno ,ename ,sal from emp a where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b where a.sal<b.sal group by a.empno ,a.ename ,a.sal having count(*)=2;
SELECT empno, ename, sal FROM emp a WHERE EXISTS (SELECT 1 FROM emp b WHERE b.sal > a.sal HAVING COUNT(*) = 2)经验证是可以的。
验证前确认你的数据满足这个条件么? salary每个人都不一样
SQL> select empno,ename,sal from emp order by sal desc;
EMPNO ENAME SAL --------------------- ---------- --------- 8839 KING 6050.00 8788 SCOTT 3640.00 8902 FORD 3630.00 8566 JONES 3599.75 8698 BLAKE 3448.50 8782 CLARK 2964.50 8499 ALLEN 1936.00 8844 TURNER 1815.00 8934 MILLER 1573.00 8521 WARD 1512.50 8654 MARTIN 1512.50 8876 ADAMS 1331.00 8900 JAMES 1149.50 8369 SMITH 970.00
14 rows selected
SQL> SQL> select empno ,ename ,sal from emp a 2 where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);
EMPNO ENAME SAL --------------------- ---------- --------- 8902 FORD 3630.00
SQL> SQL> select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b 2 where a.sal<b.sal 3 group by a.empno ,a.ename ,a.sal 4 having count(*)=2;
EMPNO ENAME SAL COUNT(*) --------------------- ---------- --------- ---------- 8902 FORD 3630.00 2
SQL>
查出第3到第5高的salary记录SELECT * FROM a a1 where exists (select 1 from a a2 where a1.age>=a2.age having count(*) between 3 and 5)
FROM (SELECT empno, ename, sal, row_number() over(ORDER BY sal DESC) rn
FROM emp)
WHERE rn = 3
记得好像sql server里也有分析函数,算不算标准sql?
select empno ,ename ,sal from emp a
where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b
where a.sal<b.sal
group by a.empno ,a.ename ,a.sal
having count(*)=2;
找出第三高的?
我明白题目的意思我是问你写的语句的意思select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b
where a.sal<b.sal
group by a.empno ,a.ename ,a.sal
having count(*)=2;自己做关联查询,分组?
where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b
where a.sal<b.sal
group by a.empno ,a.ename ,a.sal
having count(*)=2;
FROM emp a
WHERE EXISTS
(SELECT 1
FROM emp b
WHERE b.sal > a.sal
HAVING COUNT(*) = 2)经验证是可以的。
salary每个人都不一样
EMPNO ENAME SAL
--------------------- ---------- ---------
8839 KING 6050.00
8788 SCOTT 3640.00
8902 FORD 3630.00
8566 JONES 3599.75
8698 BLAKE 3448.50
8782 CLARK 2964.50
8499 ALLEN 1936.00
8844 TURNER 1815.00
8934 MILLER 1573.00
8521 WARD 1512.50
8654 MARTIN 1512.50
8876 ADAMS 1331.00
8900 JAMES 1149.50
8369 SMITH 970.00
14 rows selected
SQL>
SQL> select empno ,ename ,sal from emp a
2 where exists(select 1 from emp b where b.sal>a.sal having count(*)=2);
EMPNO ENAME SAL
--------------------- ---------- ---------
8902 FORD 3630.00
SQL>
SQL> select a.empno ,a.ename ,a.sal,count(*) from emp a,emp b
2 where a.sal<b.sal
3 group by a.empno ,a.ename ,a.sal
4 having count(*)=2;
EMPNO ENAME SAL COUNT(*)
--------------------- ---------- --------- ----------
8902 FORD 3630.00 2
SQL>
where exists
(select 1 from a a2 where a1.age>=a2.age having count(*) between 3 and 5)