source

Maria Db - 사용자 ID가 = 변수인 모든 메시지 목록에서 최근 메시지를 가져옵니다.

ittop 2023. 7. 26. 22:28
반응형

Maria Db - 사용자 ID가 = 변수인 모든 메시지 목록에서 최근 메시지를 가져옵니다.

특정 사용자에 대한 최근 메시지 목록을 가져오려고 합니다. 내 탭은 다음과 같습니다.

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| sender_user_id | int(11)             | NO   |     | NULL    |                |
| remote_user_id | int(11)             | NO   |     | NULL    |                |
| message        | longtext            | NO   |     | NULL    |                |
| read           | tinyint(4)          | NO   |     | NULL    |                |
| created_at     | timestamp           | YES  |     | NULL    |                |
| updated_at     | timestamp           | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

테이블 만들기:

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sender_user_id` int(11) NOT NULL,
  `remote_user_id` int(11) NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `read` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

이것이 제가 지금까지 시도한 것입니다.SELECT sender_user_id, remote_user_id FROM messages m1 WHERE id = (SELECT MAX(m2.id) FROM messages m2 WHERE m1.sender_user_id = m2.sender_user_id AND m1.remote_user_id = m2.remote_user_id)

이는 가까운 것처럼 보이지만 분명히 당신이 검색한 userId를 고려하지 않으며 때때로 다음과 같은 중복 항목을 반환합니다.

MariaDB [snowdon]> SELECT sender_user_id, remote_user_id FROM messages m1 WHERE id = (SELECT MAX(m2.id) FROM messages m2 WHERE m1.sender_user_id = m2.sender_user_id AND m1.remote_user_id = m2.remote_user_id)
    -> ;
+----------------+----------------+
| sender_user_id | remote_user_id |
+----------------+----------------+
|              1 |              2 |
|              2 |              1 |
|              3 |              1 |
+----------------+----------------+
3 rows in set (0.00 sec)

첫 번째 행과 두 번째 행은 동일한 대화의 일부이므로 가장 최근의 행만 결과에 표시했으면 합니다.

누가 제가 SQL 작성하는 것을 도와줄 수 있나요?

탁스

당신은 원하는 것 같아요:

SELECT (CASE WHEN m.sender_user_id = ? THEN m.remote_user_id ELSE m.sender_user_id END) as other_user_id
FROM messages m
WHERE ? IN (m.sender_user_id, m.remote_user_id) AND
      m.id = (SELECT MAX(m2.id)
              FROM messages m2
              WHERE (m2.sender_user_id, m2.remote_user_id) IN 
                      ( (m.sender_user_id, m.remote_user_id),
                        (m.remote_user_id, m.sender_user_id)
                      )
             );

효율적인 검색을 지원할 수 있는 인덱스가 없습니다.따라서 다음 두 개의 인덱스를 생성해야 합니다.(sender_user_id, remote_user_id, id)그리고.(remote_user_id, sender_user_id, id)이제 ID=123인 사용자와 주고받은 최신 메시지를 찾을 때 다음 쿼리를 사용할 수 있습니다.

select m.*
from (
    select max(id) as id
    from (
        select remote_user_id as user_id, max(id) as id
        from messages
        where sender_user_id = 123
        group by remote_user_id
        union all
        select sender_user_id as user_id, max(id) as id
        from messages
        where remote_user_id = 123
        group by sender_user_id
    ) u
    group by user_id
) g
join messages m using(id)

언급URL : https://stackoverflow.com/questions/58570816/maria-db-get-most-recent-message-from-list-of-all-messages-where-user-id-var

반응형