好玩的SQL，好玩SQL

好玩的SQL，好玩SQL

1. 做一個3*3的加法表

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);

A||'+'||B||'='||(A+B)
------------------------------------------------------------------------------------------------------------------------
1+1=2
1+2=3
1+3=4
2+1=3
2+2=4
2+3=5
3+1=4
3+2=5
3+3=6

9 rows selected.

2. 做一個5*5的乘法表

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

3. 不用connect by，只用dual表，構造出1到128

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

4. 池塘邊上有牛和鵝若干，小華總共看到15個頭42條腿，請問牛和鵝各有多少？

with a as (select 1 from dual union all select 1 from dual),
b as (select rownum n from a,a,a,a)
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

5. 百錢買雞兔：老母雞3塊1只，小母雞4塊5只，大白兔2塊1只，小白兔3塊4只，要求買回來的動物總共100只，並且腳不少於240條不多於320條。花100塊錢來買這些動物，要求每種動物都至少要購買一只且錢正好花完，輸出所有的可能情況。

with t as (select 1 from dual union all select 1 from dual),
t1 as (select rownum n from t,t,t,t,t)
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;

6. 每個雇員的薪水（SAL）都對應到一個薪水級別（SALGRADE表中的GRADE字段），哪個薪水級別上的雇員數量最多？輸出該薪水級別信息。本題需要用三種不同的寫法作答。

第一種寫法：

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);

第二種寫法：

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),
t1 as (select max(num) maxnum from t)
select s.* from salgrade s,t,t1 where s.grade=t.grade and t.num=t1.maxnum;

第三種寫法：

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);