计数重复值-帮助我 mysql 查询是死慢

标签: sql MySQL
发布时间: 2015/6/11 21:25:18
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

让说,我有这样一个表:

Id*|value

每个 Id 我想去数多少次相应的 value 存在。喜欢此示例数据:

1, a
2, b
3, a 
4, b
5, c
6, a

我想:

1, a, 3
2, b, 2
3, a, 3
4, b, 2
5, c, 1
6, a, 3

这是现在所拥有,我相信它工作,但它是死慢:

SELECT t1.Id, t1.value, COUNT(t2.value) FROM `table` AS t1
LEFT JOIN `table` AS t2 ON (t1.value = t2.value)
GROUP by t1.Id

我的表包含数以千计的追捕行。如何提高此性能副任何建议吗?

解决方法 1:

试试这个

SELECT t1.Id, t1.value, t2.cnt FROM Table1 AS t1
INNER JOIN 
(
  SELECT value, COUNT(value) as cnt 
    FROM Table1 GROUP BY value
) AS t2 ON (t1.value = t2.value)
ORDER BY t1.Id

SELECT t1.id,t1.value,COUNT(t2.id) AS cnt FROM Table1 AS t1
INNER JOIN Table1 AS t2
   ON  t1.value = t2.value
GROUP BY t1.id,t1.value
ORDER BY t1.id
官方微信
官方QQ群
31647020