我有以下两个查询-一个显示总销售额,一个显示付费销售额(查询之间的唯一区别是附加条件-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
有没有一种方法可以组合以下两个查询?谢谢!
您可以使用条件聚合:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句