SQL查询统计

最近一个周一直在做测评分析，既然是分析就离不开查询统计，一点心得体会与大家一起分享。

sql行列转换

我想做统计时行列转换应该是最常见的操作之一。现在我们拿NorthWind数据库作为例子，由于这个数据库大家都比较熟悉了我也不在这里过多的阐述所用到的表的具体信息。

假设现在我们要得到每一笔订单每种产品的订购数量，就可以这样：

SELECT dbo.[Order Details].OrderID,dbo.Categories.CategoryName,dbo.Products.ProductName,dbo.[Order Details].Quantity

FROM dbo.Orders

INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID

INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID

但是在做统计的时候往往这样的显示是不够的，假设现在我要统计每笔订单每类产品的数量（当然事先我们已经知道产品的种类）。这是我们就要用到行列转换，对，也就是我们要将我们的结果中的行数据作为列来显示。具体怎么做呢？

SELECT

dbo.Orders.OrderID,

SUM(CASE WHEN CategoryName='Beverages' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Condiments' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Confections' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Dairy Products' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Grains/Cereals' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Meat/Poultry' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Produce' THEN Quantity ELSE 0 END),

SUM(CASE WHEN CategoryName='Seafood' THEN Quantity ELSE 0 END)

FROM dbo.Orders

INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID

INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID

GROUP BY orders.OrderID

ORDER BY dbo.Orders.OrderID

上面是一种方法（注意上面Sum不能换成Max，因为每类商品有多种），当然还有别的方法，假如你用是SQL SERVER 2005或以上版本，我们可能会这样：

SELECT *

FROM

(SELECT dbo.[Order Details].OrderID,dbo.Categories.CategoryName,dbo.[Order Details].Quantity

FROM dbo.Orders

INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID

INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID)

AS A

PIVOT

(

    SUM(A.Quantity) FOR A.CategoryName IN ([Beverages],[Condiments],[Confections],[Dairy Products],[Grains/Cereals],[Meat/Poultry],[Produce],[Seafood])

)

AS B

上面就显示了Pivot函数的强大了，具体用法我就不再具体说了。但是就上面的方法对于再稍微复杂一点的情形就不行了（或许是还有什么方法我目前还不知道吧），例如假设对于我们上面商品类别进行了分等次划分，每类商品都有好(Good)、一般(General)、差(Bad)三种档次（当然这些等次也在另一张表中存储，假设有一张表叫ProductLevel（LevelID,LevelName）），而我们要显示的结果是下面这种形式。

订购批次	Beverages			Condiments			…
	Good	General	Bad	Good	General	Bad	…

这个时候我想应该用Pivot就没办法了吧（上面说了，如果有人知道用Pivot可实现请告诉我一下，谢谢了），但是用第一种方法就很容易了：

SELECT

dbo.Orders.OrderID,

SUM(CASE WHEN CategoryName='Beverages' AND LevelName='Good' THEN Quantity ELSE 0 END),

SUM (CASE WHEN CategoryName='Beverages' AND LevelName='General' THEN Quantity ELSE 0 END),

SUM (CASE WHEN CategoryName='Beverages' AND LevelName='Bad'