postgresql - 根据列中的值将行分为两种类型

Question

我有一张 table :

------------------------------------------
Uid | mount | category 
-----------------------------------------
1   | 10    |    a
1   |  3    |    b
3   |  7    |    a
4   |  1    |    b
4   | 12    |    a
4   |  5    |    b
1   |  2    |    c
2   |  5    |    d

我想要一个这样的结果:

------------------------------------------
Uid | suma | sumnota
-----------------------------------------
 1  |  10  |    5    
 2  |   0  |    5
 3  |   7  |    0
 4  |  12  |    6

按uid分组；
Suma 是 sum(mount) where catagory = 'a';
Sumnota 是 sum(mount) where catagory <> 'a';

有什么办法吗？

Answer 1

将条件聚合与 CASE 结合使用SUM() 中的陈述功能:

SELECT
    uid
  , SUM(CASE WHEN category = 'a' THEN mount ELSE 0 END) AS suma
  , SUM(CASE WHEN category IS DISTINCT FROM 'a' THEN mount ELSE 0 END) AS sumnota
FROM
  yourtable
GROUP BY uid
ORDER BY uid

我正在使用 IS DISTINCT FROM 正确处理 NULL 的条款类别列中的值。如果这不是你的情况，你可以简单地使用 <>运营商。

来自 documentation (大胆强调我的):

Ordinary comparison operators yield null (signifying "unknown"), nottrue or false, when either input is null.

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true.