带有 SUM() 的 MySQL 查询未返回预期结果 [英] MySQL query with SUM() is not returning the expected results
问题描述
查询:
SELECT
id_data,
id_tag IN (75) AS tag1,
id_tag IN (12) AS tag2,
SUM(id_tag IN (75, 12)) summedTags
FROM
tags_inservice
WHERE id_service = 1
GROUP BY id_data
ORDER BY summedTags DESC
结果:
id_data tag1 tag2 summedTags
------- ------ ------ ----------
3109 0 1 2
1956 0 0 2
1928 0 0 1
2738 1 0 1
我认为不对的地方:
summedTags
很少与标签的实际总和相匹配!在示例中,id_data
3109 有 0 + 1 = 2,这是错误的,而 id_data
1956 显示 0 + 0 = 2,这也是错误的,等等.我做错了什么?
The summedTags
rarely matches the actual sum of the tags! In the example id_data
3109 has 0 + 1 = 2 which is wrong, and id_data
1956 shows 0 + 0 = 2 which is wrong too, etc, etc.
What am I doing wrong?
顺便说一句,我也尝试添加 HAVING SUM(id_tag IN (75, 12)) = 2
,结果相似.
By the way, I also tried adding HAVING SUM(id_tag IN (75, 12)) = 2
with similar results.
如果您需要更多额外信息:
我有一个表 tags_inservice 包含一堆数据(由 id_data 表示,来自另一个表的 id)并且数据可以用不同的标签进行标记.一个 id_data 可以有零个或多个 id_tags,因此可能会有不止一行具有相同的 id_data 值.事实上,有时用相同的 id_tag 多次标记相同的 id_data 是有效的.
I have a table tags_inservice that contains a bunch of data (represented by id_data, an id from another table) and the data can be tagged by different tags. An id_data can have zero or more id_tags so there could be more than one row with the same id_data value. In fact, sometimes it's valid to have the same id_data tagged with the same id_tag more than once.
DESCRIBE tags_inservice;
Field Type Null Key Default Extra
---------- ------- ------ ------ ------- --------------
id_intag int(11) NO PRI (NULL) auto_increment
id_tag int(11) YES MUL (NULL)
id_service int(11) YES MUL (NULL)
data_type int(11) YES (NULL)
id_data int(11) YES MUL (NULL)
推荐答案
似乎问题是由 group by 引起的,因为 summedTags
是 id_data
seems the problem is caused by group by as the summedTags
is the occurrences of id_data
SELECT
id_data,
sum(if (id_tag=75,1,0)) as tag1,
sum(if (id_tag=12,1,0)) as tag2,
sum(if (id_tag in(12,75),1,0)) as summedTags
FROM tags_inservice
WHERE id_service = 1
GROUP BY id_data
ORDER BY summedTags DESC;
这篇关于带有 SUM() 的 MySQL 查询未返回预期结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!