同一查询结果集中具有不同where子句的多个和

Question

我有几个表，我将它们连接在一起，并从中选择不同的列和和，我想组合select语句，以便所有结果都在同一个结果集中。我目前有一个类似的查询：

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))

这会产生一个数据集，看起来像：

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

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

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))

这会产生一个类似的结果集：

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

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

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

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

Answer 1

好吧，如果你不能在一个查询中完成所有操作，那么你可以简单地将两个查询连接在一起：

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

Answer 2

使用这个概念：

SUM ( CASE WHEN ConditionIsMet THEN Column1 ELSE 0 END ),
SUM ( CASE WHEN OtherCondIsMet THEN Column2 ELSE 0 END )