mysql - 具有多个嵌套选择的 LEFT JOIN

Question

下面的语句令人惊讶地有效，但我不确定加入同一个表 3 次是否有效。我必须禁用 ONLY_FULL_GROUP_BY 才能使其正常工作。

有 2 张 table 在玩。第一个是包含经销商信息的主表，第二个是采购表，其中包含关联经销商的金额、日期和id在主表(assoc)中。

我需要三样东西。年初至今的销售额，对特定经销商当年的销售额进行求和。去年的销售额，与上一年的情况相同。最后只需获取最新的购买日期和金额即可。

用户需要能够通过这些值(lys、ytd 等)进行过滤，因此将它们作为变量加入似乎是可行的方法。数据库大小约为 7,000 条记录。

SELECT 
    d.*,
    ytd_total,
    lys_total,
    last_amount,
    last_purchase

FROM Distributor as d
LEFT JOIN (
    SELECT
        assoc, SUM(amount) ytd_total
        FROM purchases
        WHERE db = 1 AND purchase_date >= '{$year}-01-01'
        GROUP BY assoc
) AS ytd
ON ytd.assoc = d.id

LEFT JOIN (
    SELECT
        assoc, SUM(amount) lys_total
        FROM purchases
        WHERE db = 1 AND purchase_date BETWEEN '{$lyear}-01-01' AND '{$lyear}-12-31'
        GROUP BY assoc
) AS lys
ON lys.assoc = d.id

LEFT JOIN (
    SELECT
        assoc, amount last_amount, purchase_date last_purchase
        FROM purchases
        WHERE db = 1
        GROUP BY assoc
) AS lst
ON lst.assoc = d.id

WHERE ........

Answer 1

您可以在每个聚合查询中执行更多工作。我认为这更符合您的需求:

select d.*, pa.ytd_total, pa.lys_total, pa.last_purchase_date, p.amount
from distributor d left join
     (select p.assoc,
             sum(case when p.purchase_date >= '{$year}-01-01' then p.amount end) as ytd_total,
             sum(case when p.purchase_date BETWEEN '{$lyear}-01-01' AND '{$lyear}-12-31' then p.amount end) as lys_total,
             max(p.purchase_date) as last_purchase_date             
      from purchases p
      where p.db = 1
      group by p.assoc
     ) pa left join
     purchases p
     on pa.assoc = p.assoc and pa.last_purchase_date = p.purchase_date;