mysql - 将 UNION 添加到 MySQL 中的复杂 SELECT

Question

我想帮助添加一个 UNION 来获取查询的所有月份。我尝试在很多地方添加 union，但就是做对了。

SELECT MONTH(FirstPublishedToPortal) AS theMonth,
   YEAR(FirstPublishedToPortal) AS theYear,
   MONTHNAME(FirstPublishedToPortal) AS theMonthName,
   COUNT(DISTINCT PONum) AS POCount,

   SUM(unconfirmedEmailSent) AS unconfirmedEmailsSent,
   ( SELECT COUNT(DISTINCT PONum)
     FROM POFlags
     WHERE unconfirmedEmailSent = 0
     AND MONTH(FirstPublishedToPortal) = theMonth
     AND YEAR(FirstPublishedToPortal) = theYear
     AND VendorNum = '2222'
   ) AS onTimeConfirmed,

   SUM(lateEmailSent) AS lateEmailsSent,
   ( SELECT COUNT(DISTINCT PONum)
     FROM POFlags
     WHERE lateEmailSent = 0
     AND MONTH(FirstPublishedToPortal) = theMonth
     AND YEAR(FirstPublishedToPortal) = theYear
     AND VendorNum = '2222'
   ) AS onTimePOCount

FROM POFlags

WHERE VendorNum = '2222'
   AND FirstPublishedToPortal >= '2017-01-08'

GROUP BY theYear DESC, theMonth DESC
ORDER BY FirstPublishedToPortal DESC 

联合子句在这个查询中放在哪里？我认为需要有类似于以下代码的内容，但我不明白将它放在哪里才能与 GROUP BY 或 ORDER BY 一起正常工作。

(SELECT 1, null, 'January', 0, 0, 0, 0, 0)
UNION 
(SELECT 2, null, 'February', 0, 0, 0, 0, 0)
UNION 
(SELECT 3, null, 'March', 0, 0, 0, 0, 0)
.    
.
(SELECT 12, null, 'December, 0, 0, 0, 0, 0)

SQL FIDDLE HERE

Answer 1

使用基于带联合的嵌套查询的左连接。

使用 Coalesce 应用您的默认值。

在 join 子句上移动 where 条件

警告，2017 年是硬编码的

是这样的:http://sqlfiddle.com/#!9/458808/27

SELECT
    all_month.nMonth AS theMonth, 
    coalesce(YEAR(FirstPublishedToPortal),2017) AS theYear,
    all_month.sMonth AS theMonthName,
    coalesce(COUNT(DISTINCT PONum),0) AS POCount,

    coalesce(SUM(unconfirmedEmailSent),0) AS unconfirmedEmailsSent,
    coalesce(( SELECT COUNT(DISTINCT PONum)
       FROM POFlags
       WHERE unconfirmedEmailSent = 0
         AND MONTH(FirstPublishedToPortal) = theMonth
         AND YEAR(FirstPublishedToPortal) = theYear
         AND VendorNum = '2222'
    ),0) AS onTimeConfirmed,

    coalesce(SUM(lateEmailSent),0) AS lateEmailsSent,
    coalesce(( SELECT COUNT(DISTINCT PONum)
       FROM POFlags
       WHERE lateEmailSent = 0
         AND MONTH(FirstPublishedToPortal) = theMonth
         AND YEAR(FirstPublishedToPortal) = theYear
         AND VendorNum = '2222'
     ),0) AS onTimePOCount
FROM (
     (SELECT 1 nMonth, 'January' sMonth)   UNION ALL
     (SELECT 2 nMonth, 'February' sMonth ) UNION ALL
     (SELECT 3 nMonth, 'March' sMonth )    UNION ALL
     (SELECT 4 nMonth, 'April' sMonth )    UNION ALL
     (SELECT 5 nMonth, 'May' sMonth )      UNION ALL
     (SELECT 6 nMonth, 'June' sMonth )     UNION ALL
     (SELECT 7 nMonth, 'July' sMonth )     UNION ALL
     (SELECT 8 nMonth, 'August' sMonth )   UNION ALL
     (SELECT 9 nMonth, 'September' sMonth) UNION ALL
     (SELECT 10 nMonth, 'October' sMonth ) UNION ALL
     (SELECT 11 nMonth, 'November' sMonth )UNION ALL    
     (SELECT 12 nMonth, 'December' sMonth)
     ) all_month left join POFlags on
         MONTH(FirstPublishedToPortal)=nMonth and
         VendorNum = '2222' and
         FirstPublishedToPortal >= '2017-01-08'
GROUP BY
    theYear DESC, nMonth DESC
ORDER BY
    nMonth DESC 

存在另一种使用原始结果和默认值之间的全局联合的方法，但这种方法需要第二个分组...