Group by和condition在select语句中

Question

如果参与者同时拥有" success ";和";error"状态，需要返回为"partial_error"，否则按原样返回。

我在下面准备了一个正在工作的查询。我认为这是不正确的方式。你能建议一下吗?

SELECT
T.PARTICIPANT_ID,
(CASE
WHEN COUNT(*) > 1 THEN 'PARTIAL_ERROR'
WHEN ISNULL(MAX(RLV.DESCRIPTION), '') = '' THEN MAX(T.LINE_STATUS)
ELSE MAX(RLV.DESCRIPTION)
END) LINE_STATUS
FROM 
(SELECT
PARTICIPANT_ID,
wp.LINE_STATUS
FROM 
[PROSPER].[PR_WKDAY_PAYFILE_PARTICIPANTS] wp
WHERE 
wp.LINE_STATUS <> 'CANCELLED'
GROUP BY 
PARTICIPANT_ID, wp.LINE_STATUS) T
LEFT JOIN 
[dbo].[LOOKUP_VALUE] rlv ON rlv.VALUE = T.LINE_STATUS
GROUP BY
T.PARTICIPANT_ID

Answer 1

我认为你不需要在FROM子句中使用子查询。您的查询可以缩短为

SELECT PARTICIPANT_ID,
(CASE
WHEN COUNT(DISTINCT wp.LINE_STATUS) > 1 THEN 'PARTIAL_ERROR'
ELSE ISNULL(MAX(RLV.DESCRIPTION), MAX(wp.LINE_STATUS))
END) AS LINE_STATUS
FROM   [PROSPER].[PR_WKDAY_PAYFILE_PARTICIPANTS] wp
LEFT JOIN [dbo].[LOOKUP_VALUE] rlv ON rlv.VALUE = wp.LINE_STATUS
WHERE  wp.LINE_STATUS <> 'CANCELLED'
GROUP BY PARTICIPANT_ID