mysql - sql计算两个值从另一列共享相同值的次数

这是我的桌子：

ID  State
---------
0   A
1   A
2   C
3   C
2   A
3   A
2   D
0   D
2   E
3   F

结果是：

ID1 ID2 N_State
---------
0   1   1
2   3   2
2   0   1

问题是：
要计算两个id共有的状态数，并按上述格式输出吗？

最佳答案

select      t1.id,t2.id,count(*) as N_State

from                mytable t1
            join    mytable t2
            on      t2.State = t1.State
                and t2.id > t1.id

group by    t1.id,t2.id

+----+----+----------+
| id | id | count(*) |
+----+----+----------+
| 0  | 1  | 1        |
+----+----+----------+
| 0  | 2  | 2        |
+----+----+----------+
| 0  | 3  | 1        |
+----+----+----------+
| 1  | 2  | 1        |
+----+----+----------+
| 1  | 3  | 1        |
+----+----+----------+
| 2  | 3  | 2        |
+----+----+----------+

添加行号

select      @i:=@i+1 as i
           ,id1,id2,N_State

from        (select      t1.id as id1,t2.id as id2,count(*) as N_State

             from                mytable t1

                         join    mytable t2

                         on      t2.State = t1.State
                             and t2.id > t1.id

             group by    t1.id,t2.id
             ) t

             cross join (select @i:=-1) x

order by    id1,id2

关于mysql - sql计算两个值从另一列共享相同值的次数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41220065/