查询，从具有相同值的多个行中只获取一行

I have a notification table as follows

我有一个通知表，如下所示

|id|user_receiver|user_sender|post_id|action|date|is_read

here user_sender is the person who generated notification, user_receiver is one who got the notification, post_id is id of post, action can be like, comment etc, is_read is 1 if its read by receiver else 0

这里user_sender是生成通知的人，user_receiver是收到通知的人，post_id是post的id，操作可以是like, comment等，is_read是1，如果它被receiver else读取为0

I want to get all notifications for loggedin user

我想获得loggedin用户的所有通知

query that I am using is

我正在使用的查询是is

SELECT id, user_receiver, user_sender, post_id, action, 
 max(date) as date, is_read 
FROM notification 
WHERE user_receiver=$ses_user 
group by user_sender, action,post_id,is_read 
order by date desc

but it doesn't give me latest rows even though I am using max(date) and I also want to get number of notifications that are unread.

但是它不会给我最新的行，即使我正在使用max(date)，而且我还想获得一些未读的通知。

I want only one row if there are multiple rows having same post_id, user_sender and action together and that should be the latest one. For example a user likes a post, a row is added to the table, then user dislikes and likes again, then a new row is added again, I want only the new row only.

如果有多个行具有相同的post_id、user_sender和action，那么我只需要一行，这应该是最新的一行。例如，一个用户喜欢一个帖子，一个行被添加到表中，然后用户不喜欢和喜欢，然后再添加一个新的行，我只想要新的行。

2 个解决方案

#1

To get the latest row in MySQL, you need to use a join or correlated subquery:

要获取MySQL中的最新行，需要使用连接或相关子查询:

SELECT id, user_receiver, user_sender, post_id, action, date, is_read
FROM notification n
WHERE user_receiver=$ses_user and
      date = (select max(date)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id and
                    n2.is_read = n.is_read
             )
order by date desc;

In other databases, you would simply use the row_number() function (or distinct on in Postgres).

在其他数据库中，您只需使用row_number()函数(或在Postgres中不同)。

EDIT:

编辑:

For the biggest id:

最大的id:

SELECT id, user_receiver, user_sender, post_id, action, date, is_read
FROM notification n
WHERE user_receiver=$ses_user and
      id   = (select max(id)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id
             )
order by date desc;

If you want the number of rows where isread = 1, then you can do something like:

如果你想要isread = 1的行数，那么你可以这样做:

SELECT sum(is_read = 1)
FROM notification n
WHERE user_receiver=$ses_user and
      id   = (select max(id)
              from notification n2
              where n2.user_sender = n.user_sender and
                    n2.action = n.action and
                    n2.post_id = n.post_id
             );

#2

Hope this post will help, there is a sample query with illustration:

希望这篇文章会有所帮助，有一个示例查询与说明:

SELECT 
    BookId, 
    BookName, 
    BookCategory, 
    BookReferenceNumber, 
    COUNT( BookReferenceNumber ) AS HowManyRecs, 
    BookDetails, 
    BookStatus
FROM 
    tbl_BOOKS
WHERE (
    BookReferenceNumber LIKE '42324%'
    OR BookName LIKE '%42324%'
)    
AND (
    BookStatus = 'Available'
)
GROUP BY 
    BookReferenceNumber
HAVING (
    HowManyRecs > 0
)

(Taken from techqube.blogspot.in)

(取自techqube.blogspot.in)

#1