请教一个数据库查询高难度题，请指教，万分感谢

Question

产品型号  产品编号   对应领域   状态     =======（表1）
1001    01        a         Testing
1001    02        a         Complete
1001    03        b         Planning
1002    01        f         Testing
1002    02        x         Testing
1002    03        f         Testing
1002    04        x         Planning
1003    01        i         Complete
1003    02        i         Complete
1003    03        i         Planning如上表，产品型号：为产品类型的名称，一个产品型号就是一类产品的统称。
产品编号：此类产品现在有多少个，顺序的编号.
对应领域：对应领域用a到b的字母表示，同一个产品型号最多只可能有两种对应领域。
状态：此类产品的状态，是测试阶段，还是完成，还是在策划阶段。通过上面的表格，进行查找，最终需要完成下表
产品类型    对应领域1   对应领域2  Complete   Testing   Planning =========（表2）
1001       a          b         1          1         1
1002       f          x         0          3         1
1003       i                    2          0         1对应领域如果有两个，则需要依次填入对应位置，然后记录每个产品类型的所有产品的所有状态的个数，比如1001中有1个完成，1个测试，1个策划中。
请指教如何通过查询表一来完成表二，最好写成一个查询语句，请附带详细写的讲解，在此真的非常感谢。

Answer 1

固定列的话好做
设表为a ,字段 产品类型 pro_type,产品编号 pro_id, 领域 domain,状态 stat
select pro_type 产品类型,max(decode(rn,1,domain,null))  对应领域1,
       max(decode(rn,2,domain,null))  对应领域1,
       sum(decode(stat,'Complete',1,0)) Complete ,
       sum(decode(stat,'Testing ',1,0)) Testing ,
       sum(decode(stat,'Planning  ',1,0)) Planning from
(
select  a.*,dense_rank() over (partition by pro_type order by domain) rn
from a)
group by pro_type

Answer 2

declare @tb table(prod nvarchar(20),prod_code nvarchar(20),area nvarchar(20),status nvarchar(40))
insert into @tb
select 1001,    '01',        'a',         'Testing'
union all select 1001,    '02',        'a',         'Complete' 
union all select 1001,    '03',        'b',         'Planning' 
union all select 1002,    '01',        'f',         'Testing' 
union all select 1002,    '02',        'x',         'Testing' 
union all select 1002,    '03',        'f',         'Testing' 
union all select 1002,    '04',        'x',         'Planning' 
union all select 1003,    '01',        'i',         'Complete' 
union all select 1003,    '02',        'i',         'Complete' 
union all select 1003,    '03',        'i',         'Planning' select a.prod,b.area1,b.area2,a.Complete,a.Testing,a.Planning
from 
(
  -- 求和
  select prod,
       sum(case when status ='Testing' then 1 else 0 end) as 'Testing',
       sum(case when status ='Complete' then 1 else 0 end) as 'Complete',
       sum(case when status ='Planning' then 1 else 0 end) as 'Planning'
   from @tb group by prod) a,
(--求两个区域
 select prod,max(case when px =1 then area else '' end) as area1 , max(case when px=2 then area else ''end) as area2 from
  (select prod,area,px = (select count(1)+1 from (select prod,area from @tb group by prod,area)t where t.prod =t2.prod and t.area <t2.area  )
  from (select prod,area from @tb group by prod,area) t2 )t3 group by prod 
) b
where a.prod = b.prod/*
prod                 area1                area2                Complete    Testing     Planning
-------------------- -------------------- -------------------- ----------- ----------- -----------
1001                 a                    b                    1           1           1
1002                 f                    x                    0           3           1
1003                 i                                         2           0           1(3 row(s) affected)
*/

Answer 3

SELECT   pro_type 产品类型, MAX (DECODE (rn, 1, domain, NULL)) 对应领域1,
         MAX (DECODE (rn, 2, domain, NULL)) 对应领域2,
         SUM (DECODE (stat, 'Complete', 1, 0)) COMPLETE,
         SUM (DECODE (stat, 'Testing', 1, 0)) testing,
         SUM (DECODE (stat, 'Planning', 1, 0)) planning
    FROM (SELECT a.*,
                 DENSE_RANK () OVER (PARTITION BY pro_type ORDER BY domain)
                                                                           rn
            FROM a)
GROUP BY pro_type
结果
Row#   产品类型  对应领域1      对应领域2          COMPLETE         TESTING  PLANNING1       1001      a               b                 1               1      1
2       1002      f               x                 0               3      1
3       1003      i                                 2               0      1

Answer 4

SELECT a.*,
                 DENSE_RANK () OVER (PARTITION BY pro_type ORDER BY domain)
                                                                           rn
            FROM a
第一步，按产品类型分组，按领域编号（dense_rank为并列编号）
结果
品型号  产品编号   对应领域      状态        rn 
1001    01        a         Testing     1
1001    02        a         Complete     1
1001    03        b         Planning     2
1002    01        f         Testing     1
1002    02        x         Testing     2
1002    03        f         Testing     1
1002    04        x         Planning    2
1003    01        i         Complete    1
1003    02        i         Complete    1
1003    03        i         Planning    1然后用docode生成如下数据
品型号  产品编号   对应领域      状态        rn  decode(rn,1,domain,null)  DECODE (rn, 2, domain, NULL)  DECODE (stat, 'Complete', 1, 0)) ,DECODE (stat, 'Testing', 1, 0),DECODE (stat, 'Planning', 1, 0)
1001    01        a         Testing     1  a      null    0   1  0
1001    02        a         Complete     1 a      null    1   0  1
1001    03        b         Planning     2 null     b     0   0  1
1002    01        f         Testing     1  f      null    0   1  0
1002    02        x         Testing     2  null   x       0   1  0
1002    03        f         Testing     1  f      null    0   1  0
1002    04        x         Planning    2  null    x      0   0  1
1003    01        i         Complete    1  i      null    1   0  0
1003    02        i         Complete    1  i      null    1   0  0
1003    03        i         Planning    1  i      null    0   0  1最后再按产品类型分组，对后面几个用decode产生和字段，两个max,3个sum 就出来你要的结果

Answer 5

SQL> select * from t1 order by type,num;TYPE  NUM        AREA       STATUS
----- ---------- ---------- ------------------------------
1001  01         a          Testing
1001  02         a          Complete
1001  03         b          Planning
1002  01         f          Testing
1002  02         x          Testing
1002  03         f          Testing
1002  04         x          Planning
1003  01         i          Complete
1003  02         i          Complete
1003  03         i          Planning已选择10行。select type 产品类型,regexp_substr(area,'[^,]') 对应领域1,regexp_substr(area,'[^,]',1,2) 对应领域2,
(length(status)-length(replace(status,'Complete','')))/length('Complete') 
Complete,
(length(status)-length(replace(status,'Testing','')))/length('Testing') Testing,
(length(status)-length(replace(status,'Planning','')))/length('Planning') 
Planning from(
select type,wmsys.wm_concat(num) num,
wmsys.wm_concat(distinct area) area,
wmsys.wm_concat(status) status from t1
group by type)
/产品  对应领域 对应领域 COMPLETE TESTING PLANNING
----- -------- -------- -------- ------- --------
1001  a        b               1       1        1
1002  f        x               0       3        1
1003  i                        2       0        1