好玩的SQL

Terminal

1. 做一个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.

复制代码

2. 做一个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

复制代码

3. 不用connect by，只用dual表，构造出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

复制代码

4. 池塘边上有牛和鹅若干，小华总共看到15个头42条腿，请问牛和鹅各有多少？

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

复制代码

5. 百钱买鸡兔：老母鸡3块1只，小母鸡4块5只，大白兔2块1只，小白兔3块4只，要求买回来的动物总共100只，并且脚不少于240条不多于320条。花100块钱来买这些动物，要求每种动物都至少要购买一只且钱正好花完，输出所有的可能情况。

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;

复制代码

6. 每个雇员的薪水（SAL）都对应到一个薪水级别（SALGRADE表中的GRADE字段），哪个薪水级别上的雇员数量最多？输出该薪水级别信息。本题需要用三种不同的写法作答。

第一种写法：

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

复制代码

第二种写法：

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;

复制代码

第三种写法：

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

复制代码

jackson198574

有趣味性~

吹风筒

不错，挺好玩的，要多分享

li0924

第一題；這樣寫好看些

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

复制代码

renxiao2003

太厉害 了。有想象力。