Case 语句逻辑功能不全 [英] Case Statement Logic Not Fully Functional
问题描述
以下是我期望从我的脚本中得到的一些示例结果:示例 1:仅向客户 12 提供服务NEI2".那么结果应该是
AcctNum PStatus IStatus12 1 5示例 2:如果客户 21 有服务PN20"、PN4"和FL1"，那么结果应该是
AcctNum PStatus IStatus21 3 221 4 5响应将始终为Y".如果需要，您可以修改脚本.谢谢.
脚本如下:
SELECT 不同的 A.AcctNum,案件当 O.Order = 'NEI2' THEN '1'WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'当 AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'当 AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'其他'5'结束为 [PStatus],案件当 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'其他'5'结束为 [IStatus]来自 AData AS AD内部连接 AVisit AS AVON AD.Visit = AV.VisitAND AV.QueryID IN ('PNE1','PNE2','PN20','PN4','FL1','REF','FL2','FL6','NEU.G','HE.B','NOA')LEFT JOIN Order AS OON AD.Visit = O.VisitAND O.Order IN ('NEI2','DO2','FL25','VACHP');我已将您的查询修改为 group by AcctNum，以及 [Pstatus] 的值 和 [IStatus].
不要相信有可能实现您在示例 2 中的建议 - 即生成 SQL 分组/合并规则以将行值 (3,5)(4,5)(5,2) 合并为 (3,2)(4,5):
<块引用>示例 2:如果客户 21 有服务PN20"、PN4"和FL1"，则结果应该是
AcctNum PStatus IStatus21 3 221 4 5<块引用>
我的建议是将您的数据分组到 AcctNum，以及 [Pstatus] 和 [IStatus] 的值，因为任何更多"需要超出其价值的复杂性:
SELECT帐户编号，(案件当 O.Order = 'NEI2' THEN '1'WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'当 AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'当 AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'其他'5'结尾) AS [Pstatus],(案件当 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'其他'5'结尾) AS [I状态]来自 AData AS ADINNER JOIN AVisit AS AV ON AD.Visit = AV.Visit ANDAV.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 ANDO.Order IN ('NEI2','DO2','FL25','VACHP');按帐户编号分组，(案件当 O.Order = 'NEI2' THEN '1'WHEN AV.Query IN ('PNE1','PNE2') AND AV.Response = 'Y' THEN '2'当 AV.Query = 'PN20' AND AV.Response = 'Y' THEN '3'当 AV.Query = 'PN4' AND AV.Response = 'Y' THEN '4'其他'5'结尾)，(案件当 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'其他'5'结尾)Below are some example results that I am expecting from my script: Example 1: Customer 12 was provided only service 'NEI2'. then result should be
AcctNum PStatus IStatus
12 1 5
Example 2: If customer 21 had service 'PN20', 'PN4', and 'FL1' then the result should be
AcctNum PStatus IStatus
21 3 2
21 4 5
Response will always be 'Y'. You can modify the script if you need to. Thank you.
Below is the script:
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');
I have modified your query to group by AcctNum, and values of [Pstatus] and [IStatus].
Do not believe it is possible to achieve what you are suggesting in example 2 - i.e producing a SQL grouping/merging rule to consolidate row values (3,5)(4,5)(5,2) into (3,2)(4,5):
Example 2: If customer 21 had service 'PN20', 'PN4', and 'FL1' then the result should be
AcctNum PStatus IStatus
21 3 2
21 4 5
My suggestion is to group your data to AcctNum, and values of [Pstatus] and [IStatus] because anything "more" would require complexity beyond it's worth:
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)
这篇关于Case 语句逻辑功能不全的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
