关于几道sql笔试题的疑问

Question

===================================================
第一题:select * from t19;MON              DEPTNO          ACH
---------------- -------- ----------
m1               d1               10
m1               d2               10
m1               d3                5
m2               d2                8
m2               d4                9
m3               d3                8select * from t20;DEPTNO   DNAME
-------- ----------------
d1       cd1
d2       cd2
d3       cd3
d4       cd4select 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         75select 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
.... ....

Answer 1

在家用mysql模拟出来，先上第四题答案
select concat(t1.name,t2.name) from test t1,test t2 where t1.no<
t2.no;
换成ORACLE应该是
select t1.name||t2.name from test t1,test t2 where t1.no<
t2.no;

Answer 2

第四题，先说下思路，先行列转换成
id c1 value
1  a1  1
1  a2  1
1  a3  2
然后按id,value分组，统计每组的条数，分别对应二三四

Answer 3

附
第一题要求查询的结果
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回2楼:嗯,我会沿着你的思路试试

Answer 4

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

Answer 5

第四题答案，看起来有点繁琐，期待更好答案...
WITH t AS(
SELECT 1 id,1 a1,1 a2,2 a3,2 a4,3 a5,3 a6,4 a7,4 a8 FROM dual 
UNION ALL 
SELECT 2,1, 1, 2, 5, 6, 3, 4, 9 FROM dual 
UNION ALL 
SELECT 3,1, 1, 1, 3, 5, 7 ,2 ,6 FROM dual
UNION ALL 
SELECT 4,1, 1, 1 , 3, 3 ,3, 8 ,8 FROM dual
UNION ALL 
SELECT 5,1, 1, 3, 3, 3 ,3, 8 ,8 FROM dual  
)
SELECT DISTINCT id FROM (
  SELECT id,val,Last_Value(Count(*)) over (PARTITION BY id ORDER BY Count(*) rows between unbounded preceding and unbounded following)num FROM (
      SELECT id,'a1'c1,a1 val FROM t 
      UNION ALL
      SELECT id,'a2'c1,a2 FROM t 
      UNION ALL
      SELECT id,'a3'c1,a3 FROM t 
      UNION ALL
      SELECT id,'a4'c1,a4 FROM t 
      UNION ALL
      SELECT id,'a5'c1,a5 FROM t 
      UNION ALL
      SELECT id,'a6'c1,a6 FROM t 
      UNION ALL
      SELECT id,'a7'c1,a7 FROM t 
      UNION ALL
    SELECT id,'a8'c1,a8 FROM t
  )GROUP BY id,val
)WHERE num=2;

Answer 6

第1题：WITH t19 AS (
    SELECT 'm1' mon,'d1' deptno,10 ach FROM DUAL UNION ALL
    SELECT 'm1' mon,'d2' deptno,10 ach FROM DUAL UNION ALL
    SELECT 'm1' mon,'d3' deptno,5 ach FROM DUAL UNION ALL
    SELECT 'm2' mon,'d2' deptno,8 ach FROM DUAL UNION ALL
    SELECT 'm2' mon,'d4' deptno,9 ach FROM DUAL UNION ALL
    SELECT 'm3' mon,'d3' deptno,8 ach FROM DUAL
),
t20 AS (
    SELECT 'd1' deptno,'cd1' dname FROM DUAL UNION ALL
    SELECT 'd2' deptno,'cd2' dname FROM DUAL UNION ALL
    SELECT 'd3' deptno,'cd3' dname FROM DUAL UNION ALL
    SELECT 'd4' deptno,'cd4' dname FROM DUAL
)
SELECT a.deptno,
       MAX(DECODE(a.mon, 'm1', ach, 0)) m1,
       MAX(DECODE(a.mon, 'm2', ach, 0)) m2,
       MAX(DECODE(a.mon, 'm3', ach, 0)) m3
  FROM t19 a,
       t20 b
 WHERE a.deptno = b.deptno
 GROUP BY a.deptno
 ORDER BY a.deptnoDEPTNO         M1         M2         M3
