mysql - 图表数据的 SQL 查询帮助

Question

这是我们的目标:

我们要为特定用户选择每个月以及每个月获得的收入。

这是我们的表格

产品、购买、用户、月份

用户 1..* 产品，产品 1..* 购买

产品有一个列“user_id”，购买有一个列“product_id”

months 只是一个以字符串形式包含每个月的表格。目前，我们正在使用它来进行一些左连接，如下所示。

SELECT months.name, IFNULL(sum(purchase.price), 0) as revenue
FROM months
    LEFT JOIN purchase
        ON DATE_FORMAT(purchase.purchase_date, '%M') = months.name
        AND DATE_FORMAT(purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
        AND purchase.status = 2
GROUP BY name
ORDER BY months.id ASC;

这对所有用户和本月进行的所有购买都非常有效(购买状态为 2 表示完成)。下一部分是如何根据用户 ID 进行过滤？表“product”有我们正在寻找的 user_id，表 purchase 有一个“product_id”。每次我尝试某事时，它要么什么都不做，要么删除所有我们不想要的空值。

尝试@NathanialWoolswhere 子句将删除收入为零(或 null，因为 IFNULL 语句)的所有行。这是我尝试过的:

SELECT name, IFNULL(sum(purchase.price), 0) as revenue
FROM months
    LEFT JOIN purchase
        ON DATE_FORMAT(purchase.purchase_date, '%M') = months.name
        AND DATE_FORMAT(purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
        AND purchase.status = 2
    LEFT JOIN product
        ON product.id = purchase.product_id
WHERE product.user_id = 1
GROUP BY name
ORDER BY months.id ASC;

Answer 1

如果您有一个用户列表，您可以从它开始，加入几个月，然后加入产品。 (如果您不只是从产品开始)。

SELECT u.user_id, m.name, IFNULL(sum(p.price), 0) as revenue 
FROM user u
CROSS JOIN months m
LEFT JOIN product d
    ON u.user_id = d.user_id
LEFT JOIN purchase p
    ON DATE_FORMAT(p.purchase_date, '%M') = m.name
    AND DATE_FORMAT(p.purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
    AND p.product_id = d.product_id
    AND p.status = 2
WHERE u.user_id = <user you care about>
GROUP BY u.user_id, m.name
ORDER BY m.id ASC;