mysql - SQL - select concat 与表连接中的重复值

Question

场景

我想获取用户拥有的注册语言以及所有用户详细信息，我当前的问题是当我GROUP_CONCAT目标列时，值会重复。有人可以解释一下为什么这些值是重复的吗？

我的表格

// users
+------+-------------+------------+----------------+--------------+
|  id  |  firstname  |  lastname  |      email     |  created_at  |
+------+-------------+------------+----------------+--------------+
|  10  |    John     |     Doe    |  john@doe.com  |  2014-10-21  |
+------+-------------+------------+----------------+--------------+

// teachers
+------+-----------+------------------+-------------------+
|  id  |  user_id  |  years_teaching  |  months_teaching  |
+------+-----------+------------------+-------------------+
|  35  |     10    |        3         |         6         |
+------+-----------+------------------+-------------------+

// teacher_languages
+------+--------------+---------------+----------+---------+
|  id  |  teacher_id  |  language_id  |  status  |  views  |
+------+--------------+---------------+----------+---------+
|  52  |      35      |       5       |     1    |    80   |
+------+--------------+---------------+----------+---------+
|  53  |      35      |       7       |     1    |    40   |
+------+--------------+---------------+----------+---------+

// translators
+------+-----------+------------------------+---------------------+
|  id  |  user_id  |  certified_translator  |  accept_travelling  |
+------+-----------+------------------------+---------------------+
|  23  |     10    |            0           |          1          |
+------+-----------+------------------------+---------------------+

// translator_languages
+------+-----------------+---------------+----------+---------+
|  id  |  translator_id  |  language_id  |  status  |  views  |
+------+-----------------+---------------+----------+---------+
|  52  |       23        |       5       |     1    |    27   |
+------+-----------------+---------------+----------+---------+
|  53  |       23        |       7       |     1    |    82   |
+------+-----------------+---------------+----------+---------+

// languages
+------+-----------------+------------+
|  id  |  language_text  |  language  |
+------+-----------------+------------+
|   5  |     English     |    EN      |
+------+-----------------+------------+
|   7  |      French     |    FR      |
+------+-----------------+------------+

当前查询

这是产生重复值的查询

SELECT 
  u.*,
  // teacher languages
  GROUP_CONCAT(l.language_text) AS teacher_languages,
  GROUP_CONCAT(tl.status) AS teacher_languages_status,
  GROUP_CONCAT(tl.views) AS teacher_language_views
  // translator languages
  GROUP_CONCAT(trl_txt.language_text) AS translator_languages,
  GROUP_CONCAT(trl.status) AS translator_languages_status,
  GROUP_CONCAT(trl.views) AS translator_language_views
FROM
  users u
LEFT JOIN 
  teachers t ON t.user_id = u.id
LEFT JOIN 
  teacher_languages tl ON tl.teacher_id = t.id
LEFT JOIN 
  languages l ON l.id = tl.language_id
LEFT JOIN 
  translators tr ON tr.user_id = u.id
LEFT JOIN 
  translator_languages trl ON trl.teacher_id = t.id
LEFT JOIN 
  languages trl_txt ON trl_txt.id = trl.language_id
GROUP BY
  u.id
ORDER BY
  u.created_at

结果

[0] =>
  [id]                         => 10
  [firstname]                  => 'John'
  [lastname]                   => 'Doe'
  [email]                      => 'john@doe.com'
  [created_at]                 => '2014-10-21'
  [teacher_languages]          => 'English, English, French, French'
  [teacher_language_status]    => '1,1,1,1'
  [teacher_language_views]     => '80,40,80,40'
  [translator_languages]       => 'English, French, English, French'
  [translator_language_status] => '1,1,1,1'
  [translator_language_views]  => '27,82,27,82'

Answer 1

问: 有人可以解释一下为什么这些值是重复的。

答:为了更好地理解值重复的原因，请运行不带 GROUP_CONCAT 和 GROUP BY 的查询。然后，您的查询会产生某种笛卡尔积:因为结果中每个老师和每个译者都有两种语言，所以您会得到所有可能的组合:

teacher_languages  translator_languages-----------------  --------------------English            EnglishEnglish            FrenchFrench             EnglishFrench             French

GROUP_CONCAT just concatenates all column values and you get:

[teacher_languages]          => 'English, English, French, French'
[translator_languages]       => 'English, French, English, French'

<小时/>

避免重复的可能解决方案之一是使用子查询:

SELECT 
  u.*,
  a.teacher_languages, a.teacher_languages_status, a.teacher_language_views,
  b.translator_languages, b.translator_languages_status, b.translator_language_views
FROM users u
LEFT JOIN (
  SELECT 
    t.user_id,
    -- teacher languages
    GROUP_CONCAT(tl_txt.language_text) AS teacher_languages,
    GROUP_CONCAT(tl.status) AS teacher_languages_status,
    GROUP_CONCAT(tl.views) AS teacher_language_views
  FROM teachers t
  LEFT JOIN teacher_languages tl ON tl.teacher_id = t.id
  LEFT JOIN languages tl_txt ON tl_txt.id = tl.language_id
  GROUP BY t.user_id
) a ON a.user_id = u.id
LEFT JOIN (
  SELECT 
    tr.user_id,
    -- translator languages
    GROUP_CONCAT(trl_txt.language_text) AS translator_languages,
    GROUP_CONCAT(trl.status) AS translator_languages_status,
    GROUP_CONCAT(trl.views) AS translator_language_views
  FROM translators tr
  LEFT JOIN translator_languages trl ON trl.teacher_id = tr.id
  LEFT JOIN languages trl_txt ON trl_txt.id = trl.language_id
  GROUP BY tr.user_id
) b ON b.user_id = u.id
ORDER BY u.created_at