每日一练（四）

第一个题：
环境准备：
drop table teachers;
drop table students;
drop table classes;
create table Teachers(T_ID int,T_NAME varchar2(20));
create table Students(S_ID int,S_NAME varchar2(20));
create table Classes(T_ID int,S_ID int,C_NAME varchar2(20));
insert into Teachers values(1,'T1');
insert into Teachers values(2,'T2');
insert into Teachers values(3,'T3');
insert into Teachers values(4,'T4');
insert into Teachers values(5,'T5');
insert into Students values(1,'S1');
insert into Students values(2,'S1');
insert into Students values(3,'S1');
insert into Students values(4,'S1');
insert into Students values(5,'S1');
insert into Students values(6,'S1');
insert into Students values(7,'S1');
insert into Students values(8,'S1');
insert into Students values(9,'S1');
insert into Students values(10,'S1');
insert into Students values(11,'S1');
insert into Students values(12,'S1');
insert into Students values(13,'S1');
insert into Students values(14,'S1');
insert into Students values(15,'S1');
insert into Students values(16,'S1');
insert into Classes values(1,1,'大班');
insert into Classes values(1,2,'大班');
insert into Classes values(1,3,'小班');
insert into Classes values(1,4,'大班');
insert into Classes values(1,13,'大班');
insert into Classes values(1,14,'大班');
insert into Classes values(1,15,'小班');
insert into Classes values(1,16,'大班');
insert into Classes values(2,1,'大班');
insert into Classes values(2,2,'小班');
insert into Classes values(2,3,'大班');
insert into Classes values(2,4,'大班');
insert into Classes values(2,16,'小班');
insert into Classes values(2,15,'小班');
insert into Classes values(2,14,'小班');
insert into Classes values(3,5,'大班');
insert into Classes values(3,6,'小班');
insert into Classes values(3,7,'大班');
insert into Classes values(4,4,'大班');
insert into Classes values(4,5,'大班');
insert into Classes values(4,6,'小班');
insert into Classes values(4,7,'小班');
insert into Classes values(4,8,'小班');
insert into Classes values(5,9,'大班');
insert into Classes values(5,10,'小班');
insert into Classes values(5,11,'小班');
insert into Classes values(5,12,'小班');
commit;
 老师表 Teachers(T_ID,T_NAME)
 学生表 Students(S_ID,S_NAME)
 班级表 Classes(T_ID,S_ID,C_NAME)
 其中 C_NAME  的取值只有‘大班’和‘小班’。
请查询出符合条件的老师的名字，条件是老师在大班中带的学生数大于此老师在小班中带
的学生数。

(1)
select *
  from teachers
 where t_id in (select c1.t_id
          from (select distinct t_id,
                                c_name,
                                count(*) over(partition by t_id) cnt
                  from classes
                 where c_name = '大班') c1
         inner join (select distinct t_id,
                                    c_name,
                                    count(*) over(partition by t_id) cnt
                      from classes
                     where c_name = '小班') c2
            on (c1.t_id = c2.t_id)
         where c1.cnt > c2.cnt);


                                   T_ID T_NAME
--------------------------------------- --------------------
                                      1 T1
                                      3 T3
(2)
select t_name
from teachers
where t_id in (select t_id
from (select t_id,
sum(decode(c_name, '大班', 1, 0)) c1,
sum(decode(c_name, '小班', 1, 0)) c2
from classes
group by t_id)
where c1 > c2);


第二题：
drop table b;
create table B(id int,name varchar2(20));
insert into B values (1,'a');
insert into B values (2,'b');
insert into B values (3,'c');
insert into B values (4,'d');
commit;


有一张表 B:
编号  名称
1  a
2  b
3  c
4  d
如何写一条 SQL  语句，显示以下结果
ab,ac,ad,bc,bd,cd


select listagg(name, ',') within group(order by name)
  from (SELECT b1.name || '' || b2.name name
          FROM B B1, B B2
         where b1.id < b2.id);

select wmsys.wm_concat(name)
  from (select b1.name || '' || b2.name name
          from b b1, b b2
         where b1.id < b2.id);          

第三题：
create table leftTable(id int,name varchar(20));
create table rightTable(id int,name varchar(20));
insert into leftTable values(1,'A5');
insert into leftTable values(2,'A8');
insert into leftTable values(3,'AF');
insert into leftTable values(4,'B5');
insert into leftTable values(5,'B3');
insert into leftTable values(6,'BD');
insert into leftTable values(7,'C3');
insert into leftTable values(8,'CK');
insert into leftTable values(9,'CI');
commit;
只用一条 SQL  语句, 要求从左表查询出右表! 左表是查询表, 
右表是要求查询出的结果, 并不是两表联合查询.
左表： 右表：
ID NAME ID NAME
---------- ------------------
1 A5 1 A5,A8,AF....
2 A8 2 B5,B3,BD....
3 AF 3 C3,CK,CI....
4 B5
5 B3
6 BD
7 C3
8 CK
9 CI

select row_number() over(order by name) id, name
  from (select listagg(name, ',') within group(order by name) name
          from lefttable
         group by substr(name, 1, 1));


SQL> select row_number() over(order by name) id, name
  2    from (select wmsys.wm_concat(name) name
  3            from lefttable
  4           group by substr(name, 1, 1));
