这是我所有表的结构和查询
(请关注 下面的 最后一个 查询)。正如您在小提琴中看到的那样,这是 当前输出:
+---------+-----------+-------+------------+--------------+
| 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
。但是它不能按预期工作,甚至会影响标签。
无论如何,这是 预期的结果:
+---------+-----------+-------+------------+--------------+------------------------+
| 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。
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)
且没有虚假聚合。尽管有时您可以撤消此类虚假聚合。
正确的对称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 ;
问题内容: 我遇到了来自java.util.Calendar的奇怪行为: 我想知道为什么会这样吗? 问题答案: 问题是您要从2013年1月30日开始使用日历。 然后,您将年份设置为2013年-这不是问题。 然后,您将月份设置为1(即2月)。您希望在这里发生什么?实际发生的情况是,它会记住需要将月份设置为1,而 不是 重新计算实际时间值。根据文档(Emphsis我的), 将 在您调用to时重新计算时
为什么第一次计算的结果比第二次计算的结果大? 结果:
在一个视图分页程序中,我有几个片段,其中一个片段使用嵌套的scrollview和一个header和一个RecycerView: 标记“header”代表了一个复杂的布局,我不想在这里发布,因为它扩展了代码。 当我在选项卡之间切换时,它会滚动到回收器视图。标题被隐藏了,我必须向上滚动才能看到它。 有什么原因吗?我不想在我的适配器中使用类型,如果我可以避免它。
下面的代码是一个小示例,可以轻松重现问题。所以我有 String 类型的变量,它设置了默认值。我有3种方法: getter 塞特 将字符串转换为布尔值的方便方法 自省不会将getter作为readMethod返回,将setter作为WriteMethod返回。相反,它将isTest()方法作为readMethod返回。setter为空。 从文档中我了解到,如果类型是boolean,那么" is "
null 结果将是0xFFFFFFFFFFFFFFFF0000(正确,寄存器溢出,但我们只更改2字节) 结果将为0x00000000000000000000(WTF?,寄存器溢出,但结果更改所有8个字节而不是4个字节。为什么是0x00000000000000,而不是0xFFFFFF00000000)
但是,我犯了一个错误,写了这样一段话: 然后我写道: 没有打印任何输出,所以我的问题是,基本上,为什么?