同一查询结果集中具有不同where子句的多个求和 [英] Multiple Sums with different where clauses in same query result set

问题描述

我有几个表要连接在一起，并从中选择不同的列和总和，我想合并select语句，以便所有结果都在同一结果集中. 我目前有一个查询，如:

I have a few tables that I am joining together and selecting various columns and sums from it and i want to combine the select statements so that all results are in the same results set. i currently have a query like:

declare @year INT
declare @month INT
set @year = '2013'
set @month = '08'

select CAST(LEFT(b.name, 3) AS varchar(3))AS Region, a.model, sum(a.Number)AS Uniques
from Table1 a
inner join database2.....table2 b
on a.code = b.code
where Year(a.EventDate) = @year
and Month(a.EventDate) = @month
and a.make='Toyota'
group by CAST(LEFT(b.name, 3) AS varchar(3)), a.model
order by CAST(LEFT(b.name, 3) AS varchar(3))

这将显示一个类似于以下内容的数据集:

This brings up a dataset which looks something like:

Region   Model    Uniques
EST      Toyota      200
EST      Honda       350
CEN      Toyota      220
CEN       VW         150

然后我有另一个类似的查询，但是使用了不同的表和要求:

I then have another similar query, but which uses different tables and requirements:

select CAST(LEFT(c.name, 3) AS varchar(3)) AS Region, a.Model, sum(b.Number)As Sales
from Table1 a
left join Table3 b
on a.leadid = b.leadid
inner join Database1..Table2 c
on a.code = c.code
where Year(a.EventDate) = @year
and Month(a.EventDate) = @month
and a.make='Toyota'
group by a.Model, CAST(LEFT(c.Name, 3) AS varchar(3))
order by CAST(LEFT(c.Name, 3) AS varchar(3))

这会产生类似的结果集:

this brings up a similar results set:

 Region      Model       Sales
    EST      Toyota      150
    CEN       VW         80
    CEN      Toyota      75

我只想将这些结果配对或进行联合查询，这样我就可以得到类似的结果；

I just want to pair these results or make a joined query so I can have results like;

Region    Model       Uniques    Sales
EST       Toyota      200         150
EST       Honda       350          0
CEN       Toyota      220         75
CEN       vW          150         80

总共可能存在20-30个区域/模型组合，并且每个结果集都相同.

There are maybe 20-30 combinations of Region/Model that exist in total, and they are the same for each result set.

推荐答案

好吧，如果您不能在一个查询中完成全部操作，则可以将两个查询简单地连接在一起:

Well, if you cannot do it all in one query, then you can simply join the 2 queries together:

select
t1.region,
t1.model,
t1.uniques,
t2.sales
from
(select CAST(LEFT(b.name, 3) AS varchar(3))AS Region, a.model, sum(a.Number)AS Uniques
from Table1 a
inner join database2.....table2 b
on a.code = b.code
where Year(a.EventDate) = @year
and Month(a.EventDate) = @month
and a.make='Toyota'
group by CAST(LEFT(b.name, 3) AS varchar(3)), a.model
order by CAST(LEFT(b.name, 3) AS varchar(3))
) t1 
inner join
(
select CAST(LEFT(c.name, 3) AS varchar(3)) AS Region, a.Model, sum(b.Number)As Sales
from Table1 a
left join Table3 b
on a.leadid = b.leadid
inner join Database1..Table2 c
on a.code = c.code
where Year(a.EventDate) = @year
and Month(a.EventDate) = @month
and a.make='Toyota'
group by a.Model, CAST(LEFT(c.Name, 3) AS varchar(3))
order by CAST(LEFT(c.Name, 3) AS varchar(3))
) t2
on t1.region = t2.region 
and t1.model = t2.model

这篇关于同一查询结果集中具有不同where子句的多个求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！