将 UNION 添加到 MySQL 中的复杂 SELECT

本文介绍了将 UNION 添加到 MySQL 中的复杂 SELECT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想帮助添加一个 UNION 以获取查询的所有月份.我尝试在一堆地方添加 union 但就是做对了.

I would like help adding a UNION to get all the months to a query. I tried adding union in a bunch of spots but just can't get it right.

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 一起正常工作.

Where do the union clauses go in this query? I think there needs to be something like the following code but I don't understand where to put it to work correctly with the GROUP BY or 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 在这里

推荐答案

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

Use a left join based on a nested query with union.

使用 Coalesce 应用您的默认值.

Use Coalesce to apply your default values.

在 join 子句上移动 where 条件

Move where condition on join clause

警告，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

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

Another way exist using a global union between your orginal result and the default value, but this method require a second group by...

这篇关于将 UNION 添加到 MySQL 中的复杂 SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Union