我有一个带有2列X，Y的表。我想计算两列中每条记录的出现次数。例如：
X | Y
A | A
B | A
B | C
D | C
结果应该是
variable | X_count | Y_count
A | 1 | 2
B | 2 | 0
C | 0 | 2
D | 1 | 0
我如何在mysql中实现呢？
编辑：好的，花了几个小时后，我确保Y中的所有值都是X的子集。即，对于任何变量，X_count永远不会为0。这有助于优化吗？
我认为您需要一个工会临时表和分组依据
select v.variable, a.count(*), b.count(*)
from
(select x as variable
from my_table
union select y
from my_table ) as v
inner join my_table as a on a.X = v.variable
inner join my_table as b on b.y = v.variable
group by v.variable
Y是X的子集的事实可以避免合并，但无论如何我们都需要独特的X
select v.variable, a.count(*), b.count(*)
from
(select distinct x as variable
from my_table ) as v
inner join my_table as a on a.X = v.variable
inner join my_table as b on b.y = v.variable
group by v.variable
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句