mysql - 我可以子查询吗？

Question

我一直在尝试让查询/子查询来处理某些统计信息，但失败了，所以我从头开始。我现在可以获得我想要的结果，但它仍然在两个单独的查询中。我对子查询几乎没有经验，但经过研究，我的内心告诉我，我应该能够在一个查询中完成此操作。所有信息都在一张表中，但我需要使用两个单独的 GROUP BY 才能获取正确的数据。我希望有人能帮助我解决这个问题，或者至少为我指出正确的方向......提前致谢

SELECT MONTH(bookADhistory)-1 AS monthNum,
    COUNT(DISTINCT bookIDHistory) AS totalBooks,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 300 THEN bookIDHistory end) AS breaches
    FROM bookhistory
    JOIN book ON bookID = bookIDHistory
    WHERE bookID = 44
    GROUP BY MONTH (bookADhistory) ORDER BY monthNum;

SELECT MONTH(historyCreated)-1 AS monthNum,
    COUNT(DISTINCT CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory end) AS delays,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 200 THEN bookIDHistory end) AS extns,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 100 THEN bookIDHistory end) AS lateClose
    FROM bookhistory
    JOIN book ON bookID = bookIDHistory
    WHERE bookID = 44
    GROUP BY MONTH (historyCreated) ORDER BY monthNum;

Answer 1

可能有一些方法可以简化这个过程，但是使用您所拥有的并将它们粘贴到子查询中将如下所示:

SELECT
    *
FROM
    (

        SELECT MONTH(bookADhistory)-1 AS monthNum,
            COUNT(DISTINCT bookIDHistory) AS totalBooks,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 300 THEN bookIDHistory end) AS breaches
        FROM bookhistory
            JOIN book ON bookID = bookIDHistory
        WHERE bookID = 44
        GROUP BY MONTH (bookADhistory) 
        ORDER BY monthNum
    ) t1
    INNER JOIN
    (

        SELECT MONTH(historyCreated)-1 AS monthNum,
            COUNT(DISTINCT CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory end) AS delays,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 200 THEN bookIDHistory end) AS extns,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 100 THEN bookIDHistory end) AS lateClose
        FROM bookhistory
            JOIN book ON bookID = bookIDHistory
        WHERE bookID = 44
        GROUP BY MONTH (historyCreated) 
        ORDER BY monthNum;
    ) t2 ON
        t1.monthNum = t2.monthNum