Geek asks and answers

The number of unique elements in the entire database of sets

I have the following user database, where everyone can speak different languages ​​at a different level.

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

I would like to create a MySQL query that takes into account the appearance of each language, regardless of its level. The desired tab should look like this:

lang    count
EN      4
DE      2
RU      2

I have already tried different combinations of this, but it is far from ideal.

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
+3
source share
2 answers

If the maximum limit on the number of languages ​​in a set, you can pull out all the first elements, elements, third elements, etc. and combine them together. Here is an example that pulls any first or second element from a set of languages ​​and combines them:

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

Demo: 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

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

, , .

0

More articles:


All Articles