按条件分组

jt123 发表于 Dev

jt123

例如，我有一个带有订单的表。该表具有键（Order.No），打开订单的日期（Order.Open）和关闭订单的日期（Order.Close）。

没有打开关闭
--------- ---------- ----------
2013-1208 2013-03-11 2013-03-26
2013-1272 2013-03-11 2013-03-11
2013-1273 2013-03-11 2013-03-11
2013-1274 2013-03-11 2013-03-11
2013-1275 2013-03-11 2013-03-11
2013-1280 2013-03-11 2013-06-26
2013-1281 2013-03-11 2013-04-18
2013-1282 2013-03-11 2013-03-14
2013-1287 2013-03-12 2013-04-18
2013-1291 2013-03-12 2013-03-12

现在，我要进行查询，我可以在该查询中找出每个月的最后一天仍有多少个订单未完成。

例如，我想找出一月的最后一天仍有多少订单：

在2月1日当天或之后关闭该订单，并在2月1日之前打开该订单：

SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year' FROM Orders
WHERE (Orders.Open < '2013-02-01') AND (Orders.Close >= '2013-02-01')

现在，如果我想获取每个月的此信息，则必须执行以下操作：

SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year'  FROM Orders
WHERE (Orders.Open < '2013-02-01') AND (Orders.Close >= '2013-02-01')

UNION

SELECT COUNT(Order.No) Open, '2' Month, '2013' 'Year'  FROM Orders
WHERE (Orders.Open < '2013-03-01') AND (Orders.Close >= '2013-03-01')

UNION

SELECT COUNT(Order.No) 'Open', '3' 'Month', '2013' 'Year' FROM Orders
WHERE (Orders.Open < '2013-04-01') AND (Orders.Close >= '2013-04-01')

UNION

SELECT COUNT(Order.No) 'Open', '4' Month, '2013' Year FROM Orders
WHERE (Orders.Open < '2013-05-01') AND (Orders.Close >= '2013-05-01')

我可以以某种方式简化此查询，这样我就不必每个月和每年都写一次吗？

所需的输出如下所示：

营业月份
---- ----- ----
684 1 2013
683 2 2013
760 3 2013
659 4 2013

加雷斯

您可以输入所需的月/年列表并加入其中，例如

SELECT  [Open] = COUNT(o.No),
        [Month] = DATEPART(MONTH, d.[Date]),
        [Year] = DATEPART(YEAR, d.[Date])
FROM    (VALUES
            ('2013-01-01'),
            ('2013-02-01'),
            ('2013-03-01'),
            ('2013-04-01'),
            ('2013-05-01'),
            ('2013-06-01')
        ) d (Date)
        LEFT JOIN Orders AS o
            ON o.[Open] < d.[Date]
            AND o.[Close] >= d.[Date]
GROUP BY d.Date;

如果您有日历表，则可以使用它代替硬编码日期列表。

如果您不想对所需的日期进行硬编码，则可以很容易地即时生成一个列表，首先生成一个数字列表：

WITH E1 AS -- 10 ROWS
(   SELECT  N = 1
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), E2 AS -- 10 X 10 = 100 ROWS
(   SELECT  N = 1
    FROM    E1 CROSS JOIN E1 AS E2
), E3 AS -- 100 x 100 = 10,000 ROWS
(   SELECT  N = 1
    FROM    E2 CROSS JOIN E2 AS E3
)
SELECT  N = ROW_NUMBER() OVER(ORDER BY N)
FROM    E3;

这只是一个示例，但是会生成10,000个连续数字，实际上，您可能不需要报告10,000个月，但是进行演示并没有什么害处。然后，您可以将此数字列表转换为日期列表：

WITH E1 (N) AS 
(   SELECT 1 
    FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), 
E2 (N) AS (SELECT 1 FROM E1 CROSS JOIN E1 AS E2), 
E3 (N) AS (SELECT 1 FROM E2 CROSS JOIN E2 AS E3)
SELECT  [Date] = DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY N) - 1, '19000101')
FROM    E3;

然后，您可以将其用作主表，并向订单左联接：

WITH E1 (N) AS 
(   SELECT 1 
    FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), 
E2 (N) AS (SELECT 1 FROM E1 CROSS JOIN E1 AS E2), 
E3 (N) AS (SELECT 1 FROM E2 CROSS JOIN E2 AS E3),
Dates AS 
(   SELECT  [Date] = DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY N) - 1, '19000101')
    FROM    E3
)
SELECT  [Open] = COUNT(o.No),
        [Month] = DATEPART(MONTH, d.[Date]),
        [Year] = DATEPART(YEAR, d.[Date])
FROM    Dates AS d
        LEFT JOIN Orders AS o
            ON o.[Open] < d.[Date]
            AND o.[Close] >= d.[Date]
WHERE   d.Date >= '20130101' -- OR WHATEVER DATE YOU LIKE
AND     d.Date < GETDATE();