选择一个网格领土/销售人员/类别连击，所有行项目有不止一个数量

Question

使用的AdventureWorks 2008 R2，我要查询一个网格，

1. 列表上的垂直每个地域/销售人员组合轴
2. 解释横轴上的每个子类别
3. 每个小区指定销售的总数（唯一的SalesOrderID s）的结合all the line items该类别/地区/销售人员组合具有2 or more量

下面是一个示例（由数据，我不知道如何查询真正的东西！）：

      M.Bikes  Chains  Gloves 
Northwest John Doe  15   4   3 
Canada  John Doe  4   2   1 
Northwest Jill Doe  0   5   3 
Canada  Jill Doe  1   5   1 
etc 

我觉得下面的SQL将得到我这个山地自行车（SUBCAT ID 1）但我不知道如何轻松添加更多列。如果我开始为每列一个独立的查询，它会变得非常慢，速度非常快（尤其是当我有所有这些列归队在一起！）

select 
    terr.Name as Territory, 
    sp.BusinessEntityID, 
    SUM(case when detail.OrderQty > 1 then 1 else 0 end) as MatchingSales 

from 
    sales.salesorderdetail detail 
    inner join sales.SalesOrderHeader header on header.SalesOrderID = detail.SalesOrderID 
    inner join sales.SalesPerson sp on sp.BusinessEntityID = header.SalesPersonID 
    inner join sales.SalesTerritory terr on terr.TerritoryID = sp.TerritoryID 
    inner join sales.SpecialOfferProduct sop on sop.ProductID = detail.ProductID 
    inner join Production.Product on Product.ProductID = sop.ProductID 
    inner join Production.ProductSubcategory subcat on subcat.ProductSubcategoryID = Product.ProductSubcategoryID 

where 
    subcat.ProductSubcategoryID = 1 --mountain bikes 

Answer 1

我想说我不给前言本”没有安装AdventureWorks，所以我无法检查查询...我也不知道销售人员名称栏是如何调用的，所以您可能不得不改变它，但是在这里，您只需添加每个子类别顶部的一列。我也假设原始查询是正确的。

select 
terr.Name as Territory, 
sp.Name SalesPerson, 
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 1 then 1 else 0 end) as MBikes, 
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 2 then 1 else 0 end) as Chains, 
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 3 then 1 else 0 end) as Gloves 
from 
sales.salesorderdetail detail 
inner join sales.SalesOrderHeader header on header.SalesOrderID = detail.SalesOrderID 
inner join sales.SalesPerson sp on sp.BusinessEntityID = header.SalesPersonID 
inner join sales.SalesTerritory terr on terr.TerritoryID = sp.TerritoryID 
inner join sales.SpecialOfferProduct sop on sop.ProductID = detail.ProductID 
inner join Production.Product on Product.ProductID = sop.ProductID 
inner join Production.ProductSubcategory subcat on subcat.ProductSubcategoryID = Product.ProductSubcategoryID 
group by terr.Name, sp.Name