Nick's Blog

如何处理SQL报告数据

本篇将用举例子的方法来介绍如何处理SQL的报告数据,主要涉及到的SQL语句有INTERSECT, EXCEPT, UNION, UNION ALL, GROUP BY ROLLUP, GROUP BY CUBE, GROUPING BY GROUP SET.

INTERSECT: 同时属于两组数据。

EXCEPT: 属于上方表格的数据,但是不属于下方表格的数据。

SELECT Store_ID, Store_Name
FROM SalesOrder.SalesEmployee
INTERSECT/EXCEPT
SELECT Store_ID, Store_Name
FROM SaleOrders.Orders

UNION/UNION ALL

SELECT Store_ID, Store_Name
FROM SalesOrder.NAmerican_Stores
WHERE Store_Name LIKE ‘bik%’
UNION/UNION ALL
SELECT Store_ID, Store_Name
FROM SaleOrders.Europe_stores
ORDER BY Store_Name

GROUP BY ROLLUP/CUBE

SELECT Product_ID, CustomerID, SUM(SubTotal)
AS SubTotal, SUM(TotalDue) AS TotalDue
FROM SaleOrders.Orders
WHERE DueDate >= ‘2012-01-01’
GROUP BY ROLLUP (Product_ID, CustomerID)

SELECT Product_ID, CustomerID, SUM(SubTotal)
AS SubTotal, SUM(TotalDue) AS TotalDue
FROM SaleOrders.Orders
WHERE DueDate >= ‘2012-01-01’
GROUP BY CUBE(Product_ID, CustomerID)

GROUPING BY GROUP SET

SELECT Product_ID, CustomerID, SUM(SubTotal)
AS SubTotal, SUM(TotalDue) AS TotalDue
FROM SaleOrders.Orders
WHERE DueDate >= ‘2012-01-01’
GROUP BY GROUPING SETS((Product_ID, CustomerID),(cUSTOMERid), ())