gpt4 book ai didi

mysql - 让 SQL 计算整个列的总和 - 我相信在分组时遇到问题

转载 作者:行者123 更新时间:2023-11-29 03:05:53 24 4
gpt4 key购买 nike

所附图片显示了结果集,我将在下面留下我的代码 - 为什么我无法计算每个单元格中整个 viewTotal 列的总和?我希望 viewTotal 列下方的每个单元格都显示为 4

我认为这是某种分组问题,尽管我在网络上找不到任何关于哪些列需要分组的具体信息。 重要的是保留所有三行 - 我不想只返回一行。也许这个标准使我试图做的事情变得更加困难?

谢谢,埃文

Result Preview

Select topic_id, topic_subject, SUM(topicViews) as viewTotal, replyCount From
(
Select
T.topic_id, T.topic_subject, Count(distinct Tvt.id) as topicViews, Count(Distinct R.reply_id) as replyCount, R.reply_id, R.reply_topic
From topic T
LEFT JOIN topic_view_tracker Tvt ON
T.topic_id = Tvt.topic_id
LEFT Join reply R ON
T.topic_id = R.reply_topic
Where
T.topic_by = 10
Group By T.topic_id) B
Group By topic_id
Order by replyCount DESC

示例记录:

主题

╔══════════╦════════════════════════════╦══════════╗
║ TOPIC_ID ║ TOPIC_SUBJECT ║ TOPIC_BY ║
╠══════════╬════════════════════════════╬══════════╣
║ 25 ║ School police in the night ║ 10 ║
║ 29 ║ The first topic, enjoy it ║ 10 ║
║ 30 ║ This is a normal title... ║ 10 ║
╚══════════╩════════════════════════════╩══════════╝

TOPIC_VIEW_TRACKER

╔════╦════════════╦══════════╗
║ ID ║ USER_IP ║ TOPIC_ID ║
╠════╬════════════╬══════════╣
║ 1 ║ xx.xx.xx.x ║ 25 ║
║ 2 ║ xx.xx.xx.x ║ 25 ║
║ 3 ║ xx.xxx.xxx ║ 29 ║
║ 4 ║ xxx.xx.xx ║ 30 ║
╚════╩════════════╩══════════╝

回复

╔══════════╦═════════════╗
║ REPLY_ID ║ REPLY_TOPIC ║
╠══════════╬═════════════╣
║ 1 ║ 25 ║
║ 2 ║ 29 ║
╚══════════╩═════════════╝

Expected output (example):

topic_id       topic subject                          view total   reply count29         The first topic, enjoy it                    4            525          school police in the night                  4            430          this is a normal title for a topic ...      4            0

最佳答案

SELECT  x.*, 
COALESCE(y.viewTotal, 0) viewTotal,
COALESCE(z.replyCount, 0) replyCount
FROM topic x
LEFT JOIN
(
SELECT a.topic_by, COUNT(b.topic_ID) viewTotal
FROM topic a
LEFT JOIN topic_view_tracker b
ON a.topic_ID = b.topic_ID
GROUP BY a.topic_by
) y ON x.topic_by = y.topic_by
LEFT JOIN
(
SELECT reply_topic, COUNT(*) replyCount
FROM reply
GROUP BY reply_topic
) z ON x.topic_ID = z.reply_topic
WHERE x.topic_by = 10

输出(基于提供的记录)

╔══════════╦════════════════════════════╦══════════╦═══════════╦════════════╗
║ TOPIC_ID ║ TOPIC_SUBJECT ║ TOPIC_BY ║ VIEWTOTAL ║ REPLYCOUNT ║
╠══════════╬════════════════════════════╬══════════╬═══════════╬════════════╣
║ 25 ║ School police in the night ║ 10 ║ 4 ║ 1 ║
║ 29 ║ The first topic, enjoy it ║ 10 ║ 4 ║ 1 ║
║ 30 ║ This is a normal title... ║ 10 ║ 4 ║ 0 ║
╚══════════╩════════════════════════════╩══════════╩═══════════╩════════════╝

关于mysql - 让 SQL 计算整个列的总和 - 我相信在分组时遇到问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15399501/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com