Выборка уникальных записей с максимальными датами, и затем выборка по найденому

@MaksT · Регистрация: 01.04.2014

Author24 — интернет-сервис помощи студентам

Как вообще такой запрос лучше составить?

У меня есть таблица User(id) и Message( id, fromUserId, toUserId, sendDate).
Мне нужно выбрать пользователей у которых есть переписка сообщениями например с пользователем с id=4, при этом они должны быть упорядочены по убыванию, в зависимости от того кто последний присылал сообщение.

Получается что с табл. Message, нужно выбрать user_id = (fromUserId OR toUserId), затем взять максимально новые даты sendDate, но что бы пользователи были уникальными. С этой задачей как раз справляется ORDER BY sendDate DESC, а после GROUP BY user_id ORDER BY sendDate DESC.

В общем эта часть запроса:

SQL

SELECT mes.*
FROM
(
    (SELECT `fromUserId` AS user_id, sendDate FROM `Message` WHERE `toUserId`=3)
      UNION
    (SELECT `toUserId` AS user_id, sendDate FROM `Message` WHERE `fromUserId`=3)
   ORDER BY sendDate DESC
) AS mes GROUP BY user_id ORDER BY sendDate DESC

Работает так как нужно, но как теперь сделать что бы с полученных user_id и sendDate выбрать Пользователей именно в полученном порядке. Например если в итоге у нас такой запрос возвратил user_id = (4,1,2), то в таком порядке и найти пользователей с id = (4,1,2).

Как так сделать? Помогите пожалуйста!

Я новичок в этих делах, но все таки пробовал сам что то намудрить, получилось так но не работает, я понимаю почему, там GROUP BY не там. )) Но я пытался))

SQL

SELECT mes.*, u.id, u.login
FROM
  USER AS u,
(
    (SELECT `fromUserId` AS user_id, sendDate FROM `Message` WHERE `toUserId`=3)
      UNION
    (SELECT `toUserId` AS user_id, sendDate FROM `Message` WHERE `fromUserId`=3)
   ORDER BY sendDate DESC
) AS mes GROUP BY user_id ORDER BY sendDate DESC
WHERE
  u.id=mes.user_id

Помогите пожалуйста, как нужно правильно делать такие запросы? Если я как то тупо обяснил что мне нужно спрашивайте я быстро уточню!

@cygapb-007 · 01.04.2014, 21:33

Можно так попробовать (хотя место на диске и жалко): http://sqlfiddle.com/#!2/b6e598/1

MySQL

create table Message( 
  id int auto_increment primary key, 
  fromUserId int references Users(id), 
  toUserId int references Users(id), 
  sendDate datetime
  -- индексы увеличивают место на диске и скорость обновлений
  -- но существенно уменьшают скорость поиска
  ,key FT_Dt(fromUserId,toUserId,sendDate) 
  ,key TF_Dt(toUserId,fromUserId,sendDate)
  );

MySQL

set @id=4;
 
select g.id, u.name, max(g.sendDate) last_sendDate
from(
  select m.toUserId AS id, max(m.sendDate) sendDate
    from Message m
    where m.fromUserId = @id
    group by m.toUserId -- задействован индекс FT_Dt
  union all
  select n.fromUserId, max(n.sendDate) 
    from Message n
    where n.toUserId = @id
    group by n.fromUserId -- задействован индекс TF_Dt
  )g
natural join Users u -- связь по полю ID
group by g.id -- мало влияет на скорость из-за небольшого объема данных
order by last_sendDate desc;

план выполнения:

ID	SELECT_TYPE	TABLE	TYPE	POSSIBLE_KEYS	KEY	KEY_LEN	REF	ROWS	FILTERED	EXTRA
1	PRIMARY		ALL					4	100	Using temporary; Using filesort
1	PRIMARY	u	eq_ref	PRIMARY	PRIMARY	4	g.id	1	100
2	DERIVED	m	ref	FT_Dt	FT_Dt	5		4	100	Using where; Using index
3	UNCACHEABLE UNION	n	ref	TF_Dt	TF_Dt	5		2	100	Using where; Using index
	UNION RESULT		ALL

@MaksT · 01.04.2014, 22:14 **[ТС]**

Огромедное спасибо cygapb-007! То что нужно! Я очень Вам благодарен!

Я пока мучился тоже вот наколхозил кое что, оно работает так же но я знаю что это мега тупой запрос)) :

SQL

SET @nn=4;
 
SELECT m.sendDate, u.*
FROM
        (
            (SELECT `fromUserId` AS user_id, sendDate FROM `Message` WHERE `toUserId`=@nn)
                UNION
            (SELECT `toUserId` AS user_id, sendDate FROM `Message` WHERE `fromUserId`=@nn)
                ORDER BY `sendDate` DESC
        ) AS m
    JOIN
        USER AS u
        ON m.user_id = u.id
 
GROUP BY id ORDER BY sendDate DESC

	01.04.2014, 22:14

Выборка уникальных записей с максимальными датами, и затем выборка по найденому

Решение