2. 查詢工資比30號部門任意一個員工低的員工信息。
select *
from emp
where sal < any (
select sal from emp where deptno = 30
)
相當于
select *
from emp
where sal < (
select MAX(sal) from emp where deptno = 30
)
select *
from emp
where sal < any (
select sal from emp where deptno = 30
)
相當于
select *
from emp
where sal < (
select MAX(sal) from emp where deptno = 30
)
2017-04-06
1. 查詢工資比30號部門任意一個員工高的員工信息。
select *
from emp
where sal > any (
select sal from emp where deptno = 30
)
相當于
select *
from emp
where sal > (
select MIN(sal) from emp where deptno = 30
)
select *
from emp
where sal > any (
select sal from emp where deptno = 30
)
相當于
select *
from emp
where sal > (
select MIN(sal) from emp where deptno = 30
)
2017-04-06
select c.ci_id,wm_concat(s.stu_name) from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id) >0 group by c.ci_id
沒人覺得案例一有問題么?不是說runum只取默認排序么?那對emp的排序不是不應該影rownum么?e1表的rownum字段還是默認值吧?,如果不是的話不就跟前面講課沖突了嗎?排序的rownum應該寫到對e1的查詢的吧?
2017-03-30
在oracle中rownum永遠是從1開始的,所以where條件不能 使用>、>=(比如:蓋8層樓,1234層都沒有蓋,怎么能蓋5678呢?大概就是這個意思。說的不對,趕緊提出來哦
使用instr()時給a和b兩邊加逗號來確保準確
wm_concat()在最新版oracle中已被禁用,使用listagg()代替。
SQL> select ci_id, listagg(stu_name, ',') within group (order by stu_id) stu_names from (select c.ci_id, s.stu_id, s.stu_name from pn_ci c, pn_stu s where instr((','||stu_ids||','),(','||(to_char(s.stu_id))||','))>0) group by ci_id;
wm_concat()在最新版oracle中已被禁用,使用listagg()代替。
SQL> select ci_id, listagg(stu_name, ',') within group (order by stu_id) stu_names from (select c.ci_id, s.stu_id, s.stu_name from pn_ci c, pn_stu s where instr((','||stu_ids||','),(','||(to_char(s.stu_id))||','))>0) group by ci_id;