PS 之前的帖子已被洗掉，希望這次能接觸到更多的人。
我有巨大的歷史資料集，我需要為與客戶端關聯的所有行的服務日期分配值（ResponsibleName - 從 JSON 格式列）到一個新的“責任”列。
每個客戶都可以在特定日期范圍內擁有唯一的負責人（JSON 列）：
- 如果代碼列包含主管，則將此員工分配給僅作為責任人的關聯行。
- 最后，如果代碼列包含員工，則將 JSON 列（針對特定日期范圍）中的相關負責人分配給責任人
第一步我沒有問題，但是我找不到實作第二個（最后一個）陳述句的解決方案。
與客戶表連接的原始歷史表：
SELECT h.Id,
h.ServiceDate
h.ClientId,
cl.ClientName,
h.EmployeeName,
cl.ResponsibleJSON,
h.Codes
FROM History AS h
JOIN ClientTable AS cl
ON (h.ClientId = cl.ClientId)
該表的輸出：
| ID | 服務日期 | 客戶 ID | 客戶名稱 | 員工姓名 | 負責任的JSON | 代碼 |
|---|---|---|---|---|---|---|
| 1 | 2020-05-06 | 123 | 約翰·史密斯 | 克里斯埃文斯 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 員工, 辦公室 |
| 2 | 2020-05-08 | 123 | 約翰·史密斯 | 湯姆·霍蘭德 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 主管，遠程 |
| 3 | 2020-05-11 | 123 | 約翰·史密斯 | 克里斯埃文斯 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 員工, 辦公室 |
| 4 | 2020-05-15 | 123 | 約翰·史密斯 | 托馬斯·安德森 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 員工, 辦公室 |
| 5 | 2020-06-10 | 123 | 約翰·史密斯 | 湯姆·霍蘭德 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 主管，辦公室 |
| 6 | 2020-06-17 | 123 | 約翰·史密斯 | 托馬斯·安德森 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 員工，遠程 |
| 7 | 2020-06-22 | 123 | 約翰·史密斯 | 伊隆面具 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 員工，遠程 |
| 8 | 2020-07-01 | 123 | 約翰·史密斯 | 湯姆·霍蘭德 | [{"ResponsibleName":"Kevin Costner","ResponsibleStartDate":"2019-02-14","ResponsibleEndDate":"2020-05-31"},{"ResponsibleName":"Tom Cruise", "ResponsibleStartDate": "2020-06-01","ResponsibleEndDate":null}] | 主管，遠程 |
我為所有客戶端創建了包含所有 JSON 資料的表：
SELECT c.ClientId,
c.ClientFullName,
c.ResponsibleJSON,
JSON_VALUE(X.VALUE,'$.ResponsibleName') AS ResponsibleName,
JSON_VALUE(X.VALUE,'$.ResponsibleStartDate') AS ResponsibleStartDate,
ISNULL(JSON_VALUE(X.VALUE,'$.ResponsibleEndDate'), '2999-12-31') AS ResponsibleEndDate
FROM ClientTable AS c
CROSS APPLY OPENJSON(c.ResponsibleJSON) AS X
ORDER BY c.ClientFullName, ResponsibleStartDate
現在我需要以某種方式將它們組合起來以生成下面的列：
期望的輸出：
| 負責任的 |
|---|
| 凱文·科斯特納 |
| 湯姆·霍蘭德 |
| 凱文·科斯特納 |
| 凱文·科斯特納 |
| 湯姆·霍蘭德 |
| 湯姆·克魯斯 |
| 湯姆·克魯斯 |
| 湯姆·霍蘭德 |
我需要幫助的代碼可以在下面找到。我不知道我是否可以在 CASE 陳述句中進行 OUTER APPLY，并且我認為 JOIN 應該在 ClientId 上，并檢查責任開始日期和責任結束日期之間的服務日期。
SELECT h.Id,
h.ServiceDate
h.ClientId,
cl.ClientName,
h.EmployeeName,
cl.ResponsibleJSON,
h.Codes,
CASE
WHEN h.Codes LIKE '%Supervisor%' THEN h.EmployeeName --- 1st statement
WHEN --- here should be the second statement. I don't know how to implement it.
END AS Responsible
FROM History AS h
JOIN ClientTable AS cl
ON (h.ClientId = cl.ClientId)
uj5u.com熱心網友回復：
Codes當列不包含時，您似乎只想查找 JSON Supervisor。以下查詢應該做你想做的事：
SELECT
c.Id,
c.ServiceDate,
c.ClientId,
c.ClientName,
c.EmployeeName,
c.Codes,
Supervisor =
CASE WHEN c.Codes LIKE '%Supervisor%' THEN c.EmployeeName
ELSE (
SELECT TOP (1)
j.ResponsibleName
FROM OPENJSON(c.ResponsibleJSON)
WITH (
ResponsibleName nvarchar(100),
ResponsibleStartDate date,
ResponsibleEndDate date
) j
WHERE c.ServiceDate >= j.ResponsibleStartDate
AND (j.ResponsibleEndDate IS NULL OR c.ServiceDate <= j.ResponsibleEndDate)
)
END
FROM ClientTable c;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/428258.html
上一篇：根據服務日期加入表