mysql - 合并两个 SQL 查询，同时能够访问每个查询中的元素

Question

我有以下两个问题:

    SELECT applicationid as LastMonthID, month(sold) AS LastMonthSold, SUM(quantity) AS LastMonthTotalSales
FROM(
    SELECT applicationid, sold, quantity
    FROM Sales
    JOIN WebApplication
    ON applicationid = id) AS AllMonthlySales
WHERE month(sold) = month(now())-1
GROUP BY applicationid;


SELECT applicationid ThisMonthID, month(sold) AS ThisMonthSold, SUM(quantity) AS ThisMonthTotalSales
FROM ( 
    SELECT applicationid, sold, quantity
    FROM Sales
    JOIN WebApplication
    ON applicationid = id) As AllMonthlySales
WHERE month(sold) = MONTH(now())
GROUP BY applicationid;

这些查询产生以下结果:

1. 申请编号
2. 销售发生的月份(数字)
3. 每个应用程序的销售数量总和

我想做以下事情:

1. 将上个月的数量与本月每个应用程序的数量进行比较。如果销售数量减少 5%，它应该给我那个特定应用程序的 applicationid。我将如何做到这一点？

Answer 1

您可以使用条件聚合在一个查询中执行此操作。这只会在两个不同的列中为您提供本月和上个月的数量:

SELECT  applicationid,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) AS ThisMonth,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) AS LastMonth
FROM    Sales AS s
        INNER JOIN WebApplication AS w
            ON w.ApplicationID = s.ID
WHERE   MONTH(Sold) INT (MONTH(NOW()), MONTH(NOW()) - 1)
GROUP BY w.ApplicationID;

然后你可以限制它到 thow where ThisMonth比 LastMonth 小 5% - (ThisMonth / LastMonth < 0.95)与 HAVING子句:

SELECT  applicationid,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) AS ThisMonth,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) AS LastMonth
FROM    Sales AS s
        INNER JOIN WebApplication AS w
            ON w.ApplicationID = s.ID
WHERE   MONTH(Sold) INT (MONTH(NOW()), MONTH(NOW()) - 1)
GROUP BY w.ApplicationID
HAVING (ThisMonth / LastMonth) < 0.95;

注意MySQL 允许您引用 HAVING 中的列别名子句，对于其他 DBMS，您将需要重复表达式:

HAVING SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) / 
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) < 0.95

最后，我不会用WHERE MONTH(sold) = MONTH(NOW()) ，首先它会返回前几年这个月的结果，其次，它不是可搜索的，而是使用以下方法获取本月的第一天:

DATE_FORMAT(NOW() ,'%Y-%m-01')

和下个月的第一天:

DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01')

然后您可以比较以下日期:

WHERE sold >= DATE_FORMAT(NOW(),'%Y-%m-01')
AND Sold < DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01')

这意味着 Sold 上的任何索引现在可以使用了。