使用 CASE 语句选择 SQL Server 2008 R2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20573779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select with CASE Statement SQL Server 2008 R2
提问by MCP_infiltrator
I have a query that uses a CASEstatement to score accounts. The query looks for values that are in a vector form. So for instance if I am a patient I can have multiple diagnosis codes, but they are not stored as column values, they are stored in another row like so:
我有一个使用CASE语句对帐户进行评分的查询。该查询查找矢量形式的值。因此，例如，如果我是一名患者，我可以有多个诊断代码，但它们不会存储为列值，而是存储在另一行中，如下所示：
VISIT_ID | CLASFCD
123 | 196.0
123 | 197.0
123 | 198.0
321 | 199.0
321 | 650.9
222 | 111
555 | ...
...
My query uses a Case Statment like so:
我的查询使用了一个 Case Statment，如下所示：
, CASE
WHEN DV.ClasfCd IN (
'196.0','196.1','196.2','196.3','196.5','196.6','196.8','196.9',
'197.0','197.1','197.2','197.3','197.4','197.5','197.6','197.7',
'197.8','198.2','198.3','198.4','198.5','199.1','209.7'
)
THEN 6
ELSE 0
END AS PRIN_DX_CD_5
I do this for 5 different groups of codes. What's happening is that if the criteria is met for one of those groups, the results come back in another row instead of on the same row. Here is an example of the data that I am getting back:
我对 5 组不同的代码执行此操作。发生的情况是，如果这些组中的一个满足条件，结果将返回到另一行而不是同一行。这是我返回的数据示例：
VISIT_ID | CC GROUP 1 | CC GROUP 2 | CC GROUP 3 | CC GROUP 4 | CC GROUP 5 | TOTAL
123 | 1 | 0 | 0 | 0 | 0 | 1
123 | 0 | 2 | 0 | 0 | 0 | 2
123 | 0 | 0 | 0 | 0 | 0 | 0
What I want returned is the following:
我想要返回的是以下内容：
VISIT_ID | CC GROUP 1 | CC GROUP 2 | CC GROUP 3 | CC GROUP 4 | CC GROUP 5 | TOTAL
123 | 1 | 2 | 0 | 0 | 0 | 3
321 | 1 | 0 | 0 | 0 | 6 | 6
The final total score cannot exceed 6.
最终总分不能超过6分。
The entire query in some brevity is here, it is part of a multi-part query, I am making changes to the original:
简短的整个查询在这里，它是多部分查询的一部分，我正在对原始查询进行更改：
SET ANSI_NULLS OFF
GO
DECLARE @SD DATETIME
DECLARE @ED DATETIME
SET @SD = '2013-01-01';
SET @ED = '2013-05-31';
-- @CM TABLE DECLARATION #############################################]
DECLARE @CM TABLE (
ENCOUNTER_ID VARCHAR(200)
, [MRN CM] VARCHAR(200)
, NAME VARCHAR(500)
, [CC GRP ONE SCORE] VARCHAR(20)
, [CC GRP TWO SCORE] VARCHAR(20)
, [CC GRP THREE SCORE] VARCHAR(20)
, [CC GRP FOUR SCORE] VARCHAR(20)
, [CC GRP FIVE SCORE] VARCHAR(20)
, [CC LACE SCORE] INT
)
--####################################################################]
INSERT INTO @CM
SELECT
C.PT_NO
, C.MED_REC_NO
, C.PT_NAME
, C.PRIN_DX_CD_1
, C.PRIN_DX_CD_2
, C.PRIN_DX_CD_3
, C.PRIN_DX_CD_4
, C.PRIN_DX_CD_5
, CASE
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 0 THEN 0
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 1 THEN 1
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 2 THEN 2
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 3 THEN 3
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 4 THEN 4
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 5 THEN 5
WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) >= 6 THEN 6
END AS CC_LACE_SCORE
FROM (
SELECT DISTINCT PAV.PT_NO
, MED_REC_NO
, PT_NAME
, CASE
WHEN dv.ClasfCd IN (
)
THEN 1
ELSE 0
END AS PRIN_DX_CD_1
, CASE
WHEN DV.ClasfCd IN (
)
THEN 2
ELSE 0
END AS PRIN_DX_CD_2
, CASE
WHEN DV.ClasfCd IN (
)
THEN 3
ELSE 0
END AS PRIN_DX_CD_3
, CASE
WHEN DV.ClasfCd IN (
)
THEN 4
ELSE 0
END AS PRIN_DX_CD_4
, CASE
WHEN DV.ClasfCd IN (
)
THEN 6
ELSE 0
END AS PRIN_DX_CD_5
FROM smsdss.BMH_PLM_PtAcct_V PAV
JOIN smsdss.BMH_PLM_PtAcct_Clasf_Dx_V DV
ON PAV.PtNo_Num = DV.PtNo_Num
WHERE Dsch_Date BETWEEN @SD AND @ED
)C
GROUP BY C.PT_NO
, C.MED_REC_NO
, C.PT_NAME
, C.PRIN_DX_CD_1
, C.PRIN_DX_CD_2
, C.PRIN_DX_CD_3
, C.PRIN_DX_CD_4
, C.PRIN_DX_CD_5
ORDER BY C.Pt_No
SELECT * FROM @CM
thank you for your help,
感谢您的帮助，
回答by Gordon Linoff
The problem is that you are including the calculated PRIN_DX_columns in the aggregation. Instead, remove them from the aggregation and just choose the non-0 value (using max()):
问题是您PRIN_DX_在聚合中包含计算列。相反，将它们从聚合中删除，只需选择非 0 值（使用max()）：
SELECT C.PT_NO, C.MED_REC_NO, C.PT_NAME,
max(C.PRIN_DX_CD_1) as PRIN_DX_CD_1,
max(C.PRIN_DX_CD_2) as PRIN_DX_CD_2,
max(C.PRIN_DX_CD_3) as PRIN_DX_CD_3,
max(C.PRIN_DX_CD_4) as PRIN_DX_CD_4,
max(C.PRIN_DX_CD_5) as PRIN_DX_CD_5,
(case when max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) +
max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5) < 6
then max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) +
max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5)
else 6
end) as CC_LACE_SCORE
FROM (SELECT DISTINCT PAV.PT_NO, MED_REC_NO, PT_NAME,
(CASE WHEN dv.ClasfCd IN ()
THEN 1
ELSE 0
END) AS PRIN_DX_CD_1,
(CASE WHEN DV.ClasfCd IN ()
THEN 2
ELSE 0
END) AS PRIN_DX_CD_2
(CASE WHEN DV.ClasfCd IN ()
THEN 3
ELSE 0
END) AS PRIN_DX_CD_3,
(CASE WHEN DV.ClasfCd IN ()
THEN 4
ELSE 0
END) AS PRIN_DX_CD_4,
(CASE WHEN DV.ClasfCd IN ()
THEN 6
ELSE 0
END) AS PRIN_DX_CD_5
FROM smsdss.BMH_PLM_PtAcct_V PAV join
smsdss.BMH_PLM_PtAcct_Clasf_Dx_V DV
ON PAV.PtNo_Num = DV.PtNo_Num
WHERE Dsch_Date BETWEEN @SD AND @ED
) C
GROUP BY C.PT_NO, C.MED_REC_NO, C.PT_NAME
ORDER BY C.Pt_No;
I suspect the distinctin the subquery may not be necessary, but that depends on what your data really looks like.
我怀疑distinct子查询中的 可能不是必需的，但这取决于您的数据的真实情况。
回答by vittore
UPDATE:
更新：
You defenitly need to look into pivot as @Darren Kopp says
正如@Darren Kopp 所说，你肯定需要研究支点
Create table to map your values in INclauses with groups
创建表以将IN子句中的值与组映射
Then do pivot
然后做枢轴
Then simpify your case when to minimum(val, 6)using something like
然后在minimum(val, 6)使用类似的东西时简化你的情况
CREATE FUNCTION Minimum
(@Param1 Integer, @Param2 Integer)
Returns Table As
Return(Select Case When @Param1 < @Param2
Then @Param1 Else @Param2 End MinValue)
So your table cdmap would be
所以你的表 cdmap 将是
6 | '196.0'
6 | '196.1'
6 | '196.2'
SELECT ...., [1], [2], [4], [6]
FROM
(
FROM smsdss.BMH_PLM_PtAcct_V PAV
JOIN smsdss.BMH_PLM_PtAcct_Clasf_Dx_V DV
ON PAV.PtNo_Num = DV.PtNo_Num
JOIN cdmap c on c.ClasfCd = dv.ClasfCd
WHERE Dsch_Date BETWEEN @SD AND @ED
) AS SourceTable
PIVOT
(
...
FOR c.ClasfCd IN ([1], [2], [4], [6])
) AS PivotTable;
