SQL SERVER中 PIVOT和UNPIVOT的用法【分组统计】
USE LocalAppDB GO ---Use aggregate function SELECT C.customerid, city, CASE WHEN COUNT(orderid) = 0 THEN 'no_orders' WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders' WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders' END AS category FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid GROUP BY C.customerid, city; ---use pivot key word: Find city [no_orders]/[upto_two_orders]/[more_than_two_orders]'s CustomerID Count SELECT city,[no_orders],[upto_two_orders],[more_than_two_orders] FROM ( SELECT C.[customerid], [city], (CASE WHEN COUNT(orderid) = 0 THEN 'no_orders' WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders' WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders' END) AS [category] FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid GROUP BY C.customerid, city ) p PIVOT ( count(p.[customerid]) FOR p.category IN ([no_orders],[upto_two_orders],[more_than_two_orders]) ) AS pvt ---Can Use this SQL Statement To Replace PIVOT(Just Can Use After SQL 2005 Version ) Key Word SELECT city, COUNT(CASE WHEN category = 'no_orders' THEN customerid END) AS [no_orders], COUNT(CASE WHEN category = 'upto_two_orders' THEN customerid END) AS [upto_two_orders], COUNT(CASE WHEN category = 'more_than_two_orders' THEN customerid END) AS [more_than_two_orders] FROM (SELECT C.customerid, city, CASE WHEN COUNT(orderid) = 0 THEN 'no_orders' WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders' WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders' END AS category FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid GROUP BY C.customerid, city) AS D GROUP BY city; ---Now Test the UNPIVOT Key Word SELECT city,[no_orders],[upto_two_orders],[more_than_two_orders] INTO dbo.PivotedCategories FROM ( SELECT C.[customerid], [city], (CASE WHEN COUNT(orderid) = 0 THEN 'no_orders' WHEN COUNT(orderid) <= 2 THEN 'upto_two_orders' WHEN COUNT(orderid) > 2 THEN 'more_than_two_orders' END) AS [category] FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid GROUP BY C.customerid, city ) p PIVOT ( count(p.[customerid]) FOR p.category IN ([no_orders],[upto_two_orders],[more_than_two_orders]) ) AS pvt --- UPDATE dbo.PivotedCategories SET no_orders = NULL, upto_two_orders = 3 WHERE city = 'Madrid'; SELECT * FROM PivotedCategories --- SELECT city, category, num_custs FROM dbo.PivotedCategories UNPIVOT ( num_custs FOR category IN([no_orders], [upto_two_orders], [more_than_two_orders]) ) AS UNPvt ---Drop the Test Table DROP TABLE dbo.PivotedCategories; ---This Dome Test OVER Key Word SELECT orderid, customerid, COUNT(*) OVER(PARTITION BY customerid) AS num_orders FROM dbo.Orders WHERE customerid IS NOT NULL AND orderid % 2 = 1; ---Use Aggregate Function SELECT orderid, customerid, COUNT(*) AS num_orders FROM dbo.Orders WHERE customerid IS NOT NULL AND orderid % 2 = 1 GROUP BY orderid, customerid ---- SELECT seqno,username,ROW_NUMBER() OVER(ORDER BY seqno DESC) AS [Sseqno],password,enabled FROM dbo.aduser ORDER BY seqno ---- SELECT seqno,username,ROW_NUMBER() OVER(PARTITION BY username ORDER BY seqno DESC) AS [Sseqno],password,enabled FROM dbo.aduser ORDER BY seqno -- SELECT seqno,username,COUNT(*) OVER(PARTITION BY username) AS [CT],password,enabled FROM dbo.aduser ORDER BY seqno --- SELECT customerid FROM dbo.Customers EXCEPT SELECT customerid FROM dbo.Orders; --- SELECT customerid FROM dbo.Customers UNION SELECT customerid FROM dbo.Orders;
很悲催,之前写的SQL 相关文档人间蒸发鸟,又得慢慢积累,囧~