------ ---------- ---------- ----------
d1             10          0          0
d2             10          8          0
d3              5          0          8
d4              0          9          0第2题：WITH tb AS (
    SELECT 'SEKER' username, 65 chinese, 75 maths,60 english FROM DUAL UNION ALL
    SELECT 'BLUES' username, 60 chinese, 90 maths,0  english FROM DUAL UNION ALL
    SELECT 'PG'    username,  0 chinese, 80 maths,90 english FROM DUAL UNION ALL
    SELECT 'ZORRO' username, 70 chinese, 80 maths,75 english FROM DUAL
)
SELECT ROWNUM,
       t1.username,
       DECODE(t2.lv, 1, 'chinese', 2, 'maths', 3, 'english') subject,
       DECODE(t2.lv, 1, DECODE(t1.chinese,0,NULL,t1.chinese), 2, DECODE(t1.maths,0,NULL,t1.maths), 3, DECODE(t1.english,0,NULL,t1.english)) grade
  FROM tb t1,
       (SELECT LEVEL lv
          FROM DUAL
        CONNECT BY LEVEL <= 3) t2    ROWNUM USERNAME SUBJECT GRADE
---------- -------- ------- ----------------------------------------
         1 SEKER    chinese 65
         2 BLUES    chinese 60
         3 PG       chinese 
         4 ZORRO    chinese 70
         5 SEKER    maths   75
         6 BLUES    maths   90
         7 PG       maths   80
         8 ZORRO    maths   80
         9 SEKER    english 60
        10 BLUES    english 
        11 PG       english 90
        12 ZORRO    english 75第3题：WITH tb AS (
    SELECT 1 num,'a' tname FROM DUAL UNION ALL
    SELECT 2 num,'b' tname FROM DUAL UNION ALL
    SELECT 3 num,'c' tname FROM DUAL UNION ALL
    SELECT 4 num,'d' tname FROM DUAL
)
SELECT SUBSTR(SYS_CONNECT_BY_PATH(m.tname, ' '), 2) tname
  FROM (SELECT ROWNUM rn,
               t1.tname || t2.tname tname
          FROM tb t1,
               tb t2
         WHERE t1.num < t2.num) m
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH m.rn = 1
CONNECT BY PRIOR m.rn = m.rn - 1TNAME
--------------------------------------------------------------------------------
ab ac ad bc bd cd
第4题：WITH tb AS (
    SELECT 1 tid,1 a1,1 a2,2 a3,2 a4,3 a5,3 a6,4 a7,4 a8 FROM DUAL UNION ALL
    SELECT 2 tid,1 a1,1 a2,2 a3,5 a4,6 a5,3 a6,4 a7,9 a8 FROM DUAL UNION ALL
    SELECT 3 tid,1 a1,1 a2,1 a3,3 a4,5 a5,7 a6,2 a7,6 a8 FROM DUAL UNION ALL
    SELECT 4 tid,1 a1,1 a2,1 a3,3 a4,3 a5,3 a6,8 a7,8 a8 FROM DUAL UNION ALL
    SELECT 5 tid,1 a1,1 a2,3 a3,3 a4,3 a5,3 a6,8 a7,8 a8 FROM DUAL
)
,tmp AS (
    SELECT LEVEL + 1 same_cnt FROM DUAL CONNECT BY LEVEL <= 7
)
SELECT v.same_cnt,
       SUBSTR(SYS_CONNECT_BY_PATH(v.tid, ','), 2) tid
  FROM (SELECT m1.same_cnt,
               m2.tid,
               ROW_NUMBER() OVER(PARTITION BY m1.same_cnt ORDER BY m2.tid) rn
          FROM tmp m1
          LEFT JOIN (SELECT p.tid,
                           MAX(P.cnt) same_cnt
                      FROM (SELECT n.tid,
                                   n.lv,
                                   n.a,
                                   COUNT(*) OVER(PARTITION BY n.tid, n.a) cnt
                              FROM (SELECT t1.tid,
                                           t2.lv,
                                           DECODE(t2.lv, 1, t1.a1, 2, t1.a2, 3, t1.a3, 4, t1.a4, 5, t1.a5, 6, t1.a6, 7, t1.a7, 8, t1.a8) a
                                      FROM tb t1,
                                           (SELECT LEVEL lv
                                              FROM DUAL
                                            CONNECT BY LEVEL <= 8) t2) n) p
                     GROUP BY p.tid) m2 ON m1.same_cnt = m2.same_cnt) v
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH v.rn = 1
CONNECT BY PRIOR v.rn = v.rn - 1
       AND PRIOR v.same_cnt = v.same_cnt  SAME_CNT TID
