集合数据库中唯一元素的计数

Question

我有下面的用户数据库，每个用户都可以在不同的级别上讲不同的语言。

id      langs
12      EN-21
36      EN-2,RU-3
41      EN-9
57      DE-35,EN-28
60      DE-9,RU-14

我想要创建一个MySQL查询来统计每种语言的出现情况，而不管的级别如何。所需的选项卡应该如下所示：

lang    count
EN      4
DE      2
RU      2

我已经尝试过不同的组合，但这还远远不够完美。

SELECT 
    DISTINCT SUBSTRING_INDEX(langs, '-', 1) AS lang, 
--  COUNT(langs) as count
--  SUM(
--      (SELECT DISTINCT SUBSTRING_INDEX(langs, '-', 1) 
--      FROM people
--      WHERE langs != '')
--  )
FROM people
WHERE langs != ''
--  GROUP BY lang
ORDER BY lang

Answer 1

如果集合中的语言数量存在最大限制，则可以提取所有的第一元素、第二元素、第三元素等，并将它们结合在一起。下面是一个从语言集中提取任何第一个或第二个元素并将它们组合起来的示例：

select distinct substring_index(langs, '-', 1) as lang
from people where langs != ''
union
select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(langs, '-', 2), ',', -1)
from people where LENGTH(langs) - LENGTH(REPLACE(langs,',','')) + 1 > 1

演示：http://www.sqlfiddle.com/#!2/b86f2/1

从这里开始，就需要将语言列表与人员列表结合起来，并通过比较people.langs like '%EN%' (例如：

select
  lang,
  count(case when people.langs like concat('%',langs.lang,'%') then 1 end) as count
from people,
  (
    select distinct substring_index(langs, '-', 1) as lang
    from people where langs != ''
    union
    select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(langs, '-', 2), ',', -1)
    from people where LENGTH(langs) - LENGTH(REPLACE(langs,',','')) + 1 > 1
  ) langs
group by langs.lang
order by langs.lang

样本输出：

LANG    COUNT
====    ====
DE      2
EN      4
RU      2

演示：http://www.sqlfiddle.com/#!2/b86f2/5

Answer 2

SELECT SUBSTRING_INDEX(langs, '-', 1) AS lang, count(1) as count_lang
FROM people
WHERE langs!=''
GROUP BY lang
ORDER BY lang

请试试这个，让我知道你得到了什么。