mysql 计算一个字段链接到另一个字段的次数 [英] mysql count how many times one field links to another

问题描述

如果我有下表:

id  |  ref
1       b
4       c
2       a
3       b
1       b
3       b

我如何计算有多少 unique id 实例将 b 作为它们的引用?我现在的查询是:

how do I count how many unique instances of id have b as their reference? The query I have now is:

select id, ref, count(ref) from table group by ref;

但是这显示了每个 ref 在表中存在的次数.我正在寻找的是:

but this shows me the number of times each ref exists in the table. What I'm looking for this is:

id  |  ref  |  count
1       b        2  /*id 1, id 3*/
4       c        1  /*id 4 */
2       a        1  /*id 2 */

编辑

我实际上仍然需要查看所有行 - 而不是 group by 它们的摘要，所以我需要它看起来像这样:

Edit

I actually need to see all the lines still - not a group by summary of them, so I need it to look like this:

id  |  ref  |  count
1       b        2 /* id 1, id 3 */
4       c        1 /* id 4 */
2       a        1 /* id 2 */
3       b        2 /* id 1, id 3 */
1       b        2 /* id 1, id 3 */
3       b        2 /* id 1, id 3 */

推荐答案

select ref, count(distinct id) from table group by ref;

这将通过 ref 为您提供不同 id 的计数.

This will give you a count of distinct id by ref.

select ref, count(*) from table group by ref;

这将为您提供参考记录数.

This will give you number of records by ref.

试试这个以获得你想要的输出.

Try this to get the output you desire.

select t.*, m.counter
from table t 
join (
   select ref, count(distinct id) as counter
   from table group by ref
) m on t.ref = m.ref

SQLFiddle 示例:http://sqlfiddle.com/#!9/2b93c/2

Example on SQLFiddle: http://sqlfiddle.com/#!9/2b93c/2

这篇关于mysql 计算一个字段链接到另一个字段的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！