所以我写了以下查询:
SELECT
Store,
Revenue
FROM [mydb].[dbo].[orders] as orders
WHERE
order_date BETWEEN '2015-10-26' AND '2015-11-22'
AND
type_desc = 'north'
AND store_number
NOT IN(
SELECT DISTINCT store_number
FROM [mydb].[dbo].[orders]
WHERE
order_date BETWEEN '2015-10-26' AND '2015-11-22'
AND
type_desc = 'south')
GROUP BY
Store,
Revenue
该查询的目的是找到商店出现在北部而不是南部的收入。下面的查询非常有效,但是我想知道的是如何使此查询更简洁的版本,以便可以将其他内容集成到其中。我想我几乎想加入其他列来计算内容。因此,我将在最终结果中有多个专栏,例如所有商店的去年收入,今年的收入,北部和南方的收入等...
您可以使用有条件的聚合来按北部和南部收入对商店进行分类:
SELECT store,
SUM(CASE WHEN type_desc = 'north' THEN Revenue ELSE 0 END) as north_revenue,
SUM(CASE WHEN type_desc = 'south' THEN Revenue ELSE 0 END) as south_revenue
FROM [mydb].[dbo].[orders] o
WHERE o.order_date BETWEEN '2015-10-26' AND '2015-11-22'
GROUP BY store;
这似乎是您想要的基本信息。
如果您只想拥有北部收入和南部收入的商店,则可以添加:
HAVING SUM(CASE WHEN type_desc = 'south' THEN Revenue ELSE 0 END) = 0
(注意:如果Revenue
可能为负,那么您可能只想使用THEN 1
而不是THEN Revenue
来计数值。)