SQL多重计数和联接

杰奎琳·帕索斯（Jaqueline Passos）

我正在尝试做这样的事情：

User_guid | Post Message | Discussion board | Total |

1 | 300 | 25 | 325 |

2 | 15 | 185 | 200 |

3 | 100 | 203 | 303 |

但我不知道怎么做

我有此查询的发布消息结果：

SELECT COUNT( * ) as 'Quantidade' , users_entity.name , users_entity.username
FROM river 
JOIN users_entity 
ON river.subject_guid = users_entity.guid 
AND river.action_type = 'create'
AND river.view = 'river/object/thewire/create'
GROUP BY river.subject_guid ORDER BY 'Quantidade' DESC

并且我有此查询的讨论结果：

SELECT COUNT( * ) as 'Quantidade' , users_entity.name , users_entity.username
FROM river 
JOIN users_entity 
ON river.subject_guid = users_entity.guid 
AND river.action_type = 'reply'
GROUP BY river.subject_guid ORDER BY 'Quantidade' DESC

但是我不知道如何将这些结果作为users_entity表的行加入。交互总数应为帖子消息和讨论区值的总和。有人可以帮我吗？谢谢。

埃德·吉布斯

没有表结构和样本数据很难说（这就是为什么要投反对票），但是您可能需要这样的东西：

SELECT
  users_entity.name,
  users_entity.username,
  COUNT(CASE WHEN river.action_type = 'create' AND river.view = 'river/object/thewire/create' THEN 1 END) AS Post_Message,
  COUNT(CASE WHEN river.action_type = 'reply' THEN 1 END) AS Discussion_Board
FROM river
JOIN users_entity ON users_entity.guid = river.subject_guid
GROUP BY
  users_entity.name,
  users_entity.username
ORDER BY ???

我不确定ORDER BY合并查询要使用哪个值。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。