Case语句逻辑功能不全
Case语句逻辑功能不全
提问于 2015-02-04 09:30:04
下面是我期望从我的脚本中得到的一些示例结果:示例1:仅为客户12提供了服务'NEI2‘。那么结果应该是
AcctNum PStatus IStatus
12 1 5示例2:如果客户21具有服务'PN20‘、'PN4’和'FL1‘，则结果应为
AcctNum PStatus IStatus
21 3 2
21 4 5响应将始终为'Y‘。如果需要，您可以修改脚本。谢谢。
以下是脚本：
SELECT distinct A.AcctNum,
CASE
WHEN O.Order = 'NEI2' THEN '1'
WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'
ELSE '5'
END AS [PStatus],
CASE
WHEN O.Order IN ('DO2','FL25','VACHP') THEN '1'
WHEN AV.Query = 'FL1' AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'REF' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query IN ('FL2','FL6','NEU.G','HE.B') AND AV.Response = 'Y' THEN '4'
WHEN AV.Query = 'NOA' AND AV.Response = 'Y' THEN '6'
ELSE '5'
END AS [IStatus]
FROM AData AS AD
INNER JOIN AVisit AS AV
ON AD.Visit = AV.Visit
AND AV.QueryID IN ('PNE1','PNE2','PN20','PN4','FL1','REF','FL2','FL6','NEU.G','HE.B','NOA')
LEFT JOIN Order AS O
ON AD.Visit = O.Visit
AND O.Order IN ('NEI2','DO2','FL25','VACHP');回答 2
Stack Overflow用户
发布于 2015-02-04 09:41:18
您想要按状态显示group by吗？
SELECT AcctNum,
(CASE WHEN O.Order = 'NEI2' THEN '1'
WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'
ELSE NULL
END) AS [Pstatus],
(CASE WHEN O.Order IN ('DO2','FL25','VACHP') THEN '1'
WHEN AV.Query = 'FL1' AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'REF' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query IN ('FL2','FL6','NEU.G','HE.B') AND AV.Response = 'Y' THEN '4'
WHEN AV.Query = 'NOA' AND AV.Response = 'Y' THEN '6'
ELSE NULL
END) AS [IStatus]
FROM AData AS AD INNER JOIN
AVisit AS AV
ON AD.Visit = AV.Visit
WHERE AV.QueryID IN ('PNE1', 'PNE2', 'PN20', 'PN4', 'FL1', 'REF', 'FL2','FL6', 'NEU.G','HE.B','NOA') LEFT JOIN
Order AS O
ON AD.Visit = O.Visit AND O.Order IN ('NEI2','DO2','FL25','VACHP');
GROUP BY AcctNum,
(CASE WHEN O.Order = 'NEI2' THEN '1'
WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'
ELSE NULL
END) AS [Pstatus],
(CASE WHEN O.Order IN ('DO2','FL25','VACHP') THEN '1'
WHEN AV.Query = 'FL1' AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'REF' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query IN ('FL2','FL6','NEU.G','HE.B') AND AV.Response = 'Y' THEN '4'
WHEN AV.Query = 'NOA' AND AV.Response = 'Y' THEN '6'
ELSE NULL
END) AS [IStatus];Stack Overflow用户
发布于 2015-02-04 11:27:40
我已将您的查询修改为group by AcctNum，以及[Pstatus]和[IStatus]的值。
不相信可以实现您在示例2中建议的结果-即生成一个SQL分组/合并规则来将行值(3,5)(4,5)(5,2)合并到(3，2)(4，5)中：
示例2:如果客户21具有服务'PN20‘、'PN4’和'FL1‘，则结果应为
AcctNum PStatus IStatus
21 3 2
21 4 5我的建议是将数据分组为AcctNum，以及[Pstatus]和[IStatus]的值，因为任何“更多”的值都需要超出其价值的复杂性：
SELECT
AcctNum,
(CASE
WHEN O.Order = 'NEI2' THEN '1'
WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'
ELSE '5'
END
) AS [Pstatus],
(CASE
WHEN O.Order IN ('DO2','FL25','VACHP') THEN '1'
WHEN AV.Query = 'FL1' AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'REF' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query IN ('FL2','FL6','NEU.G','HE.B') AND AV.Response = 'Y' THEN '4'
WHEN AV.Query = 'NOA' AND AV.Response = 'Y' THEN '6'
ELSE '5'
END
) AS [IStatus]
FROM AData AS AD
INNER JOIN AVisit AS AV ON AD.Visit = AV.Visit AND
AV.QueryID IN ('PNE1','PNE2','PN20','PN4','FL1','REF','FL2','FL6','NEU.G','HE.B','NOA')
LEFT JOIN Order AS O ON AD.Visit = O.Visit AND
O.Order IN ('NEI2','DO2','FL25','VACHP');
GROUP BY AcctNum,
(CASE
WHEN O.Order = 'NEI2' THEN '1'
WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'
ELSE '5'
END),
(CASE
WHEN O.Order IN ('DO2','FL25','VACHP') THEN '1'
WHEN AV.Query = 'FL1' AND AV.Response = 'Y' THEN '2'
WHEN AV.Query = 'REF' AND AV.Response = 'Y' THEN '3'
WHEN AV.Query IN ('FL2','FL6','NEU.G','HE.B') AND AV.Response = 'Y' THEN '4'
WHEN AV.Query = 'NOA' AND AV.Response = 'Y' THEN '6'
ELSE '5'
END)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28311963
复制相关文章
相似问题
腾讯云开发者
