连接和空值数据到透视表

Question

在SQL方面没有什么问题。我要做的是数一数不同的状态，并将其显示为支点表。代码：

SELECT TAIL_NO,
       isnull(pt.A_GR_ST,0) as A_GR_ST,
       isnull(pt.O_R_LR_FLTS,0)
 FROM (select distinct
                X."Tail_#" as TAIL_NO,
                COUNT(*) as qty,
                X.statuss
         FROM X
        group by X.Tail_#, X.statuss) p    
PIVOT
 (
  MAX([qty])
  FOR [statuss] In([A_GR_ST], [O_R_LR_FLTS])
 ) As pt
order by TAIL_NO

对我来说很完美，并给出了输出：

 TAIL_NO   A_GR_ST    O_R_LR_FLTS
--------- ---------  -------------
 RUD       0          1
 EW        7          2
 ED        100        10 

每个状态号(A_GR_ST，O_R_LR_FLTS)可以按日计数进行更深的分割: 0-1d,2-5d，我想要做的是将这些天拆分到现有的枢轴表中，通过日计数查看拆分状态结果，如果没有，我必须在表中看到零。产出应如下：

 TAIL_NO    DAYS   A_GR_ST   O_R_LR_FLTS
--------- ------- --------- ------------- 
 RUD       0-1d    0         0
 RUD       2-5d    0         1
 EW        0-1d    7         2
 EW        2-5d    0         0
 ED        0-1d    40        3
 ED        2-5d    60        7

怎么做？预先谢谢你。

当我使用代码时：

SELECT TAIL_NO, 
       days,
       isnull(pt.A_GR_ST,0) as A_GR_ST,
       isnull(pt.O_R_LR_FLTS,0)
FROM (select distinct
               X."Tail_#" as TAIL_NO,
               COUNT(*) as qty,
               X.statuss,
               X.days
        FROM X
       group by X.Tail_#, X.statuss, X.days) p
PIVOT
 (
  MAX([qty])
  FOR [statuss] In([A_GR_ST],  [O_R_LR_FLTS])
 ) As pt
order by TAIL_NO

我得到一个输出：

 TAIL_NO    DAYS   A_GR_ST   O_R_LR_FLTS
--------- ------- --------- ------------- 
 RUD       2-5d    0         1
 EW        0-1d    7         2
 ED        0-1d    40        3
 ED        2-5d    60        7

不包括零行。我需要他们。

Answer 1

你应该生成你的缺失行。一个国际信息中心将是：

select 
  a.tail_no,
  a.statuss,
  days.days
  nvl(orig.qty, 0) as qty
from 
    (SELECT 
       X."Tail_#" as TAIL_NO, X.statuss
     FROM X
     GROUP BY X.Tail_#, X.statuss
     )a
   cross join (select distinct days from x) days
   left join (
       SELECT 
          X."Tail_#" as TAIL_NO,
          X.statuss,
          X.days,
          COUNT(*) as qty
        FROM X
        GROUP BY X.Tail_#, X.statuss, X.days
        ) orig
       ON a.tail_no = orig.tail_no and a.statuss=orig.statuss and a.days=orig.days

然后继续对此查询进行枢轴查询。

Answer 2

通过使用CTE并执行一些联接，可以实现以下目标：

with table_main as (
        SELECT TAIL_NO, DAYS, pt.A_GR_ST, pt.O_R_LR_FLTS
        FROM (select distinct
                      X."Tail_#" as TAIL_NO,
                      COUNT(*) as qty,
                      X.statuss,
                      X.DAYS as DAYS
                      FROM X
                     group by X.Tail_#,X.statuss,X.days) p

                PIVOT
                 (
                  MAX([qty])
                  FOR [statuss] In([A_GR_ST],  [O_R_LR_FLTS])
                    ) As pt
                  order by TAIL_NO),
table_days as (select d.days, t.tail_no from (
                    (select '0-1d' as days union all
                     select '2-5d') d
               cross join (select distinct TAIL_NO from table_main) t))
select td.DAYS, td.TAIL_NO, isnull(tm.A_GR_ST, 0), isnull(tm.O_R_LR_FLTS, 0)
  from table_days td
  left outer join table_main tm
  on tm.DAYS = td.days and tm.TAIL_NO = td.TAIL_NO;