select row_number() over(order by name) id, name
  from (select wmsys.wm_concat(name) name
          from lefttable
         group by substr(name, 1, 1))
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB

我的数据库是11g，wmsys.wm_concat()函数返回的是CLOB类型，10g中是返回varchar2,
但是12c取消了这个函数，因为这个函数是非公开函数，如果出现问题，ORACLE是不会管的。

select row_number() over(order by name) id, name
  from (select to_char(wmsys.wm_concat(name)) name
          from lefttable
         group by substr(name, 1, 1));
        ID NAME
    ------ -----------------
         1 A5,A8,AF
         2 B3,B5,BD
         3 C3,CI,CK

第四题：
环境准备：
drop table t1;
drop table t2;
create table t1(seller varchar2(5),non_seller varchar2(5));
insert into t1 values('A','B');
insert into t1 values('A','C');
insert into t1 values('A','D');
insert into t1 values('B','A');
insert into t1 values('B','C');
insert into t1 values('B','D');
insert into t1 values('C','A');
insert into t1 values('C','B');
insert into t1 values('C','D');
insert into t1 values('D','A');
insert into t1 values('D','B');
insert into t1 values('D','C');
commit;
create table t2(seller varchar2(5),coupon number,bal number);
insert into t2 values('A',9,100);
insert into t2 values('B',9,200);
insert into t2 values('C',9,300);
insert into t2 values('D',9,400);
insert into t2 values('A',9.5,100);
insert into t2 values('B',9.5,20);
insert into t2 values('A',10,80);
commit;

某人：“去年应聘一个职位未果, 其间被考了一个看似简单的题, 但我没有找到好的答案，不
知各位大虾有无好的解法？”
题为 ： 有两个表, t1, t2
Table t1:
SELLER  NON_SELLER
------------------------------------
A  B
A  C
A  D
B  A
B  C
B  D
C  A
C  B
C  D
D  A
D  B
D  C
Table t2:
SELLER  COUPON BAL
----------------------------------------------
A  9  100
B  9  200
C  9  300
D  9  400
A  9.5 100
B  9.5 20
A  10 80
select t1.seller, t2.coupon, sum(t2.bal)
  from t1,
       (select seller, coupon, bal
          from t2
        union all
        select 'B', 10, 0
          from dual) t2
 where t1.non_seller = t2.seller
 group by t1.seller, t2.coupon
 order by 2, 1;
 要求用 SELECT  语句列出如下结果:
 如 A 的 的 SUM(BAL)为 为 B,C,D  的和,B 的 的 SUM(BAL)为 为 A,C,D  的和.......
且用的方法不要增加数据库负担, 如用临时表等.
NON-SELLER COUPON SUM(BAL)
------------------------------------------------------------
A  9  900
B  9  800
C  9  700
D  9  600
A  9.5 20
B  9.5 100
C  9.5 120
D  9.5 120
A  10 0
B  10 80
C  10 80
D  10 80
第五题：
有两个表 A 和 和 B ，均有 key 和 和 value  两个字段。
如果 B 的  key  在 A  中也有，就把 B 的 的 value  换为 A  中对应的 value ，这道题的 SQL  语句怎么写？
merger into
merge into b
using a
on (a.key = b.key)
when matched then
    update set a.value = b.value;
第六题：
环境准备：
drop table t1;
create table t1(id int,proid int,proname varchar2(5));
insert into t1 values(1,1,'M');
insert into t1 values(1,2,'F');
insert into t1 values(2,1,'N');
insert into t1 values(2,2,'G');
insert into t1 values(3,1,'B');
insert into t1 values(3,2,'A');
commit;
原表:
id  proid  proname
1  1  M
1  2  F
2  1  N
2  2  G
3  1  B
3  2  A
查询后的表:
id  pro1 pro2
1  M  F
2  N  G
3  B  A
写出查询语句
select id,
       max(decode(proid, 1, proname)) "pro1",
       max(decode(proid, 2, proname)) "pro2"
  from t1
 group by id;
测试结果：


                                     ID pro1  pro2
--------------------------------------- ----- -----
                                      1 M     F
                                      2 N     G
                                      3 B     A
第七题：
drop table t1;
create table t1(rq varchar2(15),shengfu varchar2(5));
insert into t1 values('2005-05-09','胜');
insert into t1 values('2005-05-09','胜');
insert into t1 values('2005-05-09','负');
insert into t1 values('2005-05-09','负');
insert into t1 values('2005-05-10','胜');
insert into t1 values('2005-05-10','负');
insert into t1 values('2005-05-10','负');
commit;
一道 SQL  语句面试题，关于 group by
表内容：
日期         战况
---------  ---------------------------------
2005-05-09  胜 
2005-05-09  胜 
2005-05-09  负 
2005-05-09  负 
2005-05-10  胜 
2005-05-10  负 
2005-05-10  负 
如果要生成下列结果,  该如何写 sql  语句?
日期        胜  负
----------- -------------------------------
2005-05-09  2  2
2005-05-10  1  2


RQ              SHENGFU


select rq,
       sum(decode(shengfu, '胜', 1, 0)) "胜",
       sum(decode(shengfu, '负', 1, 0)) "负"
  from t1
 group by rq
 order by 1;


RQ                       胜          负
--------------- ---------- ----------
2005-05-09               2          2
2005-05-10               1          2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31402276/viewspace-2127327/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31402276/viewspace-2127327/