好玩的SQL

Terminal

1. 做一個(gè)3*3的加法表

1. SQL> select a||'+'||b||'='||(a+b) from (select rownum a from all_objects where rownum<4), (select rownum b from all_objects where rownum<4);
   
2. 
   
3. A||'+'||B||'='||(A+B)
   
4. ------------------------------------------------------------------------------------------------------------------------
   
5. 1+1=2
   
6. 1+2=3
   
7. 1+3=4
   
8. 2+1=3
   
9. 2+2=4
   
10. 2+3=5
    
11. 3+1=4
    
12. 3+2=5
    
13. 3+3=6
    
14. 
    
15. 9 rows selected.

復(fù)制代碼

2. 做一個(gè)5*5的乘法表

1. with multiplier as (select rownum n from dual connect by rownum<6)
   
2. select a.n||'*'||b.n||'='||(a.n*b.n) from multiplier a, multiplier b

復(fù)制代碼

3. 不用connect by，只用dual表，構(gòu)造出1到128

1. with a as (select 1 from dual union all select 1 from dual)
   
2. select rownum from a,a,a,a,a,a,a

復(fù)制代碼

4. 池塘邊上有牛和鵝若干，小華總共看到15個(gè)頭42條腿，請(qǐng)問(wèn)牛和鵝各有多少？

1. with a as (select 1 from dual union all select 1 from dual),
   
2. b as (select rownum n from a,a,a,a)
   
3. select x.n num_of_bull, y.n num_of_goose from b x, b y where x.n*4+y.n*2=42 and x.n+y.n=15

復(fù)制代碼

5. 百錢(qián)買(mǎi)雞兔：老母雞3塊1只，小母雞4塊5只，大白兔2塊1只，小白兔3塊4只，要求買(mǎi)回來(lái)的動(dòng)物總共100只，并且腳不少于240條不多于320條�；�100塊錢(qián)來(lái)買(mǎi)這些動(dòng)物，要求每種動(dòng)物都至少要購(gòu)買(mǎi)一只且錢(qián)正好花完，輸出所有的可能情況。

1. with t as (select 1 from dual union all select 1 from dual),
   
2. t1 as (select rownum n from t,t,t,t,t)
   
3. select a.n lmj,5*b.n xmj,c.n dbt,4*d.n xbt from t1 a,t1 b,t1 c,t1 d where 3*a.n+b.n*4+c.n*2+d.n*3=100 and a.n+5*b.n+c.n+4*d.n=100 and (2*a.n+10*b.n+4*c.n+16*d.n between 240 and 320) and a.n<>0 and b.n<>0 and c.n<>0 and d.n<>0;

復(fù)制代碼

6. 每個(gè)雇員的薪水（SAL）都對(duì)應(yīng)到一個(gè)薪水級(jí)別（SALGRADE表中的GRADE字段），哪個(gè)薪水級(jí)別上的雇員數(shù)量最多？輸出該薪水級(jí)別信息。本題需要用三種不同的寫(xiě)法作答。

第一種寫(xiě)法：

1. select * from salgrade where grade=(select grade from (select s.grade,count(*) from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade order by 2 desc) where rownum=1);

復(fù)制代碼

第二種寫(xiě)法：

1. with t as (select s.grade,count(*) num from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade),
   
2. t1 as (select max(num) maxnum from t)
   
3. select s.* from salgrade s,t,t1 where s.grade=t.grade and t.num=t1.maxnum;

復(fù)制代碼

第三種寫(xiě)法：

1. select * from salgrade where exists (select 1 from (select grade from (select s.grade,count(*) from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade order by 2 desc) where rownum=1) s where s.grade=salgrade.grade);

復(fù)制代碼

jackson198574

有趣味性~

吹風(fēng)筒

不錯(cuò)，挺好玩的，要多分享

li0924

第一題；這樣寫(xiě)好看些

1. WITH t AS
   
2. (SELECT level lv FROM dual connect by level < 10)
   
3. SELECT
   
4. max(decode(an, 1, val)) col1,
   
5. max(decode(an, 2, val)) col2,
   
6. max(decode(an, 3, val)) col3,
   
7. max(decode(an, 4, val)) col4,
   
8. max(decode(an, 5, val)) col5,
   
9. max(decode(an, 6, val)) col6,
   
10. max(decode(an, 7, val)) col7,
    
11. max(decode(an, 8, val)) col8,
    
12. max(decode(an, 9, val)) col9
    
13. FROM
    
14. (
    
15. SELECT a.lv an, b.lv bn, a.lv || '+' || b.lv || '=' || (a.lv + b.lv) val
    
16. FROM t a, t b
    
17. WHERE a.lv <= b.lv
    
18. )
    
19. group by bn
    
20. order by  1

復(fù)制代碼

renxiao2003

太厲害 了。有想象力。