总结MySQL计算字段，其中列值相同

发布于 2024-10-14 14:55:27 字数 315 浏览 5 评论 0原文

基本上，当某个值（在我的例子中为 COMMUNICATIONS_ID）相等时，我试图对计算字段的值求和。这些分数与相同的 COMMUNICATIONS_ID 相关联，我想对这些值进行求和。

我是 SQL 新手，这是我被误导的第一次尝试：

SELECT *
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
    AND cal1.COMM_TYPE_ID=4

原文

Basically, I am trying to sum up the value of a calculated field when a certain value (in my case COMMUNICATIONS_ID) are equal. These scores are associated with the same COMMUNICATIONS_ID and I want to sum up these values.

I am new to SQL and this is my misguided first attempt:

SELECT *
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
    AND cal1.COMM_TYPE_ID=4

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好多鱼好多余 2024-10-21 14:55:27

SELECT COMMUNICATIONS_ID, SUM(fieldName) 
FROM consumer_action_log
WHERE COMM_TYPE_ID = 4
GROUP BY COMMUNICATIONS_ID

我认为没有必要在这里将表连接到自身。

SELECT COMMUNICATIONS_ID, SUM(fieldName) 
FROM consumer_action_log
WHERE COMM_TYPE_ID = 4
GROUP BY COMMUNICATIONS_ID

I don't see a need to JOIN the table to itself here.

回复收藏 0 原文

已下线请稍等 2024-10-21 14:55:27

即使 INNER JOIN 的结果相同，最好还是分开 ON 和 WHERE 条件。使两个表之间的联系更加清晰。

SELECT sum(cal2.somecolumn)
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
WHERE cal1.COMM_TYPE_ID=4

查找 cal1 记录，其中 COMM_TYPE_ID=4
连接到 cal2（自连接），其中 COMMUNICATIONS_ID 等于 cal1
对 cal2 中的某些列求和

如果 COMMS_TYPE_ID=4 上的过滤器结果为多个 cal1.COMMUNICATIONS_ID，那么您将需要 GROUP BY COMMUNICATIONS_ID（与 cal1 或 cal2 无关 - 它们是相同的）

SELECT cal2.COMMUNICATIONS_ID, sum(cal2.somecolumn)
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
WHERE cal1.COMM_TYPE_ID=4
GROUP BY cal2.COMMUNICATIONS_ID

It may be better to split the ON and WHERE conditions, even if the result is the same for INNER JOINs. Makes it clearer what links the two tables.

SELECT sum(cal2.somecolumn)
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
WHERE cal1.COMM_TYPE_ID=4

Find cal1 records where COMM_TYPE_ID=4
Join to cal2 (self join) where the COMMUNICATIONS_ID is equal to cal1
Sum up some column from cal2

If the filter on COMMS_TYPE_ID=4 results in multiple cal1.COMMUNICATIONS_IDs, then you will want to GROUP BY COMMUNICATIONS_ID (doesn't matter from cal1 or cal2 - they are the same)

SELECT cal2.COMMUNICATIONS_ID, sum(cal2.somecolumn)
FROM consumer_action_log as cal1
JOIN consumer_action_log as cal2
    ON cal1.COMMUNICATIONS_ID=cal2.COMMUNICATIONS_ID
WHERE cal1.COMM_TYPE_ID=4
GROUP BY cal2.COMMUNICATIONS_ID

回复收藏 0 原文

~没有更多了~