代码之家 › 专栏 › 技术社区 › Jebathon

SQL Server-使用while循环缩短多个联合联接

tsql sql-server

Jebathon · 技术社区 · 6 年前

SELECT COUNT(*) AS [Counts], Week=1
    FROM TableA
    WHERE Date >= DATEADD(week,-1,GETDATE())
      AND Date <= GETDATE()
    UNION
    SELECT COUNT(*), 2
    FROM TableA
    WHERE Date >= DATEADD(week,-2,GETDATE())
      AND Date <= DATEADD(week,-1,GETDATE())
    UNION
    SELECT COUNT(*), 3
    FROM TableA
    WHERE Date >= DATEADD(week,-3,GETDATE())
      AND Date <= DATEADD(week,-2,GETDATE())
    UNION
    SELECT COUNT(*), 4
    FROM TableA
    WHERE Date >= DATEADD(week,-4,GETDATE())
      AND Date <= DATEADD(week,-3,GETDATE())

----------------
| Count | Week |
----------------
|   20  |   1  |
----------------
|   10  |   2  |
----------------
|   30  |   3  |
----------------
|   25  |   4  |
----------------

假设我想修改查询,使其返回过去10或20周。

如何缩短查询,使其循环数周?

declare @w int;
set @w = 10;
while @w <> 0
begin
    ...; 
    --how can I do union joins?
    set @w = @w - 1;
end

1 回复 | 直到 6 年前

Lukasz Szozda 6 年前

你可以避免 UNION 并使用 GROUP BY

SELECT datepart(week, Date) AS WeekNum, COUNT(*) AS counts
FROM TableA
WHERE Date >= DATEADD(week,-20,GETDATE()) -- num of weeks
GROUP BY datepart(week, Date);  -- week of the year

如果需要从1到n的nums,则:

WITH cte AS (
    SELECT datepart(year, Date) AS [year], 
           datepart(week, Date) AS WeekNum, 
           COUNT(*) AS counts
    FROM TableA
    WHERE Date >= DATEADD(week,-20,GETDATE()) -- num of weeks
    GROUP BY datepart(year, Date), datepart(week, Date)
)
SELECT ROW_NUMBER() OVER(ORDER BY [year] DESC, WeekNum DESC) AS WeekNum, counts
FROM cte;

编辑:

是的,如果今天是星期三,20周将给您一周时间从星期三开始

可由以下人员处理:

WHERE Date >= DATEADD(week,-20,GETDATE())
 => 
WHERE Date >= DATEADD(week,-20,
           CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS DATE))

推荐文章