如何為每個不同的值只選擇一行

我有一個很長的查詢，其中包括我需要為報告執行的子查詢和連接。它執行沒有錯誤，但結果顯示每個值有 11 行，當我需要它顯示每個值 1 行時。我正在使用’select distinct’，這是我認為我需要用來實現這個目標的，但它似乎沒有工作。我究竟做錯了什麼？

每個不同的事件我只需要顯示 1 行的值是“cases.casenum”。

這是查詢：

SELECT distinct
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS TOTAL_LOST_FILES, cases.casenum AS 'Case Number', user_case_data.discharged_date AS 'Discharged Date',

(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM (cases LEFT JOIN case_checklist ON cases.casenum=case_checklist.case_id)
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
ORDER BY user_case_data.discharged_date ASC;

這是輸出的一個例子……這使得我們很容易看到我們要做什麼 - 沒有空格，“案例編號”列中的每個特定值只有一行。

SELECT 
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS "Total Lost Files", cases.casenum AS "Case Number", user_case_data.discharged_date AS "Discharged Date",

MAX(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

MAX(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

MAX(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

MAX(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

MAX(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

MAX(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

MAX(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

MAX(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM cases 
LEFT JOIN case_checklist ON cases.casenum = case_checklist.case_id
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num AND case_notes.topic LIKE 'LOS Case Status Update'
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
GROUP BY cases.casenum, user_case_data.discharged_date
ORDER BY user_case_data.discharged_date ASC;

您可能可以PIVOT將數據轉換為列

SELECT a.casenum AS [Case Number]
   , a.TotalLostFiles
   , a.discharge_date AS [Discharged Date]
   , [101] AS [Advised Attorney]
   , [LOS Case Status Update] AS [GENDOC_31_Mailed]
   , [LOS Updated Lein Ltr] AS [Sent Updated Lien Ltr]
   , [109] AS [Time Allocation Completed]
   , [110] AS [Attorney Signed Affidavit]
   , [111] AS [Lien Letters Sent]
   , [112] AS [Sent Lien to Counsel]
   , [113] AS [Received Costs and Transferred]
FROM (
   SELECT DISTINCT c.casenum
       , (
           SELECT COUNT(DISTINCT casenum)
           FROM user_case_data
           WHERE casenum = c.casenum 
               AND discharge_date >= '##START##'
               AND discharge_date <= '##END##'
       ) AS TotalLostFiles
       , cn.topic
       , cn.note_date
       , cc.code
       , cc.due_date
       , ucd.discharge_date
   FROM case c
   LEFT JOIN case_checklist cc ON cc.case_id = c.casenum
       AND cc.code IN ('101', '109', '110', '111', '112', '113')
   LEFT JOIN user_case_data ucd ON usd.casenum = c.casenum
       AND ucd.discharge_date BETWEEN '##START##' AND '##END##'
   LEFT JOIN case_notes cn ON cn.case_num = c.casenum 
       AND cn.topic IN ('LOS Case Status Update', 'LOS Updated Lein Ltr')
) a
PIVOT (
   MAX(due_date) FOR cc.code IN ([101], [109], [110], [111], [112], [113])
) b
PIVOT (
   MAX(note_date) FOR cn.topic IN ([LOS Case Status Update], [LOS Updated Lein Ltr])
) c

引用自：https://dba.stackexchange.com/questions/257121