找到你要的答案

Q:Group by 2 columns regardless of order

Q:按顺序排列2列

The following query

select a.message, a.sender_id, a.rec_id, a.id, a.is_seen, b.total_msg, b.last_id, users.name
from tbl_message a left join users on (users.id=a.sender_id)
inner join
  (select sender_id, rec_id, max(id) last_id, count(*) total_msg
         from tbl_message group by sender_id,rec_id
  )b on a.id=b.last_id 
order by a.id desc 

gives the result as below:

+----------------------------+-----------+--------+----+---------+-----------+---------+------+
| message                    | sender_id | rec_id | id | is_seen | total_msq | last_id | name |
+----------------------------+-----------+--------+----+---------+-----------+---------+------+
| latest testing l5 aug      |         2 |     1  | 12 |      0  |       5   |     12  | B    |
| testing                    |         1 |     2  | 11 |      1  |       3   |     11  | A    |
| this msg of A              |         1 |     3  |  9 |      0  |       1   |      9  | A    |
| this is again 3rd msg of C |         3 |     1  |  6 |      1  |       3   |      6  | C    |
+----------------------------+-----------+--------+----+---------+-----------+---------+------+

I want the result as:

For sender_id/rec_id = 1 or 2 id = 12 and for sender_id/rec_id = 1 or 3 id = 9

以下查询

select a.message, a.sender_id, a.rec_id, a.id, a.is_seen, b.到tal_msg, b.last_id, users.name
from tbl_message a left join users on (users.id=a.sender_id)
inner join
  (select sender_id, rec_id, max(id) last_id, count(*) 到tal_msg
         from tbl_message group by sender_id,rec_id
  )b on a.id=b.last_id 
order by a.id desc 

给出如下结果:

+----------------------------+-----------+--------+----+---------+-----------+---------+------+
| message                    | sender_id | rec_id | id | is_seen | 到tal_msq | last_id | name |
+----------------------------+-----------+--------+----+---------+-----------+---------+------+
| latest testing l5 aug      |         2 |     1  | 12 |      0  |       5   |     12  | B    |
| testing                    |         1 |     2  | 11 |      1  |       3   |     11  | A    |
| this msg of A              |         1 |     3  |  9 |      0  |       1   |      9  | A    |
| this is again 3rd msg of C |         3 |     1  |  6 |      1  |       3   |      6  | C    |
+----------------------------+-----------+--------+----+---------+-----------+---------+------+

我想要的结果:

对于sender_id / rec_id = 1或2的ID = 12,sender_id / rec_id = 1或3的ID = 9

answer1: 回答1:

Looks like you need to join with all the grouped columns

Try this

 SELECT a.message
    ,a.sender_id
    ,a.rec_id
    ,a.id
    ,a.is_seen
    ,b.total_msg
    ,b.last_id
    ,users.NAME
FROM tbl_message a
LEFT JOIN users ON (users.id = a.sender_id)
INNER JOIN (
    SELECT sender_id
        ,rec_id
        ,max(id) last_id
        ,count(*) total_msg
    FROM tbl_message
    GROUP BY sender_id
        ,rec_id
    ) b ON a.sender_id=b.sender_id and a.rec_id=b.rec_id and a.id = b.last_id
ORDER BY a.id DESC

看起来你需要加入所有的分组列

试试这个

 SELECT a.message
    ,a.sender_id
    ,a.rec_id
    ,a.id
    ,a.is_seen
    ,b.到tal_msg
    ,b.last_id
    ,users.NAME
FROM tbl_message a
LEFT JOIN users ON (users.id = a.sender_id)
INNER JOIN (
    SELECT sender_id
        ,rec_id
        ,max(id) last_id
        ,count(*) 到tal_msg
    FROM tbl_message
    GROUP BY sender_id
        ,rec_id
    ) b ON a.sender_id=b.sender_id and a.rec_id=b.rec_id and a.id = b.last_id
ORDER BY a.id DESC
answer2: 回答2:

I think you need to remove one column from the GROUP BY clause of the derived table (subquery)

select a.message, a.sender_id, a.rec_id, a.id, a.is_seen, b.total_msg, b.last_id, users.name
from tbl_message a left join users on (users.id=a.sender_id)
inner join
  (select sender_id, rec_id, max(id) last_id, count(*) total_msg
         from tbl_message group by sender_id 
  )b on a.id=b.last_id 
order by a.id desc 

I would expect this to result in rows only for the following

SenderID ID
2        12
1        9
3        6

我想你需要通过派生表的条款从组中删除一列(子查询)

select a.message, a.sender_id, a.rec_id, a.id, a.is_seen, b.到tal_msg, b.last_id, users.name
from tbl_message a left join users on (users.id=a.sender_id)
inner join
  (select sender_id, rec_id, max(id) last_id, count(*) 到tal_msg
         from tbl_message group by sender_id 
  )b on a.id=b.last_id 
order by a.id desc 

我希望这会导致行只为下列

SenderID ID
2        12
1        9
3        6
answer3: 回答3:

It sounds like you want to group rows by the sender_id,rec_id participants pair regardless of which order they appear in (i.e. sender_id,rec_id or rec_id,sender_id should be part of the same group).

If so, change your group by from

group by sender_id, rec_id

to

group by least(sender_id,rec_id), greatest(sender_id,rec_id)

Using greatest and least will ensure that each conversation will be grouped by the participants regardless of which order they appear in.

这听起来像你想的sender_id组行,rec_id参与者对无论哪个顺序出现(即sender_id,rec_id或rec_id,sender_id应是同一集团的一部分)。

如果是,请更改您的组

group by sender_id, rec_id

group by least(sender_id,rec_id), greatest(sender_id,rec_id)

使用最大和最少将确保每个会话将分组的参与者,无论他们出现在。

php  mysql  sql