用于将行数转换为列的SQL查询

SELECT
    CustomerID,
    SUM(CASE WHEN OrderDate BETWEEN '2022-01-01' AND '2022-12-31' THEN 1 ELSE 0 END) AS Orders2022,
    SUM(CASE WHEN OrderDate BETWEEN '2021-01-01' AND '2021-12-31' THEN 1 ELSE 0 END) AS Orders2021
FROM
    Orders
GROUP BY
    CustomerID;

SELECT *
FROM
    (SELECT CustomerID, YEAR(OrderDate) AS OrderYear, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID, YEAR(OrderDate)) AS SourceTable
PIVOT
    (SUM(OrderCount) FOR OrderYear IN ([2022], [2021])) AS PivotTable;

SELECT
    c.CustomerID,
    o2022.OrderCount AS Orders2022,
    o2021.OrderCount AS Orders2021
FROM
    Customers c
LEFT JOIN
    (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY CustomerID) o2022
ON
    c.CustomerID = o2022.CustomerID
LEFT JOIN
    (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders WHERE OrderDate BETWEEN '2021-01-01' AND '2021-12-31' GROUP BY CustomerID) o2021
ON
    c.CustomerID = o2021.CustomerID;