按列分组以获取具有列最大值的行 [英] group by column to get the row with largest value of a column

问题描述

我正在实施通知系统.我的表结构有点像这样.

I am implementing notification system. My table structure is somewhat like this..

id(自动递增主键)
user_id(应显示通知的用户的用户ID，整数)
trigger_by(触发通知的用户，int)
post_id(存在通知的帖子，int)
类型(通知的类型，整数)
看到(已读取通知，布尔)

id (auto increment primary key)
user_id (user id of the user whom the notification should be shown, int)
triggered_by (user who triggered the notification, int)
post_id (post for which the notification exists, int)
type (type of the notification, int)
seen (has the notification been read, bool)

当我将通知标记为可见时，我正在使用post_id和type，这意味着我们可以放心地假设，如果看到具有最大id的行，则将看到该post_id和type的所有先前行.

When I am marking a notification as seen, I am using post_id and type, that means we can safely assume that if the row with largest id is seen, all the previous rows for that post_id and type would be seen.

现在，我想获取与该post_id和type的先前条目组合的行，以及为该post_id和type注册的行数.我当前的查询是

now, I want to fetch rows combined with previous entries for that post_id and type, plus a count of rows registered for that post_id and type. My current query is

select max(x.id) as id, 
   x.post_id as post_id, 
   x.seen as seen, 
   x.user_id as user_id, 
   x.triggered_by as triggered_by, 
   x.type as type, 
   x.count as count
from (SELECT notification.id, 
       notification.post_id, 
       notification.user_id, 
       notification.triggered_by, 
       notification.type, 
       c.count,  notification.seen 
     FROM `notification`
         join (select post_id, type, count(id) as count 
               from notification group by post_id, type) c 
           on c.type=notification.type 
             and c.post_id=notification.post_id 
     Where notification.user_id=1) x 
Group by post_id 
Order by id desc limit 10

此查询的问题在于，最外层查询的"group by"子句将返回任何随机行的"seen"列，在该列中，我希望它返回其他数据，然后从具有最大id的行开始计数.

The problem with this query is that the 'group by' clause of the outer most query is returning any random row's 'seen' column where as I want it to return data other then count from the row with the largest id.

推荐答案

尝试一下:

 Select id, post_id, seen,
    user_id, triggered_by, type, 
   (Select Count(*) From notification
    Where type = N.Type
      And post_id = n.post_id) Count
 From Notification n
 where user_id = 1
 Order by id desc limit 10

除了与该post_id和类型的先前条目结合"是什么意思?您是说要将此查询合并到输出的一行中，即源表中不同行中的值吗?就像从一排中获取post_id，但从另一行中获取Triggered_By吗?

Except what do you mean by "combined with previous entries for that post_id and type" ?? Are you saying that you want this query to combine in one row of output, values from different rows in the source table ? Like get the post_id from one row, but get the Triggered_By from some different earlier row?

这篇关于按列分组以获取具有列最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！