php - 将 2 个 SQL 查询合并为一个 - 显示总销售额和净销售额

Question

我有以下两个查询 - 一个显示总销售额，一个显示付费销售额(查询之间的唯一区别是添加的条件 - flag_payment = 1)。

输出为两列 - 供应商名称，然后是总销售额或已付销售额(以美元计)(总销售额 - 查询 #1，已付 - 查询 #2)。如果可能，我想合并这两个查询，以便输出供应商名称、总销售额和已付销售额(即 3 列)。

查询 1:销售总额

SELECT C.value , SUM( SL.price ) 
FROM sales_listings SL
INNER JOIN sales S ON SL.sale_id = S.id
INNER JOIN custom_fields_data C ON SL.listing_id = C.owner_id
WHERE C.field_id =11
GROUP BY C.value

查询 2:付费销售

SELECT C.value , SUM( SL.price ) 
FROM sales_listings SL
INNER JOIN sales S ON SL.sale_id = S.id
INNER JOIN custom_fields_data C ON SL.listing_id = C.owner_id
WHERE S.flag_payment = 1 AND C.field_id =11
GROUP BY C.value

有没有办法合并以下两个查询？谢谢!

Answer 1

您可以使用条件聚合:

SELECT 
    c.value, 
    SUM(CASE WHEN s.flag_payment = 0 THEN sl.price ELSE 0 END) AS GrossSales,
    SUM(CASE WHEN s.flag_payment = 1 THEN sl.price ELSE 0 END) AS PaidSales
FROM sales_listings sl
INNER JOIN sales s 
    ON sl.sale_id = s.id
INNER JOIN custom_fields_data c 
    ON sl.listing_id = c.owner_id
WHERE
    c.field_id = 11
GROUP BY c.value

---

由于在您对销售总额的原始查询中，没有针对 s.flag_payment 的过滤器，您可以省略条件 SUM:

SELECT 
    c.value, 
    SUM(sl.price) AS GrossSales,
    SUM(CASE WHEN s.flag_payment = 1 THEN sl.price ELSE 0 END) AS PaidSales
FROM sales_listings sl
INNER JOIN sales s 
    ON sl.sale_id = s.id
INNER JOIN custom_fields_data c 
    ON sl.listing_id = c.owner_id
WHERE
    c.field_id = 11
GROUP BY c.value