如何基于SQL CASE语句在WHERE条件下筛选记录
如何基于SQL CASE语句在WHERE条件下筛选记录
提问于 2014-07-01 19:42:11
下面的case语句用于加载代码表。当gli-src =‘8’时，我必须找到加载到带有代码表的TRAN_CODE =7的记录的计数。
INSERT INTO codes
SELECT CASE
WHEN [cash-amt] > 0 THEN '1'
WHEN [prod-n] = '42'
AND [tran-code] IN ( '-1', '-2', '-3' ) THEN '17'
WHEN [gli-src] = '8' THEN '7'
WHEN [prod-n] = '42'
AND [gli-src] = 'L'
AND [tran-code] IN ( '1', '2', '3' ) THEN '7'
WHEN [gli-src] = 'L'
AND [prod-n] = '60'
AND [tran-code] IN ( '4', '5', '6' ) THEN '7'
ELSE '4'
END AS TRAN_CODE
FROM txn 这就是我尝试过的，尽管我很困惑如何过滤掉其他的情况。
select COUNT(1) from txn
where [CASH-AMT] <= 0
and
.
.
.
. ????
and [gli-src] = '8'回答 1
Stack Overflow用户
回答已采纳
发布于 2014-07-01 19:46:48
SELECT COUNT(1)
FROM ( SELECT CASE
WHEN [cash-amt] > 0 THEN '1'
WHEN [prod-n] = '42'
AND [tran-code] IN ( '-1', '-2', '-3' ) THEN '17'
WHEN [gli-src] = '8' THEN '7'
WHEN [prod-n] = '42'
AND [gli-src] = 'L'
AND [tran-code] IN ( '1', '2', '3' ) THEN '7'
WHEN [gli-src] = 'L'
AND [prod-n] = '60'
AND [tran-code] IN ( '4', '5', '6' ) THEN '7'
ELSE '4'
END AS TRAN_CODE,
[CASH-AMT],
[gli-src]
FROM txn) AS T
WHERE [CASH-AMT] <= 0
AND [gli-src] = '8'
AND TRAN_CODE = 7页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24518077
复制相关文章
相似问题
腾讯云开发者
