mysql – 来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7598字,纯文字阅读大概需要11分钟。
内容图文
![mysql – 来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为](/upload/InfoBanner/zyjiaocheng/891/d367ebd633834b03927cc187a7071f3d.jpg)
Here是我所有表格的结构和查询(请关注最后一个查询,如下所示).正如你在小提琴中看到的,这是当前的输出:
+---------+-----------+-------+------------+--------------+
| 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 |
+---------+-----------+-------+------------+--------------+
这是正确的,一切都很好.
现在我还有一个名为“category”的存在.每个帖子只能有一个类别.而且我还希望为每个用户获得前两个类别.而here是我的新查询.正如您在结果中看到的,发生了一些重复:
+---------+-----------+-------+------------+--------------+------------------------+
| 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,技术,技术.为什么这些是重复的?我刚刚为类别添加了一个LEFT JOIN,就像标签一样.但它不能按预期工作,甚至也会对标签产生影响.
无论如何,这是预期的结果:
+---------+-----------+-------+------------+--------------+------------------------+
| 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)是键/ UNIQUE.除此之外,我不会解决那些GROUP BY.
TL; DR当您加入(q1 JOIN q2)到q3时,它不在其中一个的键/ UNIQUE上,因此对于每个user_id,您为每个可能的标签和组合组合获得一行.类别.所以最终的GROUP BY输入重复(user_id,tag)& per(user_id,category)和不恰当的GROUP_CONCATs重复标签&每个user_id的类别.正确的是(q1 JOIN q2 GROUP BY)JOIN(q1 JOIN q3 GROUP BY),其中所有连接都在公共密钥/ UNIQUE(user_id)&没有虚假的聚合.虽然有时你可以撤消这种虚假聚合.
正确的对称INNER JOIN方法:LEFT JOIN q1& q2–1:很多 – 然后GROUP BY& GROUP_CONCAT(这是你的第一个查询所做的);然后分别类似LEFT JOIN q1& q3–1:很多 – 然后GROUP BY& GROUP_CONCAT;然后INNER JOIN两个结果ON user_id – 1:1.
正确的对称标量子查询方法:使用GROUP BY将q1中的GROUP_CONCATs选为scalar subqueries.
正确的累积LEFT JOIN方法:LEFT JOIN q1& q2–1:很多 – 然后GROUP BY& GROUP_CONCAT;然后LEFT JOIN& q3–1:很多 – 然后GROUP BY& GROUP_CONCAT.
像你的第二个查询一样正确的方法:你首先LEFT JOIN q1& q2–1:很多.然后你LEFT JOIN& Q3 – 很多:1:很多.它为标签和放大器的每种可能组合提供一排.与user_id一起显示的类别.然后在GROUP BY之后GROUP_CONCAT – 重复(user_id,tag)对和重复(user_id,category)对.这就是为什么你有重复的列表元素.但是将DISTINCT添加到GROUP_CONCAT会得到正确的结果. (根据wchiquito的评论.)
您更喜欢的是通常的工程权衡,以便通过查询计划&时间,每个实际数据/使用/统计.输入和输入预期复制量的统计数据),实际查询的时间等.一个问题是多个额外的行:1:多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 ;
内容总结
以上是互联网集市为您收集整理的mysql – 来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为全部内容,希望文章能够帮你解决mysql – 来自GROUP_BY的两个LEFT JOIN的GROUP_CONCAT的奇怪重复行为所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。