这是我所有表的结构和查询 （请关注 下面的 最后一个 查询）。正如您在小提琴中看到的那样，这是 当前输出：
+---------+-----------+-------+------------+--------------+ | user_id | user_name | score | reputation | top_two_tags | +---------+-----------+-------+------------+--------------+ | 1 | Jack | 0 | 18 | css,mysql | | 4 | James | 1 | 5 | html | | 2 | Peter | 0 | 0 | null | | 3 | Ali | 0 | 0 | null | +---------+-----------+-------+------------+--------------+
没错，一切都很好。
现在，我还有一个名为“类别”的存在。每个帖子只能有一个类别。而且我也想为每个用户获得前两个类别。而这里是我的新的查询。正如您在结果中看到的，发生了一些重复：
+---------+-----------+-------+------------+--------------+------------------------+ | user_id | user_name | score | reputation | top_two_tags | top_two_categories | +---------+-----------+-------+------------+--------------+------------------------+ | 1 | Jack | 0 | 18 | css,css | technology,technology | | 4 | James | 1 | 5 | html | political | | 2 | Peter | 0 | 0 | null | null | | 3 | Ali | 0 | 0 | null | null | +---------+-----------+-------+------------+--------------+------------------------+
看到？css,css，technology, technology。为什么这些是重复的？我只是增加了一个LEFT JOIN对categories，一模一样tags。但是它不能按预期工作，甚至会影响标签。
css,css
technology, technology
LEFT JOIN
categories
tags
无论如何，这是 预期的结果：
+---------+-----------+-------+------------+--------------+------------------------+ | user_id | user_name | score | reputation | top_two_tags | category | +---------+-----------+-------+------------+--------------+------------------------+ | 1 | Jack | 0 | 18 | css,mysql | technology,social | | 4 | James | 1 | 5 | html | political | | 2 | Peter | 0 | 0 | null | null | | 3 | Ali | 0 | 0 | null | null | +---------+-----------+-------+------------+--------------+------------------------+
有人知道我该怎么做到吗？
CREATE TABLE users(id integer PRIMARY KEY, user_name varchar(5)); CREATE TABLE tags(id integer NOT NULL PRIMARY KEY, tag varchar(5)); CREATE TABLE reputations( id integer PRIMARY KEY, post_id integer /* REFERENCES posts(id) */, user_id integer REFERENCES users(id), score integer, reputation integer, date_time integer); CREATE TABLE post_tag( post_id integer /* REFERENCES posts(id) */, tag_id integer REFERENCES tags(id), PRIMARY KEY (post_id, tag_id)); CREATE TABLE categories(id INTEGER NOT NULL PRIMARY KEY, category varchar(10) NOT NULL); CREATE TABLE post_category( post_id INTEGER NOT NULL /* REFERENCES posts(id) */, category_id INTEGER NOT NULL REFERENCES categories(id), PRIMARY KEY(post_id, category_id)) ; SELECT q1.user_id, q1.user_name, q1.score, q1.reputation, substring_index(group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags, substring_index(group_concat(q3.category ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2) AS category FROM (SELECT u.id AS user_Id, u.user_name, coalesce(sum(r.score), 0) as score, coalesce(sum(r.reputation), 0) as reputation FROM users u LEFT JOIN reputations r ON r.user_id = u.id AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY u.id, u.user_name ) AS q1 LEFT JOIN ( SELECT r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation FROM reputations r JOIN post_tag pt ON pt.post_id = r.post_id JOIN tags t ON t.id = pt.tag_id WHERE r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY user_id, t.tag ) AS q2 ON q2.user_id = q1.user_id LEFT JOIN ( SELECT r.user_id AS user_id, c.category, sum(r.reputation) AS category_reputation FROM reputations r JOIN post_category ct ON ct.post_id = r.post_id JOIN categories c ON c.id = ct.category_id WHERE r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY user_id, c.category ) AS q3 ON q3.user_id = q1.user_id GROUP BY q1.user_id, q1.user_name, q1.score, q1.reputation ORDER BY q1.reputation DESC, q1.score DESC ;
您的第二个查询的格式为：
q1 -- PK user_id LEFT JOIN (... GROUP BY user_id, t.tag ) AS q2 ON q2.user_id = q1.user_id LEFT JOIN (... GROUP BY user_id, c.category ) AS q3 ON q3.user_id = q1.user_id GROUP BY -- group_concats
内部GROUP BY导致(user_id, t.tag)＆(user_id, c.category)为键/唯一。除此之外，我不会处理那些GROUP BY。
(user_id, t.tag)
(user_id, c.category)
TL; DR 当您将（q1 JOIN q2）联接到q3时，它不在其中一个的键/唯一性上，因此对于每个user_id，您将为标记和类别的每种可能组合得到一行。因此，最终的GROUP BY输入每个（user_id，tag）和每个（user_id，category）重复，而GROUP_CONCATs则每个user_id输入重复的标签和类别。正确的将是（q1 JOIN q2 GROUP BY）JOIN（q1 JOIN q3 GROUP BY），其中所有联接都位于公共键/唯一(user_id)且没有虚假聚合。尽管有时您可以撤消此类虚假聚合。
(user_id)
正确的对称INNER JOIN方法：LEFT JOIN q1＆q2–1：很多-然后是GROUP BY＆GROUP_CONCAT（这是您的第一个查询所做的事情）；然后分别以类似的方式分别左移q1和q3–1：很多-然后使用GROUP BY＆GROUP_CONCAT; 然后将两个结果INNER JOIN放在user_id–1：1上。
正确的对称标量子查询方法：从q1中选择GROUP_CONCAT，作为每个带有GROUP BY的标量子查询。
正确的累积LEFT JOIN方法：LEFT JOIN q1＆q2–1：很多-然后使用GROUP BY＆GROUP_CONCAT; 然后左键加入＆q3– 1：很多-然后是GROUP BY＆GROUP_CONCAT。
一种正确的方法，如您的第二次查询：您首先左联接q1和q2–1：很多。然后您就可以加入＆q3– many：1：many。它为出现在user_id中的标记和类别的每种可能组合提供一行。然后，在GROUP BY之后，GROUP_CONCAT –重复的（user_id，标签）对和重复的（user_id，类别）对。这就是为什么您有重复的列表元素的原因。但是将DISTINCT添加到GROUP_CONCAT可以得到正确的结果。（根据wchiquito的评论。）
通常情况下，您更希望根据实际数据/使用情况/统计信息通过查询计划和时间安排来进行工程设计权衡。输入和统计信息（预期的重复量），实际查询的时间等。一个问题是many：1：many JOIN方法的额外行是否抵消了GROUP BY的保存。
-- cumulative LEFT JOIN approach SELECT q1.user_id, q1.user_name, q1.score, q1.reputation, top_two_tags, substring_index(group_concat(q3.category ORDER BY q3.category_reputation DESC SEPARATOR ','), ',', 2) AS category FROM -- your 1st query (less ORDER BY) AS q1 (SELECT q1.user_id, q1.user_name, q1.score, q1.reputation, substring_index(group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags FROM (SELECT u.id AS user_Id, u.user_name, coalesce(sum(r.score), 0) as score, coalesce(sum(r.reputation), 0) as reputation FROM users u LEFT JOIN reputations r ON r.user_id = u.id AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY u.id, u.user_name ) AS q1 LEFT JOIN ( SELECT r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation FROM reputations r JOIN post_tag pt ON pt.post_id = r.post_id JOIN tags t ON t.id = pt.tag_id WHERE r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY user_id, t.tag ) AS q2 ON q2.user_id = q1.user_id GROUP BY q1.user_id, q1.user_name, q1.score, q1.reputation ) AS q1 -- finish like your 2nd query LEFT JOIN ( SELECT r.user_id AS user_id, c.category, sum(r.reputation) AS category_reputation FROM reputations r JOIN post_category ct ON ct.post_id = r.post_id JOIN categories c ON c.id = ct.category_id WHERE r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY user_id, c.category ) AS q3 ON q3.user_id = q1.user_id GROUP BY q1.user_id, q1.user_name, q1.score, q1.reputation ORDER BY q1.reputation DESC, q1.score DESC ;
