我有 2 張桌子（歷史和負責人）。他們需要根據服務日期加入。
歷史表：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 |
|---|---|---|---|---|
| 1 | 2021-10-15 | 3 | 123 | 湯姆·霍蘭德 |
| 2 | 2021-10-25 | 5 | 123 | 湯姆·霍蘭德 |
| 3 | 2022-01-14 | 2 | 123 | 湯姆·霍蘭德 |
負責表：
2999-12-31 表示責任人沒有結束日期（當前）
| 客戶 ID | 客戶名稱 | 責任人ID | 負責人姓名 | 責任開始日期 | 責任結束日期 |
|---|---|---|---|---|---|
| 123 | 湯姆·霍蘭德 | 77 | 托馬斯·安德森 | 2020-09-17 | 2021-10-17 |
| 123 | 湯姆·霍蘭德 | 88 | 湯姆·克魯斯 | 2021-10-18 | 2999-12-31 |
| 123 | 湯姆·霍蘭德 | 99 | 斯坦·李 | 2022-01-07 | 2999-12-31 |
我的代碼生成多行，因為 2022-01-14 服務日期屬于責任表中的多個日期范圍：
SELECT h.Id,
h.ServiceDate,
h.Hours,
h.ClientId,
h.ClientName,
r.ResponsibleName
FROM History AS h
LEFT JOIN Responsible AS r
ON (h.ClientId = r.ClientId AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate)
上面查詢的輸出是：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 | 負責人姓名 |
|---|---|---|---|---|---|
| 1 | 2021-10-15 | 3 | 123 | 湯姆·霍蘭德 | 托馬斯·安德森 |
| 2 | 2021-10-25 | 5 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 2 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 2 | 123 | 湯姆·霍蘭德 | 斯坦·李 |
從技術上講，輸出是正確的（因為 2022-01-14 介于 2021-10-18 - 2999-12-31 之間以及 2022-01-07 - 2999-12-31 之間），但不是我需要的。
我想知道是否可以實作 2 個輸出：
1）如果服務日期在責任表的多個日期范圍內，責任人應該是責任開始日期更接近服務日期的人：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 | 負責人姓名 |
|---|---|---|---|---|---|
| 1 | 2021-10-15 | 3 | 123 | 湯姆·霍蘭德 | 托馬斯·安德森 |
| 2 | 2021-10-25 | 5 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 2 | 123 | 湯姆·霍蘭德 | 斯坦·李 |
2) 保留所有行，如果服務日期在責任表的多個日期范圍內，但在責任人之間平均分配時間：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 | 負責人姓名 |
|---|---|---|---|---|---|
| 1 | 2021-10-15 | 3 | 123 | 湯姆·霍蘭德 | 托馬斯·安德森 |
| 2 | 2021-10-25 | 5 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 1 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 1 | 123 | 湯姆·霍蘭德 | 斯坦·李 |
uj5u.com熱心網友回復：
第一個，我們可以使用視窗函式來應用行號，根據ServiceDate與 is 的接近ResponsibleStartDate程度，然后我們可以只選擇第一行 per h.Id。如果有平局，我們可以通過選擇能給我們確定順序的東西來打破它，例如ORDER BY {DATEDIFF expression}, ResponsibleName。
;WITH cte AS
(
SELECT h.Id,
h.ServiceDate,
h.Hours,
h.ClientId,
h.ClientName,
r.ResponsibleName,
RankOrderedByProximityToServiceDate = ROW_NUMBER() OVER
(PARTITION BY h.Id
ORDER BY ABS(DATEDIFF(DAY, ResponsibleStartDate, ServiceDate)))
FROM dbo.History AS h
LEFT JOIN dbo.Responsible AS r
ON (h.ClientId = r.ClientId
AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate)
)
SELECT Id, ServiceDate, Hours, ClientId, ClientName, ResponsibleName
FROM cte WHERE RankOrderedByProximityToServiceDate = 1;
輸出：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 | 負責人姓名 |
|---|---|---|---|---|---|
| 1 | 2021-10-15 | 3 | 123 | 湯姆·霍蘭德 | 托馬斯·安德森 |
| 2 | 2021-10-25 | 5 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 2 | 123 | 湯姆·霍蘭德 | 斯坦·李 |
第二個不需要 CTE，我們可以簡單地將Hoursin除以h存在的行數h.Id，然后將其限制為 2 個小數位：
SELECT h.Id,
h.ServiceDate,
Hours = CONVERT(decimal(11,2),
h.Hours * 1.0
/ COUNT(h.Id) OVER (PARTITION BY h.Id)),
h.ClientId,
h.ClientName,
r.ResponsibleName
FROM dbo.History AS h
LEFT JOIN dbo.Responsible AS r
ON (h.ClientId = r.ClientId
AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate);
輸出：
| ID | 服務日期 | 小時 | 客戶 ID | 客戶名稱 | 負責人姓名 |
|---|---|---|---|---|---|
| 1 | 2021-10-15 | 3.00 | 123 | 湯姆·霍蘭德 | 托馬斯·安德森 |
| 2 | 2021-10-25 | 5.00 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 1.00 | 123 | 湯姆·霍蘭德 | 湯姆·克魯斯 |
| 3 | 2022-01-14 | 1.00 | 123 | 湯姆·霍蘭德 | 斯坦·李 |
兩者都在這個 db<>fiddle中展示。
uj5u.com熱心網友回復：
我在第 1 部分的嘗試 - 如果在同一開始日期有多個責任人，它將不起作用。
WITH
"all_services" AS (
SELECT
h.Id,
h.ServiceDate,
h.Hours,
h.ClientId,
h.ClientName,
r.ResponsibleName,
r.ResponsibleStartDate
FROM History AS h
LEFT JOIN Responsible AS r
ON h.ClientId = r.ClientId
AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate
),
"most_recent_key" AS (
SELECT
ServiceDate,
ClientId,
MAX(ResponsibleStartDate) AS "ResponsibleStartDate"
FROM all_services
GROUP BY ServiceDate, ClientId
)
SELECT Id, ServiceDate, Hours, ClientId, ClientName, ResponsibleName
FROM all_services
INNER JOIN most_recent_key
USING (ServiceDate, ClientId, ResponsibleStartDate)
無論如何發布它作為與 Aaron 更好的解決方案的對比，作為我自己的學習點。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/428257.html