我試圖找出從 2021 年 1 月 1 日開始，每 1 周有多少跑步者報名。我對查詢沒有任何問題，但我無法對日期進行 mod (%) 以達到 7 天的數字標記來結束我的查詢。
---My Schema
CREATE TABLE runners (
"runner_id" INTEGER,
"registration_date" DATE
);
INSERT INTO runners
("runner_id", "registration_date")
VALUES
(1, '2021-01-01'),
(2, '2021-01-03'),
(3, '2021-01-08'),
(4, '2021-01-15');
runner_id registration_date
1 2021-01-01
2 2021-01-03
3 2021-01-08
4 2021-01-15
所需的 O/P：
start_of_week signups
2021-01-01 2
2021-01-08 1
2021-01-15 1
---My Query
WITH data1 AS (
SELECT
runner_id,
registration_date,
REGISTRATION_DATE - (DATEDIFF(DAY, REGISTRATION_DATE, '2021-01-01' ) % 7) AS start_of_week
FROM runners
)
select start_of_week, count(runner_id) as signup from data1 group by start_of_week;
錯誤
運算元型別沖突：日期與 int 不兼容
我也嘗試將日期格式切換為 mod 7 但沒有用。
registration_date - ((registration_date - '2021-01-01') % 7)
錯誤
資料型別 date 和 varchar 在減法運算子中不兼容。
uj5u.com熱心網友回復：
根據評論，試試這個：
WITH data1 AS (
SELECT
runner_id,
registration_date,
(datediff(DAY, '2021-01-01',REGISTRATION_DATE ) / 7) 1 AS start_of_week
FROM runners
)
select
dateadd(week, start_of_week - 1, '2021-01-01'),
count(runner_id) as signup
from data1
group by start_of_week;
資料庫小提琴
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=91a00cf6223f3c1367b6e8f014dccd6f
如果你想擺脫時間，你也可以把它作為日期
select
cast(dateadd(week, start_of_week - 1, '2021-01-01') as date),
count(runner_id) as signup
from data1
group by start_of_week;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/396950.html
標籤：sql sql-server 查询语句 约会时间