---------- --------------------------------------------------------------------------------
         2 1,2
         3 3,4
         4 5
         5 
         6 
         7 
         8 

Answer 7

第二题:--创建测试表
create table t6 
(name varchar2(20) , 
y number ,
m number ,
e number ) ;
--插入测试数据
insert into t6 values ('SEKER',65,75,60) ;
insert into t6 values ('BLUES',60,90,0) ;
insert into t6 values ('PG',0,80,90) ;
insert into t6 values ('ZORRO',70,80,75) ;
--
SQL> select * from t6 ;
 
NAME                          Y          M          E
-------------------- ---------- ---------- ----------
SEKER                        65         75         60
BLUES                        60         90          0
PG                            0         80         90
ZORRO                        70         80         75
--查询
SQL>  SELECT rownum as id, NAME , SUBJEC , GRADE  FROM t6
  2    UNPIVOT INCLUDE NULLS (grade FOR SUBJEC IN
  3    (Y AS 'yuwen', M AS 'shuxue',
  4     E AS 'yingyu'))
  5  ;
 
        ID NAME                 SUBJEC      GRADE
---------- -------------------- ------ ----------
         1 SEKER                yuwen          65
         2 SEKER                shuxue         75
         3 SEKER                yingyu         60
         4 BLUES                yuwen          60
         5 BLUES                shuxue         90
         6 BLUES                yingyu          0
         7 PG                   yuwen           0
         8 PG                   shuxue         80
         9 PG                   yingyu         90
        10 ZORRO                yuwen          70
        11 ZORRO                shuxue         80
        12 ZORRO                yingyu         75
 
12 rows selected

Answer 8

--第一题：with t19 as 
(
select 'm1' as mon ,'d1' as deptno ,10 as ach  from dual 
union
select 'm1','d2',10 from dual 
union
select 'm1','d3',5 from dual 
union
select 'm2','d2',8 from dual 
union
select 'm2','d4',9 from dual 
union
select 'm3','d3',8 from dual 
),
 t20 as 
(
select 'd1' as deptno ,'cd1' as dname from dual
union
select 'd2','cd2' from dual
union
select 'd3','cd3' from dual
union
select 'd4','cd4' from dual
)
select t20.deptno, t20.dname, nvl(t.m1, 0) as m1 , nvl(t.m2, 0) as m2 , nvl(t.m3, 0) as m3
  from t20,
       (SELECT *
          FROM (SELECT deptno, ach, mon FROM t19) PIVOT(SUM(ach) FOR mon IN('m1' M1,
                                                                            'm2' M2,
                                                                            'm3' M3))) t
 where t20.deptno = t.deptno
 order by t20.deptno
/DEPTNO               DNAME                        M1         M2         M3
-------------------- -------------------- ---------- ---------- ----------
d1                   cd1                          10          0          0
d2                   cd2                          10          8          0
d3                   cd3                           5          0          8
d4                   cd4                           0          9          0--第三题：
dex@ORCL> with t(no,name) as (
  2  select 1,'a' from dual
  3  union
  4  select 2,'b' from dual
  5  union
  6  select 3,'c' from dual
  7  union
  8  select 4,'d' from dual
  9  )
 10  select wm_concat(t1.name||t2.name)
 11  from
 12  t t1 , t t2
 13  where t1.no < t2.no;WM_CONCAT(T1.NAME||T2.NAME)
----------------------------------------------------------------------------------------------------
ab,ac,ad,bc,bd,cd