我有一個包含以下資料的表：
user_id year week_number
1 2021 35
1 2021 27
1 2021 22
1 2021 50
1 2022 1
1 2022 7
1 2022 11
1 2022 15
1 2022 19
1 2022 2
1 2021 52
1 2022 3
1 2022 4
8 2021 48
8 2021 49
8 2021 50
8 2021 51
8 2021 52
8 2022 1
8 2022 2
8 2022 3
現在我想獲得 year1, week1 和 year2, week2 之間的所有周。例如，變數可以是：
-- Only get weeks from specific user. In this example its user 1.
userID = 1
year1 = 2021
week1 = 52
year2 = 2022
week2 = 05
我首先有以下查詢：
-- Where 2021, 2022, 52 and 05 are the variables.
SELECT
*
FROM
[db].[dbo].[table]
WHERE
[user_id] = 1
AND
[year] BETWEEN '2021' AND '2022'
AND
[week_number] BETWEEN '52' AND '05'
當我只有 1 年的時間可以選擇時，這很好用。但在這個例子中，我使用多個年。現在第 52 周屬于 2021 年，第 05 周到 2022 年。現在沒有顯示結果。
我也做了一個作業查詢，但它要求額外的引數：
-- Here I have 2 extra week numbers I have to give.
SELECT
*
FROM
[db].[dbo].[table]
WHERE
[user_id] = 1
AND
([year] = '2021'
AND
[week_number] BETWEEN '52' AND '52'
OR
[year] = '2022'
AND
[week_number] BETWEEN '01' AND '05')
我不認為這是一個很好的解決方案，因為我必須or每隔一年多寫一個。我相信事情可以在這里簡化，但不知道如何。
那么，有沒有辦法獲得我的變數之間的周數？不改變我的表結構。
uj5u.com熱心網友回復：
一個小的計算可能會有所幫助：
...
WHERE
([user_id] = 1) AND
([year] * 100 [week_number]) BETWEEN 202152 AND 202205
或者
...
WHERE
([user_id] = 1) AND
(202152 <= [year] * 100 [week_number]) AND
([year] * 100 [week_number] <= 202205)
uj5u.com熱心網友回復：
你可以......使用變數：
DECLARE @myUser int = 1,
@startYear int = 2021,
@endYear int = 2022,
@startWeek int = 5,
@endWeek INT = 13;
SELECT *
FROM [db].[dbo].[table]
WHERE [user_id] = @myUser
AND (
(@startYear = [year] AND @startWeek = [week_number] AND @startYear = @endYear AND @startWeek = @endWeek) --the selection is only one week
OR ([year] = @startYear AND [week_number] >= @startWeek AND [week_number] <= @endYear AND @startYear = @endYear AND @endWeek > @startWeek) --same year, multiple weeks
OR (@endYear > @startYear -- Spans multiple years
AND (
([year] >= @startYear AND [year] < @endYear AND [week_number] >= @startWeek ) --anything after start week for each year that is before the end year
OR ([year] <= @endYear AND [year] > @startYear AND [week_number] <= @endWeek) --anything before end week for any year after the start year
)
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/384634.html
標籤：sql sql-server
上一篇：SQLCASE滾動日期差異
下一篇：SQLWhere子句順序