我有下表:
id | name | action | count
------------------------------
1 | Value1 | 0 | 27
1 | Value1 | 1 | 49
2 | Value2 | 0 | 7
2 | Value2 | 1 | 129
3 | Value3 | 0 | 9
3 | Value3 | 1 | 7我需要使'action‘列出现两次,其中每一行的计数值如下所示:
id | name | action1 | action2
---------------------------------
1 | Value1 | 27 | 49
2 | Value2 | 7 | 129
3 | Value3 | 9 | 7我该怎么做?这是我的剧本:
SELECT m.id,
t.name,
m.action,
count(m.id) AS count
FROM table1 m
LEFT JOIN table2 t ON (m.id = t.id)
WHERE m.status != '2'
GROUP BY m.id,
t.name,
m.action
ORDER BY 1, 3发布于 2014-07-15 13:10:26
您可以使用条件聚合:
select m.id,
t.name,
count(case when m.action = 0 then m.id end) as action1,
count(case when m.action = 1 then m.id end) as action2
from table1 m
left table2 t ON (m.id = t.id)
where m.status <> '2'
group by m.id, t.name;在即将发布的9.4版中,count(case(...))可以使用新的“过滤”聚合( count(id) filter (when m.action = 0) )编写得更短一些
https://dba.stackexchange.com/questions/71556
复制相似问题