基本上,当某个值(在我的例子中是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发布于 2011-01-27 06:36:50
SELECT COMMUNICATIONS_ID, SUM(fieldName)
FROM consumer_action_log
WHERE COMM_TYPE_ID = 4
GROUP BY COMMUNICATIONS_ID我不认为有必要在这里将表本身连接起来。
发布于 2011-01-27 06:32:16
拆分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=4COMM_TYPE_ID=4中的某列
如果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_IDhttps://stackoverflow.com/questions/4810807
复制相似问题