如何将总行数计为 LIMIT 中的一行? [英] How can I count summed rows as one row in LIMIT?
问题描述
我想根据这些规则选择用户的通知:
I want to select user's notifications according to these rules:
- 所有未读通知
- 总是 2 个阅读通知
- 至少 15 个通知(默认)
这是我获取用户通知 ID 的查询:
Here is my query which gets user's notifications ids:
( SELECT id FROM events -- all unread messages
WHERE author_id = ? AND seen = 0
) UNION
( SELECT id FROM events -- 2 read messages
WHERE author_id = ? AND seen <> 0
ORDER BY date_time desc
LIMIT 2
) UNION
( SELECT id FROM events -- at least 15 rows by default
WHERE author_id = ?
ORDER BY seen, date_time desc
LIMIT 15
)
然后我在上面的查询中选择匹配的 id 加上其他这样的信息:(由于现实中的某些原因，我不想将这两个查询结合起来)
And then I select the matched ids in query above plus other info like this: (I don't want to combine these two queries because of some reasons in reality)
SELECT SUM(score) score, post_id, title, content, date_time
FROM events
GROUP BY post_id, title, content, date_time
ORDER BY seen, MAX(date_time) desc
WHERE id IN ($ids)
一切正常.
问题是:当第一个查询选择15行都具有相同的post_id时，第二个查询会将它们相加并显示为一个通知行与总分.
The problem is: When the first query selects 15 rows which all have the same post_id, then the second query will sum them up and show it as one notification row with total-scores.
我想我还必须在第一个查询中添加 SUM() 吗?那GROUP BY?有什么想法吗?
I guess I have to add that SUM() also in the first query? And that GROUP BY? Any idea?
问题的一个例子，如果用户获得了 15 个赞，第一个查询将它们选为 15 个通知，第二个查询将其选为一个通知.我怎样才能得到 15 个分开的通知?(那些将在第二个查询中求和的通知在第一个查询中应该算作一个通知，如何?)
An example of the problem, if an user earn 15 upvotes, the first query selects them as 15 notifications, and the second query make it one notification. How can I get 15 separated notification? (those notification which will be summed in the second query should be counted as one notification in the first query, how?)
推荐答案
由于您最终希望每个组有 15 行，因此在我看来，您应该对组而不是消息制定规则.
As you finally want 15 rows per group, you should have rules on groups rather than on messages in my opinion.
您可以按组汇总数据，然后检查该组是否应出现在您的结果中.您可以在带有条件聚合的 HAVING 子句中执行此操作，即在条件表达式上使用的聚合函数.这是一种计算未读消息的方法，例如:
You can aggregate your data per group and then check whether the group shall be in your results. You'd do this in the HAVING clause with conditional aggregation, i.e. an aggregation function used on a conditional expression. This is one method to count unread messages for example:
SUM(CASE WHEN seen = 0 THEN 1 ELSE 0 END)
这是另一个:
COUNT(CASE WHEN seen = 0 THEN 1 END)
(ELSE 分支被省略，默认为 null，不计入.)
(The ELSE branch is omitted and defaults to null, which is not count.)
在 MySQL 中，这些表达式更简单，因为 false 等于 0，true 等于 1.所以在 MySQL 中，您可以计算:
In MySQL these expressions are even simpler, because false equals 0 and true equals 1. So in MySQL you'd count with:
SUM(seen = 0)
您也可以使用其他聚合函数:
You can use other aggregation functions, too:
HAVING MAX(seen = 0) = 0 -- no unread messages
HAVING MIN(seen = 0) = 1 -- no read messages
现在让我们选择至少有一封未读邮件的所有组:
Now let's select all groups with at least one unread message:
SELECT SUM(score) AS score, post_id, title, content, date_time
FROM events
GROUP BY post_id, title, content, date_time
HAVING SUM(seen = 0) > 0;
(我们也可以使用HAVING MAX(seen = 0) = 1.)
现在您的 UNION 方法可以让所有群组获得至少一条未读消息，并根据需要添加尽可能多的其他群组以获得至少 15 个群组:
Now your UNION approach to get all groups with at least one unread message, plus as many other groups as necessary to get at least 15 groups:
(
SELECT SUM(score) AS score, post_id, title, content, date_time, SUM(seen = 0) as unread
FROM events
GROUP BY post_id, title, content, date_time
HAVING SUM(seen = 0) > 0
)
UNION
(
SELECT SUM(score) AS score, post_id, title, content, date_time, SUM(seen = 0) as unread
FROM events
GROUP BY post_id, title, content, date_time
ORDER BY SUM(seen = 0) DESC, date_time DESC
LIMIT 15
)
ORDER BY (unread = 0), date_time DESC;
如果您想要上述组的单个 ID，请使用 IN:
If you want the single IDs for above groups, then use IN:
SELECT id
FROM events
WHERE (post_id, title, content, date_time) IN
(
SELECT post_id, title, content, date_time
FROM (<above query>) q
);
这篇关于如何将总行数计为 LIMIT 中的一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
