30、SQL数据分组、排序与分页查询技巧

SQL数据分组、排序与分页查询技巧

1. 数据分组与排序

1.1 分组查询示例

在数据处理中，分组查询是常见的操作。以下是一个分组查询的示例，它使用 CUBE 和 ROLLUP 进行分组，并将结果合并到另一个表中：

WITH LastDay AS
(
  SELECT 
    GROUPING_ID(
      custid, empid,
      YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ) AS grp_id,
    custid, empid,
    YEAR(orderdate) AS orderyear,
    MONTH(orderdate) AS ordermonth,
    DAY(orderdate) AS orderday,
    SUM(qty) AS qty
  FROM dbo.Orders
  WHERE orderdate = '20140419'
  GROUP BY
    CUBE(custid, empid),
    ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate))
)
MERGE INTO dbo.MyGroupingSets AS TGT
USING LastDay AS SRC
  ON EXISTS(
  SELECT SRC.grp_id, SRC.orderyear, SRC.ordermonth, SRC.orderday, SRC.custid, SRC.empid