SQL試題集（三）

1.用一條SQL語句 查詢出每門課都大於80分的學生姓名　name　 kecheng　 fenshu 張三　　 語文　　　 81張三　　 數學　　　 75李四　　 語文　　　 76李四　　 數學　　　 90王五　　 語文　　　 81王五　　 數學　　　 100王五　　 英語　　　 90A: select distinct name from table　where　name not in (select distinct name from table where fenshu<=80)2.學生表 如下:自動編號　 學號　 姓名 課程編號 課程名稱 分數1　　　　2005001　張三　0001　　　數學　　692　　　　2005002　李四　0001　　　數學　　893　　　　2005001　張三　0001　　　數學　　69刪除除了自動編號不同,其他都相同的學生冗餘資訊A: delete tablename where 自動編號 not in(select min(自動編號) from tablename group by 學號,姓名,課程編號,課程名稱,分數)一個叫department的表，裡面只有一個欄位name,一共有4條紀錄，分別是a,b,c,d,對應四個球對，現在四個球對進行比賽，用一條sql語句顯示所有可能的比賽組合.你先按你自己的想法做一下，看結果有我的這個簡單嗎？答：select a.name, b.name from team a, team b where a.name < b.name 請用SQL語句實現：從TestDB資料表中查詢出所有月份的發生額都比101科目相應月份的發生額高的科目。請注意：TestDB中有很多科目，都有1－12月份的發生額。AccID：科目代碼，Occmonth：發生額月份，DebitOccur：發生額。資料庫名：JcyAudit，資料集：Select * from TestDB答：select a.*from TestDB a ,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) bwhere a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur************************************************************************************面試題：怎麼把這樣一個表兒year　month amount1991　 1　　 1.11991　 2　　 1.21991　 3　　 1.31991　 4　　 1.41992　 1　　 2.11992　 2　　 2.21992　 3　　 2.31992　 4　　 2.4查成這樣一個結果year m1　m2　m3　m41991 1.1 1.2 1.3 1.41992 2.1 2.2 2.3 2.4 答案一、select year, (select amount from　aaa m where month=1　and m.year=aaa.year) as m1,(select amount from　aaa m where month=2　and m.year=aaa.year) as m2,(select amount from　aaa m where month=3　and m.year=aaa.year) as m3,(select amount from　aaa m where month=4　and m.year=aaa.year) as m4from aaa　group by year 這個是ORACLE　中做的：select * from (select name, year b1, lead(year) over(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(partition by name order by year) rk from t) where rk=1;************************************************************************************精妙的SQL語句！精妙SQL語句　作者：不詳 發文時間：2003.05.29 10:55:05 說明：複製表(只複製結構,源表名：a 新表名：b) SQL: select * into b from a where 1<>1 說明：拷貝表(拷貝資料,源表名：a 目標表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 說明：顯示文章、提交人和最後回複時間 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 說明：外串連查詢(表名1：a 表名2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 說明：排程提前五分鐘提醒 SQL: select * from 排程 where datediff('minute',f開始時間,getdate())>5 說明：兩張關聯表，刪除主表中已經在副表中沒有的資訊 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 說明：-- SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (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 系名稱='"&strdepartmentname&"' and 專業名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績 說明： 從資料庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源） 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, 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);***************************************************************************進階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; 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 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 9 order by e.ename) ||', '|| 10 lead(e.ename,4) over (partition by d.deptno 11 order by e.ename) ||', '|| 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename),', ') emps, 14 row_number () over (partition by d.deptno 15 order by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x = 1 20 / DEPTNO DNAME EMPS ------- ----------- ------------------------------------------ 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD also 先create function get_a2; create or replace function get_a2( tmp_a1 number) return varchar2 is Col_a2 varchar2(4000); begin Col_a2:=''; for cur in (select a2 from unite_a where a1=tmp_a1) loop Col_a2=Col_a2||cur.a2; end loop; return Col_a2; end get_a2; select distinct a1 ,get_a2(a1) from unite_a 1 ABC 2 EFG 3 KMN*******************************************************************************一個SQL 面試題 去年應聘一個職位未果,其間被考了一個看似簡單的題,但我沒有找到好的大案.不知各位大蝦有無好的解法?題為:有兩個表, t1, t2,Table t1:SELLER | NON_SELLER----- -----A BA CA DB AB CB DC AC BC DD AD BD CTable t2:SELLER | COUPON | BAL----- --------- ---------A 9 100B 9 200C 9 300D 9 400A 9.5 100B 9.5 20A 10 80要求用SELECT 語句列出如下結果:------如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)為A,C,D的和.......且用的方法不要增加資料庫負擔,如用暫存資料表等.NON-SELLER| COUPON | SUM(BAL) ------- --------A 9 900B 9 800C 9 700D 9 600 A 9.5 20B 9.5 100C 9.5 120D 9.5 120A 10 0B 10 80C 10 80D 10 80關於論壇上那個SQL微軟面試題問題：一百個賬戶各有100$，某個賬戶某天如有支出則添加一條新記錄，記錄其餘額。一百天后，請輸出每天所有賬戶的餘額資訊 這個問題的痛點在於每個使用者在某天可能有多條紀錄，也可能一條紀錄也沒有（不包括第一天）返回的記錄集是一個100天*100個使用者的紀錄集下面是我的思路：1.建立表並插入測試資料：我們要求username從1-100CREATE TABLE [dbo].[TABLE2] ([username] [varchar] (50) NOT NULL , --使用者名稱[outdate] [datetime] NOT NULL , --日期[cash] [float] NOT NULL --餘額) ON [PRIMARYdeclare @i intset @i=1while @i<=100　begin　　insert table2 values(convert(varchar(50),@i),'2001-10-1',100)　　insert table2 values(convert(varchar(50),@i),'2001-11-1',50)　　set @i=@i+1　endinsert table2 values(convert(varchar(50),@i),'2001-10-1',90)select * from table2 order by outdate,convert(int,username)2.組合查詢語句：a.我們必須返回一個從第一天開始到100天的紀錄集：如：2001-10-1（這個日期是任意的）到 2002-1-8由於第一天是任意一天，所以我們需要下面的SQL語句：select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdatefrom table2group by usernameorder by convert(int,username)這裡的奧妙在於：convert(int,username)-1（記得我們指定使用者名稱從1-100 :-))group by username,min(outdate):第一天就可能每個使用者有多個紀錄。返回的結果：outdate　　　　　　　　　　　　　　　　　　　　　　　　------------------------------------------------------ 2001-10-01 00:00:00.000.........2002-01-08 00:00:00.000b.返回一個所有使用者名稱的紀錄集：select distinct username from table2 返回結果：username　　　　　　　　　　　　　　　　　　　　　-------------------------------------------------- 110100......99c.返回一個100天記錄集和100個使用者記錄集的笛卡爾集合：select * from(select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdatefrom table2group by usernameorder by convert(int,username)) as ACROSS join (select distinct username from table2 ) as Border by outdate,convert(int,username)返回結果100*100條紀錄：outdate　　　　　　　　　　　　　　username2001-10-01 00:00:00.000　　　　　　1......2002-01-08 00:00:00.000　　　　　　100d.返回當前所有使用者在資料庫的有的紀錄：select outdate,username,min(cash) as cash from table2group by outdate,usernameorder by outdate,convert(int,username)返回紀錄：outdate　　　　　　　　　　　　　　username　　cash2001-10-01 00:00:00.000　　　　　　1　　　　　90......2002-01-08 00:00:00.000　　　　　　100　　　　50e.將c中返回的笛卡爾集和d中返回的紀錄做left join:select C.outdate,C.username,D.cashfrom(select * from(select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdatefrom table2group by usernameorder by convert(int,username)) as ACROSS join (select distinct username from table2 ) as B) as Cleft join(select outdate,username,min(cash) as cash from table2group by outdate,username) as Don(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)order by C.outdate,convert(int,C.username)注意：使用者在當天如果沒有紀錄，cash欄位返回NULL，否則cash返回每個使用者當天的餘額outdate　　　　　　　　　　　　　　username　　cash2001-10-01 00:00:00.000　　　　　　1　　　　　902001-10-01 00:00:00.000　　　　　　2　　　　　100......2001-10-02 00:00:00.000　　　　　　1　　　　　902001-10-02 00:00:00.000　　　　　　2　　　　　NULL　<--注意這裡......2002-01-08 00:00:00.000　　　　　　100　　　　50f.好了，現在我們最後要做的就是，如果cash為NULL，我們要返回小於當前紀錄日期的第一個使用者餘額(由於我們使用order by cash,所以返回top 1紀錄即可，使用min應該也可以)，這個餘額即為當前的餘額：case isnull(D.cash,0)when 0 then (select top 1 cash from table2 where table2.username=C.usernameand datediff(d,C.outdate,table2.outdate)<0 order by table2.cash)else D.cashend as cashg.最後組合的完整語句就是select C.outdate,C.username,case isnull(D.cash,0)when 0 then (select top 1 cash from table2 where table2.username=C.usernameand datediff(d,C.outdate,table2.outdate)<0 order by table2.cash)else D.cashend as cashfrom(select * from(select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdatefrom table2group by usernameorder by convert(int,username)) as ACROSS join (select distinct username from table2 ) as B) as Cleft join(select outdate,username,min(cash) as cash from table2group by outdate,username) as Don(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)order by C.outdate,convert(int,C.username)返回結果：outdate　　　　　　　　　　　　　　　　 username　　　　cash2001-10-01 00:00:00.000　　1　　　　　　　　　　902001-10-01 00:00:00.000　　2　　　　　　　　　 100......2002-01-08 00:00:00.000　　100　　　　　　　　50***********************************************************************************取出sql表中第31到40的記錄（以自動成長ID為主鍵）*從資料表中取出第n條到第m條的記錄*/ declare @m int declare @n int declare @sql varchar(800) set @m=40 set @n=31 set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in( select top '+ str(@n-1) + 'autoid from idetail)' exec(@sql)  select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id--------------------------------------------------------------------------------select top 10 * from t where id in (select top 40 id from t order by id) order by id desc *******************************************************************************一道面試題,寫sql語句有表a儲存二叉樹的節點,要用一條sql語句查出所有節點及節點所在的層.表a c1 c2 A ----------1 ---- ---- / \A B B C --------2A C / / \B D D N E ------3C E / \ \D F F K I ---4E ID KC N所要得到的結果如下jd cs----- ----A 1B 2C 2D 3N 3E 3F 4K 4I 4有高手指導一下,我只能用pl/sql寫出來,請教用一條sql語句的寫法SQL> select c2, level + 1 lv2 from test start3 with c1 = 'A'4 connect by c1 = prior c25 union6 select 'A', 1 from dual7 order by lv;C2 LV-- ----------A 1B 2C 2D 3E 3N 3F 4I 4K 4已選擇9行。