mysql计数多次出现的多路复用条目 [英] mysql Count multiple occurrences of multiplexed entries
问题描述
我有一个如下所示的mysql表，
I have a mysql table as shown below,
id reasonCode
------ ------------------
1 0, 1
2 0
3 1, 2, 3
4 2
5 1, 0
我想输出为
reasonCode occurrences
0 3
1 3
2 2
3 1
我尝试了分组依据"，但是它给出了类似的内容.
I tried "group by" but it gives something like this.
reasonCode occurrences
0, 1 1
0 1
1, 2, 3 1
2 1
1, 0 1
如果有人对如何做有任何想法，将不胜感激.
if someone have any idea of how to do it, will be appreciated.
推荐答案
如果您有原因码表(我想是这样，那么您知道原因码1的含义是什么)，那么您可以这样做像这样的东西:-
If you have a table of reason codes (I presume you do, so you know what the meaning is of reason code 1 for example) then you could just do something like this:-
SELECT a.id, COUNT(b.id)
FROM reason_codes a
LEFT OUTER JOIN id_reason_code b
ON FIND_IN_SET(a.id, b.reasonCode)
GROUP BY a.id
但是，与此有关的一个问题是逗号后面有空格.逗号分隔的字段在最佳情况下是一个问题(最好拆分成另一个表的多行-如果需要，可以很容易地将它们串联在一起)，但是逗号后的空格会产生问题(请注意，删除这些空格还将使@Vignesh Kumar的解决方案更简单).
However one problem with this is that you have spaces after the commas. A comma separated field is a problem at the best of times (better split off into multiple rows of another table - easy enough to concatenate them together afterwards if needs be), but the spaces after the commas will give issues (note that removing these spaces would also make the solution by @Vignesh Kumar a bit simpler).
要解决这个问题，您可以执行以下操作:-
To get round this you could do:-
SELECT a.id, COUNT(b.id)
FROM reason_codes a
LEFT OUTER JOIN id_reason_code b
ON FIND_IN_SET(a.id, REPLACE(b.reasonCode, ' ', ''))
GROUP BY a.id
编辑-说明
这只是左外联接.这将占用第一个表中的每一行(即原因码)，并将其与第二个表中任何匹配的行相匹配(即ie_reason_code-不确定上面显示的表叫什么)；如果第二个表上没有匹配的行，则仍然返回第一个表中的行，但第二个表中的列为NULL.在这种情况下，联接是基于FIND_IN_SET完成的.这将在逗号分隔值列表中寻找第一个参数，并返回找到的位置(如果找到，则返回true).
It is just a LEFT OUTER JOIN. This will take every row from the first table (ie, reason codes), and match that against any row that matches on the 2nd table (ie, ie_reason_code - not sure what your table is called that you show above); if there are no matching rows on the 2nd table then the row from the first table is still brought back but with NULL in the columns from the 2nd table. In this case the join is done based on FIND_IN_SET. This looks for the first parameter in a list of comma separated values and returns the position if found (hence if found it evaluates to true).
COUNT/GROUP BY然后为每个a.id计数b.id的值数，并显示该计数.
The COUNT / GROUP BY then counts the number of values of b.id for each a.id and presents that count.
第二个查询执行相同的操作，但是它会在检查值之前从逗号分隔的列表中删除所有空格(当您有空格和逗号分隔值时需要).
The 2nd query is doing the same, but it is removing any spaces from the comma separated list before checking for values (required when you have a space as well as a comma separating the values).
如果您有以下表格:-
reason_codes table
id reason
0 Reason A
1 Reason B
2 Reason C
3 Reason D
4 Reason E
id_reason_code table
id reasonCode
1 0,1
2 0
3 1,2,3
4 2
5 1,0
然后显示以下sql(删除COUNT/GROUP BY):-
then the following sql (removing the COUNT / GROUP BY):-
SELECT a.id, b.id
FROM reason_codes a
LEFT OUTER JOIN id_reason_code b
ON FIND_IN_SET(a.id, b.reasonCode)
会给出以下内容:-
a.id b.id
0 1
0 2
0 5
1 1
1 3
1 5
2 3
2 4
3 3
4 NULL
运行:-
SELECT a.id, COUNT(b.id)
FROM reason_codes a
LEFT OUTER JOIN id_reason_code b
ON FIND_IN_SET(a.id, b.reasonCode)
GROUP BY a.id
COUNT/GROUP BY为a.id的每个值提供一行，然后为a.id的该值计算b.id的值(非空):-
the COUNT / GROUP BY is giving one row for each value of a.id, and then a count of the values (non null) of b.id for that value of a.id:-
a.id count(b.id)
0 3
1 3
2 2
3 1
4 0
如果您愿意，也可以带回实际原因而不是代码:-
You could also bring back the actual reason instead of the code if you wanted:-
SELECT a.id, a.reason, COUNT(b.id)
FROM reason_codes a
LEFT OUTER JOIN id_reason_code b
ON FIND_IN_SET(a.id, b.reasonCode)
GROUP BY a.id, a.reason
给予:-
a.id a.reason count(b.id)
0 Reason A 3
1 Reason B 3
2 Reason C 2
3 Reason D 1
4 Reason E 0
这篇关于mysql计数多次出现的多路复用条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
