sql-server-2008 - SQL 服务器 2008 : Using SIGN to get positive or negative value

Question

我有一个大表(数百万条记录)，其中包含每种交易类型的交易金额。我正在尝试获取金额大于、小于和等于 0 的每种交易类型的计数。我正在执行 3 个单独的查询来获取此信息，更改每个查询中的 WHERE 子句，然后将结果拼凑在一起之后。

有人可以推荐一种更智能的方式来编写此语句吗？

伪

SELECT 
TRANSACTION_CD
,COUNT(TRANSACTION_CD)
FROM [FINANCIAL_TRANSACTION] ft
INNER JOIN POLICY p ON p.POLICY_ID = ft.POLICY_ID
WHERE 
ft.EFFECTIVE_DT > '01/01/2008'
AND SIGN(ft.transaction_amt) = -1
GROUP BY 
TRANSACTION_CD
ORDER BY 
TRANSACTION_CD 

Answer 1

SELECT 
TRANSACTION_CD
,SUM(CASE WHEN ft.transaction_amt < 0 THEN 1 ELSE 0 END) AS NegativeCount
,SUM(CASE WHEN ft.transaction_amt = 0 THEN 1 ELSE 0 END) AS ZeroCount
,SUM(CASE WHEN ft.transaction_amt > 0 THEN 1 ELSE 0 END) AS PositiveCount
FROM [FINANCIAL_TRANSACTION] ft
INNER JOIN POLICY p ON p.POLICY_ID = ft.POLICY_ID
WHERE 
ft.EFFECTIVE_DT > '01/01/2008'
GROUP BY 
TRANSACTION_CD
ORDER BY 
TRANSACTION_CD