关于几道sql笔试题的疑问

spiderman2012

===================================================
第一题:

select * from t19;

MON              DEPTNO          ACH
---------------- -------- ----------
m1               d1               10
m1               d2               10
m1               d3                5
m2               d2                8
m2               d4                9
m3               d3                8

select * from t20;

DEPTNO   DNAME
-------- ----------------
d1       cd1
d2       cd2
d3       cd3
d4       cd4

result

DEPTNO   dname      m1      m2      m3
--------------------------------------
      d1         cd1        10        0       0
      d2         cd2        10        8       0
      d3         cd3        0         5        8
      d4         cd4        0         0        9


select t.deptno,max(t.dname),max(t.m1),max(t.m2),max(t.m3) from (select
a.deptno,b.dname,decode(a.mon,'m1',ach,0) as m1,decode(a.mon,'m2',ach,0) as
m2,decode(a.mon,'m3',ach,0) as m3 from t19 a,t20 b where a.deptno=b.deptno) t
group by t.deptno order by t.deptno;

=================================================
第二题:

select * from t6;

NAME             语文       数学       英语
---------- ---------- ---------- ----------
SEKER              65         75         60
BLUES              60         90          0
PG                  0         80         90
ZORRO              70         80         75

select rownum as id,a.name,a.subject,a.grade from (select name,'语文' as
subject,decode(语文,0,'',语文) as grade from t6 union all select name,'数学'
as subject,decode(数学,0,'',数学) as grade from t6 union all select
name,'英语' as subject,decode(英语,0,'',英语) as grade from t6) a;

ID NAME       SUBJEC GRADE
---------- ---------- ------ ----------------------------------------
         1 SEKER      语文   65
         2 BLUES      语文   60
         3 PG         语文
         4 ZORRO      语文   70
         5 SEKER      数学   75
         6 BLUES      数学   90
         7 PG         数学   80
         8 ZORRO      数学   80
         9 SEKER      英语   60
        10 BLUES      英语
        11 PG         英语   90
        12 ZORRO      英语   75

请问如何优化或者调整以上两条红色的sql语句,以提高执行效率或者精简sql语句

==================================================
第三题:
no    name
1      a
2      b
3      c
4      d
写一条sql语句显示以下结果
ab  ac  ad  bc  bd  cd

虽然有最简单的sql写法,但估计不是出题者想要的,可以不用子查询直接用decode或者case when写出吗

=================================================
第四题个人认为比较变态的题目)
id a1 a2 a3 a4 a5 a6 a7 a8
1,1, 1, 2, 2, 3, 3, 4, 4 算两个重复的一行
2,1, 1, 2, 5, 6, 3, 4, 9算两个重复的一行
3,1, 1, 1, 3, 5, 7 ,2 ,6 算三个重复的一行
4,1, 1, 1 , 3, 3 ,3 8 ,8 算三个重复的一行
5,1, 1, 3, 3, 3 ,3 8 ,8 算四个重复的一行
... ....
请编写sql语句得到
有2列重复行的id: 1, 2
有3列重复行的id: 3, 4
有4列重复行的id: 5
.... ....

278676125

看了下第1题，用你的SQL得出的结果如下图：


我自己写了下SQL如下(我自己测试可以的，不过写的比较繁琐)：

1. select d.deptno as deptno,d.dname as dname, d.b1 as m1 ,d.d1 as m2,nvl(e.ach,0) as m3
   
2. from
   
3. (select b.deptno,b.dname,b.b1,nvl(c.ach,0) d1
   
4. from
   
5. (select t20.deptno,t20.dname,nvl(a.ach,0) as b1
   
6. from t20
   
7. left outer join (select * from t19 where ach='10') a on t20.deptno=a.deptno)
   
8. b
   
9. left outer join (select * from t19 where deptno in ('d2','d3') and mon!='m3' and ach!='10') c on b.deptno=c.deptno
   
10. ) d
    
11. left outer join (select * from t19 where deptno in ('d3','d4') and mon!='m1')
    
12. e on d.deptno=e.deptno
    
13. order by d.deptno

复制代码

spiderman2012

额,其实答案我当时贴错了,当时没法编辑帖子就没有修改,要求获得正确答案d就是你发出来的那张截图

li0924

第1，2题是典型的行列转换。下面第一题的答案

1. select t.deptno,t.dname,s.m1,s.m2,s.m3
   
2.      from t20 t,(select * from t19 pivot(max(ach) for mon in ('m1','m2','m3'))) s
   
3.         where t.deptno=s.deptno;

复制代码

第3，4题；用awk来实现好点！

1. awk 'NR==FNR{a[$1]=$2;next}{for(i=1;i<=4;i++){if(i>$1)printf $2a[i]"\t"}print ""}' file file

复制代码

第4题有点难度；