挑战sql高手！！！

Question

1.
有一个表 tab_type 栏位如下：其中“种类”共有8种01～08。
姓名(n)   种类(k)
  A       01
  A       02
  A       03
  B       02
  B       04
  B       08
请问如何实现“查出种类至少包含02，04，08（一种或多种）的人员姓名。”
由于查询条件组合不固定，使用如下方法比较麻烦：
假设查“至少包含02，04”  
select n,k from tab_type a where k = '02' inner join (select n,k from tab_type where k = '04') b on  a.n = b.n 
问：有没有不用自身连接的SQL语句,请写出？create table tab_type (n varchar2(10),k varchar2(2));=============
2，tab_a:
typeid  nid  info
1     1      aa
2     1      bb
3     2      cc
4     3      dd
5     4      ee
6     4      ff
7     5      ggtab_b:
id    typeinfo
1      1,2,3
2      1,3,4,7
3      1,4,5
4      1,2,3
5      1,2,3
根据tab_b的typeinfo查找出在tab_a中 typeid 符合的记录集，写出SQL
说明 tab_b的typeinfo 是 tab_a中 typeid 的组合形式；输出结果如下：id      typeinfo  info
1       1,2,3     aa,bb,cc
2       1,3,4,7   aa,cc,dd,gg
......create table tab_a(typeid varchar2(2),nid varchar2(2),info varchar2(50));
create table tab_b(id varchar2(2),typeinfo varchar2(50));

Answer 1

有一个表  tab_type  栏位如下：其中“种类”共有8种01～08。  
姓名(n)      种类(k)  
   A              01  
   A              02  
   A              03  
   B              02  
   B              04  
   B              08  查同时包含“02，04”的人       
select  n,k  from  tab_type  a  where  k  =  '02'  inner  join  (select  n,k  from  tab_type  where  k  =  '04')  b  on    a.n  =  b.n    
问：有没有不用自身连接的SQL语句,请写出？  
 
create  table  tab_type  (n  varchar2(10),k  varchar2(2));  
 
=============  
2，  
 
tab_a:  
typeid    nid    info  
1          1            aa  
2          1            bb  
3          2            cc  
4          3            dd  
5          4            ee  
6          4            ff  
7          5            gg  
 
tab_b:  
id        typeinfo  
1            1,2,3  
2            1,3,4,7  
3            1,4,5  
4            1,2,3  
5            1,2,3  
根据tab_b的typeinfo查找出在tab_a中  typeid  符合的记录集，写出SQL  
说明  tab_b的typeinfo  是  tab_a中  typeid  的组合形式；输出结果如下：  
 
id            typeinfo    info  
1              1,2,3          aa,bb,cc  
2              1,3,4,7      aa,cc,dd,gg  
......  
 
create  table  tab_a(typeid  varchar2(2),nid  varchar2(2),info  varchar2(50));  
create  table  tab_b(id  varchar2(2),typeinfo  varchar2(50));

Answer 2

SELECT *
  FROM (SELECT   n, COUNT (*) num
            FROM (SELECT DISTINCT *
                             FROM tab_type
                            WHERE k = '02' OR k = '04' OR k = '08') a
        GROUP BY n)
 WHERE num = 3

Answer 3

2. 
SELECT DISTINCT  b.id, b.typeinfo,
 CASE WHEN LENGTH(REPLACE(b.typeinfo,',')) = 3 AND a1.typeid||a2.typeid||a3.typeid = replace(b.typeinfo,',') 
 THEN a1.info||a2.info||a3.info
 WHEN LENGTH(replace(b.typeinfo,',')) = 4 AND a1.typeid||a2.typeid||a3.typeid||a4.typeid = REPLACE(b.typeinfo,',') 
 THEN a1.info||a2.info||a3.info||a4.info
 ELSE NULL END CASE 
FROM tab_a a1, tab_a a2, tab_a a3, tab_a a4, tab_b  b
WHERE 
(a1.typeid||a2.typeid||a3.typeid||a4.typeid = replace(b.typeinfo,',') AND LENGTH(REPLACE(b.typeinfo,','))=4 )
 OR
(a1.typeid||a2.typeid||a3.typeid = REPLACE(b.typeinfo,',') AND LENGTH(REPLACE(b.typeinfo,','))=3 )

Answer 4

2. 
方法一:
SELECT DISTINCT  b.id, b.typeinfo,
CASE 
WHEN LENGTH(REPLACE(b.typeinfo,',')) = 3 AND a1.typeid||a2.typeid||a3.typeid = replace(b.typeinfo,',') 
THEN a1.info||a2.info||a3.info
WHEN LENGTH(replace(b.typeinfo,',')) = 4 AND a1.typeid||a2.typeid||a3.typeid||a4.typeid = REPLACE(b.typeinfo,',') 
THEN a1.info||a2.info||a3.info||a4.info
ELSE NULL 
END CASE 
FROM tab_a a1, tab_a a2, tab_a a3, tab_a a4, tab_b  b
WHERE  (a1.typeid||a2.typeid||a3.typeid||a4.typeid = replace(b.typeinfo,',') AND LENGTH(REPLACE(b.typeinfo,','))=4 )
OR
(a1.typeid||a2.typeid||a3.typeid = replace(b.typeinfo,',') AND LENGTH(REPLACE(b.typeinfo,','))=3 )

方法二: SELECT  b.id, b.typeinfo,a1.info||a2.info||a3.info||a4.info 
FROM tab_a a1, tab_a a2, tab_a a3, tab_a a4, tab_b  b
WHERE a1.typeid||a2.typeid||a3.typeid||a4.typeid = REPLACE(b.typeinfo,',') 
UNION
SELECT  b.id, b.typeinfo,a1.info||a2.info||a3.info 
FROM tab_a a1, tab_a a2, tab_a a3, tab_b  b
WHERE a1.typeid||a2.typeid||a3.typeid = REPLACE(b.typeinfo,',')

Answer 5

--第一题修正
SELECT n FROM (
      SELECT n,c,count(serid) cserid FROM (
           SELECT distinct n,k,arg0,instr(arg0,k) serid,length(arg0)-length(REPLACE(arg0,',',''))+1 c
             FROM tab_type
     NATURAL JOIN (SELECT &arg arg0 FROM dual)
            WHERE instr(arg0,k)>0) 
    GROUP BY n,c)
WHERE cserid=c
--第二题修正    SELECT id,typeinfo,
           SUBSTR(MAX(SYS_CONNECT_BY_PATH(info,',')),2) path 
      FROM (SELECT id,typeinfo,info,id||info ident,
        lag(id||info) OVER (PARTITION BY id 
                                ORDER BY instr(typeinfo,typeid)) lastident
  FROM tab_b,tab_a
   WHERE instr(typeinfo,typeid)>0)
START WITH lastident is null
CONNECT BY prior ident = lastident
  GROUP BY id,typeinfo