mysql - MySQL 有问题

Question

我有一个 MySQL 问题，这里是查询:

SELECT        JAN.Sales_Phase, JAN.January, FEB.Febuary, MAR.March, APR.April, MAY.May, JUN.June, JUL.July, AUG.August, SEP.September, OCT.October, NOV.November, 
                         DEC.December
FROM            (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS January
                          FROM            MasterTable_TM
                          WHERE        (Sales_Phase = 'Credits Card') OR
                                                    (Sales_Phase = 'PO Received') AND (PO_Month = '1')
                          GROUP BY Sales_Phase) AS JAN LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS Febuary
                               FROM            MasterTable_TM AS MasterTable_TM_1
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '2')
                               GROUP BY Sales_Phase) AS FEB ON FEB.Sales_Phase = JAN.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS March
                               FROM            MasterTable_TM AS MasterTable_TM_2
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '3')
                               GROUP BY Sales_Phase) AS MAR ON MAR.Sales_Phase = FEB.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS April
                               FROM            MasterTable_TM AS MasterTable_TM_3
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '4')
                               GROUP BY Sales_Phase) AS APR ON APR.Sales_Phase = MAR.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS May
                               FROM            MasterTable_TM AS MasterTable_TM_4
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '5')
                               GROUP BY Sales_Phase) AS MAY ON MAY.Sales_Phase = APR.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS June
                               FROM            MasterTable_TM AS MasterTable_TM_5
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '6')
                               GROUP BY Sales_Phase) AS JUN ON JUN.Sales_Phase = MAY.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS July
                               FROM            MasterTable_TM AS MasterTable_TM_6
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '7')
                               GROUP BY Sales_Phase) AS JUL ON JUL.Sales_Phase = JUN.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS August
                               FROM            MasterTable_TM AS MasterTable_TM_7
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '8')
                               GROUP BY Sales_Phase) AS AUG ON AUG.Sales_Phase = JUL.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS September
                               FROM            MasterTable_TM AS MasterTable_TM_8
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '9')
                               GROUP BY Sales_Phase) AS SEP ON SEP.Sales_Phase = AUG.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS October
                               FROM            MasterTable_TM AS MasterTable_TM_9
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '10')
                               GROUP BY Sales_Phase) AS OCT ON OCT.Sales_Phase = SEP.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS November
                               FROM            MasterTable_TM AS MasterTable_TM_10
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '11')
                               GROUP BY Sales_Phase) AS NOV ON NOV.Sales_Phase = OCT.Sales_Phase LEFT OUTER JOIN
                             (SELECT        Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS December
                               FROM            MasterTable_TM AS MasterTable_TM_11
                               WHERE        (Sales_Phase = 'Credits Card') OR
                                                         (Sales_Phase = 'PO Received') AND (PO_Month = '12')
                               GROUP BY Sales_Phase) AS DEC ON DEC.Sales_Phase = NOV.Sales_Phase

9月和10月有数据记录，但其他月份根本没有数据。我试图运行此查询，但返回零结果。如果我删除其他月份并在查询中只留下九月和十月，就会有结果返回给我。

如何显示其他月份，即使它为空？使用了 COALESCE () 函数，但它好像不起作用。

Answer 1

你真的不需要那么多连接来实现数据透视表，只需像这样分组和案例:

SELECT Sales_Phase
     , SUM(case PO_Month when 1 then PO_Amount_USD else 0 end) AS January
     , SUM(case PO_Month when 2 then PO_Amount_USD else 0 end) AS Febuary
     , SUM(case PO_Month when 3 then PO_Amount_USD else 0 end) AS March
     , SUM(case PO_Month when 4 then PO_Amount_USD else 0 end) AS April
     , SUM(case PO_Month when 5 then PO_Amount_USD else 0 end) AS May
     , SUM(case PO_Month when 6 then PO_Amount_USD else 0 end) AS June
     , SUM(case PO_Month when 7 then PO_Amount_USD else 0 end) AS July
     , SUM(case PO_Month when 8 then PO_Amount_USD else 0 end) AS August
     , SUM(case PO_Month when 9 then PO_Amount_USD else 0 end) AS September
     , SUM(case PO_Month when 10 then PO_Amount_USD else 0 end) AS October
     , SUM(case PO_Month when 11 then PO_Amount_USD else 0 end) AS November
     , SUM(case PO_Month when 12 then PO_Amount_USD else 0 end) AS December
  FROM MasterTable_TM
 WHERE (Sales_Phase = 'Credits Card') 
    OR (Sales_Phase = 'PO Received') 
   AND (PO_Month between '1' and '12') -- these conditions do really make no sense, but leave it as the original sample code
 GROUP BY Sales_Phase

您的查询有两个问题:

1. 如果一月份没有数据，第一个子查询什么也不会返回，所以其他左连接显然什么也不会返回。
2. (Sales_Phase = 'Credits Card') OR (Sales_Phase = 'PO Received') AND (PO_Month = '1') 太困惑了，如果 Sales_Phase = 'Credits Card' 你想要返回数据从整个月？ AND的优先级高于OR