目标是在网格上显示所有卖家的列表,其中包含销售额和价值。
应始终显示卖家列表,即使他们没有销售。
像这样:
seller | sales | value
John 10 1000
Marie 20 2000
Jamie 15 1500
Arnold 0 0
Peter 0 0
为此,下一个查询工作正常:
SELECT tsl.sellers,
COUNT(ts.sales) AS sales,
COALESCE(SUM(ts.value),0) AS value
FROM tab_sellers tsl
LEFT JOIN tab_sales ts ON tsl.id_seller = ts.seller
GROUP BY tsl.seller
问题是当我需要按日期范围过滤时。
SELECT tsl.sellers,
COUNT(ts.sales) AS sales,
COALESCE(SUM(ts.value),0) AS value
FROM tab_sellers tsl
LEFT JOIN tab_sales ts ON tsl.id_seller = ts.seller
WHERE ts.date_sale BETWEEN '2017-05-01' and '2017-07-04'
GROUP BY tsl.seller
考虑到只有约翰和玛丽在此日期范围内有销售额,结果是:
seller | sales | value
John 5 500
Marie 10 1000
但是,预期结果是:
seller | sales | value
John 5 500
Marie 10 1000
Jamie 0 0
Arnold 0 0
Peter 0 0
我很欣赏解决这个问题的想法。
将条件放在And
而不是where
中。
请尝试以下查询:
SELECT tsl.sellers,
COUNT(ts.sales) AS sales,
COALESCE(SUM(ts.value),0) AS value
FROM tab_sellers tsl
LEFT JOIN tab_sales ts ON tsl.id_seller = ts.seller AND ts.date_sale BETWEEN '2017-05-01' and '2017-07-04'
GROUP BY tsl.seller
希望这对您有所帮助。