【正文】 by year) b3,rank()over(partition by name order by year) rk from t) where rk=1。************************************************************************************精妙的SQL語句！精妙SQL語句作者：不詳 發(fā)文時(shí)間： 10:55:05 說明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) SQL: select * into b from a where 11 說明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) SQL: insert into b(a, b, c) select d,e,f from b。 說明：顯示文章、提交人和最后回復(fù)時(shí)間 SQL: select , from table a,(select max(adddate) adddate from table where =) b 說明：外連接查詢(表名1：a 表名2：b) SQL: select , , , , , from a LEFT OUT JOIN b ON = 說明：日程安排提前五分鐘提醒 SQL: select * from 日程安排 where datediff(39。minute39。,f開始時(shí)間,getdate())5 說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息 SQL: delete from info where not exists ( select * from infobz where = ) 說明： SQL: SELECT , , , FROM TABLE1, (SELECT , , PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,39。YYYY/MM39。) = TO_CHAR(SYSDATE, 39。YYYY/MM39。)) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,39。YYYY/MM39。) = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 39。YYYY/MM39。) 166。166。 39。/0139。,39。YYYY/MM/DD39。) 1, 39。YYYY/MM39。) ) Y, WHERE = （+） AND + NVL(,0) ) B WHERE = 說明： SQL: select * from studentinfo where not exists(select * from student where =) and 系名稱=39。amp。strdepartmentnameamp。39。 and 專業(yè)名稱=39。amp。strprofessionnameamp。39。 order by 性別,生源地,高考總成績 說明： 從數(shù)據(jù)庫中去一年的各單位電話費(fèi)統(tǒng)計(jì)(電話費(fèi)定額賀電化肥清單兩個(gè)表來源） SQL: SELECT , , , TO_CHAR(, 39。yyyy39。) AS telyear, SUM(decode(TO_CHAR(, 39。mm39。), 39。0139。, )) AS JAN, SUM(decode(TO_CHAR(, 39。mm39。), 39。0239。, )) AS FRI, SUM(decode(TO_CHAR(, 39。mm39。), 39。0339。, )) AS MAR, SUM(decode(TO_CHAR(, 39。mm39。), 39。0439。, )) AS APR, SUM(decode(TO_CHAR(, 39。mm39。), 39。0539。, )) AS MAY, SUM(decode(TO_CHAR(, 39。mm39。), 39。0639。, )) AS JUE, SUM(decode(TO_CHAR(, 39。mm39。), 39。0739。, )) AS JUL, SUM(decode(TO_CHAR(, 39。mm39。), 39。0839。, )) AS AGU, SUM(decode(TO_CHAR(, 39。mm39。), 39。0939。, )) AS SEP, SUM(decode(TO_CHAR(, 39。mm39。), 39。1039。, )) AS OCT, SUM(decode(TO_CHAR(, 39。mm39。), 39。1139。, )) AS NOV, SUM(decode(TO_CHAR(, 39。mm39。), 39。1239。, )) AS DEC FROM (SELECT , , , , FROM TELFEESTAND a, TELFEE b WHERE = ) a GROUP BY , , , TO_CHAR(, 39。yyyy39。) 說明：四表聯(lián)查問題： SQL: select * from a left inner join b on = right inner join c on = inner join d on = where ..... 說明：得到表中最小的未使用的ID號(hào) SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT 1 FROM Handle a)*******************************************************************************有兩個(gè)表A和B，均有key和value兩個(gè)字段，如果B的key在A中也有，就把B的value換為A中對(duì)應(yīng)的value這道題的SQL語句怎么寫？update b set =(select from a where =) where in(select from b,a where =)。***************************************************************************高級(jí)sql面試題原表: courseid coursename score 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為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(score60),1,39。fail39。,39。pass39。) 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(score60),1,39。fail39。,39。pass39。) as mark from course_v。 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能選成以下結(jié)果嗎？ 1 ab 2 xyz 使用pl/sql代碼實(shí)現(xiàn)，但要求你組合后的長度不能超出oracle varchar2長度的限制。 下面是一個(gè)例子 create or replace type strings_table is table of varchar2(20)。 / create or replace function merge (pv in strings_table) return varchar2 is ls varchar2(4000)。 begin for i in 1.. loop ls := ls || pv(i)。 end loop。 return ls。 end。 / create table t (id number,name varchar2(10))。 insert into t values(1,39。Joan39。)。 insert into t values(1,39。Jack39。)。 insert into t values(1,39。Tom39。)。 insert into t values(2,39。Rose39。)。 insert into t values(2,39。Jenny39。)。 column names format a80。 select ,merge(cast(multiset(select name from t where = ) 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 , , rtrim( ||39。, 39。|| 4 lead(,1) over (partition by 5 order by ) ||39。, 39。|| 6 lead(,2) over (partition by 7 order by ) ||39。, 39。|| 8 lead(,3) over (partition by 9 order by ) ||39。, 39。|| 10 lead(,4) over (partition by 11 order by ) ||39。, 39。|| 12 lead(,5) over (partition by 13 order by ),39。, 39。) emps, 14 row_number () over (partition by