(O管理)O自学自学笔记

黄刚创意工作室·IT技术部

(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE X.NUM = Y.NUM （+）

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

说明：--

SQL:

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='\and 专业名称='\性别,生源地,高考总成绩

说明：

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

21

黄刚创意工作室·IT技术部

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration

FROM TELFEESTAND a, TELFEE b

WHERE a.tel = b.telfax) a

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

说明：四表联查问题：

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

说明：得到表中最小的未使用的ID号

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

******************************************************************************* 有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value

这道题的SQL语句怎么写？

update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);

***************************************************************************

22

黄刚创意工作室·IT技术部

高级sql面试题 原表:

courseid coursename score

------------------------------------- 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80

-------------------------------------

为了便于阅读,查询此表后的结果显式如下(及格分数为60): courseid coursename score mark

--------------------------------------------------- 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass

--------------------------------------------------- 写出此查询语句

没有装ＯＲＡＣＬＥ，没试过

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course 完全正确

SQL> desc course_v Name Null? Type

----------------------------------------- -------- ---------------------------- COURSEID NUMBER

COURSENAME VARCHAR2(10) SCORE NUMBER

SQL> select * from course_v;

COURSEID COURSENAME SCORE

---------- ---------- ---------- 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;

23

黄刚创意工作室·IT技术部

COURSEID COURSENAME SCORE MARK

---------- ---------- ---------- ---- 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass

******************************************************************************* 原表:

id proid proname 1 1 M 1 2 F 2 1 N 2 2 G 3 1 B 3 2 A

查询后的表:

id pro1 pro2 1 M F 2 N G 3 B A

写出查询语句 解决方案

sql求解 表a

列 a1 a2 记录 1 a 1 b 2 x 2 y 2 z

用select能选成以下结果吗？ 1 ab 2 xyz

使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。 下面是一个例子

create or replace type strings_table is table of varchar2(20); /

create or replace function merge (pv in strings_table) return varchar2 is

24

黄刚创意工作室·IT技术部

ls varchar2(4000); begin

for i in 1..pv.count loop ls := ls || pv(i); end loop; return ls; end; /

create table t (id number,name varchar2(10)); insert into t values(1,'Joan'); insert into t values(1,'Jack'); insert into t values(1,'Tom'); insert into t values(2,'Rose'); insert into t values(2,'Jenny');

column names format a80;

select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names

from (select distinct id from t) t0;

drop type strings_table; drop function merge; drop table t;

用sql：

Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.

This example uses a max of 6, and would need more cut n pasting to do more than that.

SQL> select deptno, dname, emps 2 from (

3 select d.deptno, d.dname, rtrim(e.ename ||', '|| 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) ||', '||

6 lead(e.ename,2) over (partition by d.deptno 7 order by e.ename) ||', '||

8 lead(e.ename,3) over (partition by d.deptno

25