mysql - 获取项目计数，同时从同一个表中进行选择，然后按计数列排序

Question

例如，我有一个看起来像这样的表格

ID  NAME            YREF    UREF
1   ADAM BLAISE     0001    0007
2   JAMES HARDY     0002    0005
3   PHILIP HENRY    0003    0002
4   PETER SCHWARTZ  0004    0001
5   WILL MADISON    0005    0002
6   JEREMY PINOT    0006    0002
7   JOHN WILLIARD   0007    0007
8   MARK WILLIARD   0008    0005
9   JOHN VAUGHN     0009    0002
10  DAVID JAMES     0010    0002

我希望能够计算出每行每个用户将其 YREF 发送给另一个用户的次数，以便该用户在提交自己的姓名时引用它，从而形成一个表看起来像这样可以得到:

ID  NAME            YREF    COUNT (i.e. how many times their YREF appeared in the UREF column of other users)
1   ADAM BLAISE     0001    1
2   JAMES HARDY     0002    5
3   PHILIP HENRY    0003    0
4   PETER SCHWARTZ  0004    0
5   WILL MADISON    0005    2
6   JEREMY PINOT    0006    0
7   JOHN WILLIARD   0007    2
8   MARK WILLIARD   0008    0
9   JOHN VAUGHN     0009    0
10  DAVID JAMES     0010    0

Answer 1

select t1.id, t1.name, t1.yref, coalesce(t2.cnt, 0) as count
from your_table t1
left join 
(
  select uref, count(*) as cnt
  from your_table
  group by uref
) t2 on t1.yref = t2.uref