LINUX.ORG.RU

Помогите сделать sql запрос...


1

2

mysql

Существуют две таблицы user и mail.

user:

id - айди

login - логин

mail:

mail_id - айди

mail_to - от кого

mail_from - кому

mail_date - дата

mail_text - текст сообщения

Третий день в глубоком раздумье..

Нужно сделать выборку последнего сообщения от каждого собеседника (не важно от меня было сообщение или от него, важно что последнее) и отсортировать по дате

Подготовил бы тестовые данные, а то народу, думаешь, не влом сейчас будет создавать 2 таблицы, заполнять их, чтоб протестировать тебе запрос?

Читай про GROUP BY. Одним запросом сходу не скажу, надо проверять, а двумя будет что-то вроде select что надо фром where mail_id IN (select mail_id from mail group by (mail_to,mail_from) order by mail_date desc), нормально написать и поправить ошибки.

BaBL ★★★★★ ()
Ответ на: комментарий от BaBL

Простите, исправляюсь.

create database temp1;

use temp1;

CREATE TABLE IF NOT EXISTS `mail` ( `mail_id` int(11) NOT NULL AUTO_INCREMENT, `mail_to` int(11) NOT NULL DEFAULT '0', `mail_from` int(11) NOT NULL DEFAULT '0', `mail_msg` text NOT NULL, `mail_date` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`mail_id`), FULLTEXT KEY `mail_msg` (`mail_msg`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2207969;

CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(36) NOT NULL DEFAULT ", PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=259473;

INSERT INTO `user` VALUES (1, 'login1'), (1898, 'login2'), (123, 'login3');

INSERT INTO `mail` VALUES (1, 1898, 1, 'новый текст 1', 1223046865), (2, 1898, 1, 'старый текст 1', 1223046265), (3, 1, 1898, 'старый текст 1', 1223046855), (4, 1, 1898, 'старый текст 1', 1223046265), (5, 123, 1, 'новый текст 2', 1223046875), (6, 123, 1, 'старый текст 2', 1223046225), (7, 1, 123, 'старый текст 2', 1223046825), (8, 1, 123, 'старый текст 2', 1223046225);

mbippl ()
Ответ на: комментарий от BaBL

В том то и дело что я уже обчитался group by....

select * from `mail`,`user` where `user`.`id`= 1 and (`mail`.`mail_to`=`user`.`id` or `mail`.`mail_from`=`user`.`id`) and mail_id IN (select mail_id,mail_msg from mail group by mail_to,mail_from order by mail_date desc)

mbippl ()
Ответ на: комментарий от mbippl
SELECT *
FROM mail
WHERE mail_id IN (
		SELECT mail_id
		FROM mail
		WHERE mail_id IN (
				SELECT mail_id
				FROM mail
				GROUP BY mail_to
					,mail_from
				ORDER BY mail_from DESC
				)
		GROUP BY mail_from * mail_to
			,mail_from + mail_to
		);

pi11 ★★★★★ ()
Ответ на: комментарий от pi11

Спасибо!

А не могли бы объяснить в двух словах что значит умножение и сложение в групп бай. Честно говоря никогда не видел подобного, иначе как магией это не обзовешь.

Как можно было бы улучшить архитектуру базы на Ваш взгляд?

mbippl ()
Ответ на: комментарий от mbippl

Группировка по сложению и умножению в данном случае, чтобы сгруппировать по отдельным беседам между 2 людьми, независимо от того кто кому написал. Например есть беседа mail_to =1 mail_from=3, и mail_to=3, mail_from=1. Это одна и та же беседа. 1+3=4, 1*3=3 независимо от того кто кому писал (не зависит от направления сообщения). Нужно и умножение и деление потому что 2+6=8 и 1+7=8, но 2*6=12!=1*7.

Чтобу улучшить архитектуру - опиши подробнее свою задачу.

pi11 ★★★★★ ()

Для начала, задача в такой постановке не решается. Нужна еще одна «координата» - твой id. Если твой id известен, задача становится смешным дестким г..м:

select mail.* from (
   select remote, max(mail_date) as mail_date from
      (
         select mail_from as remote, mail_date from mail where mail_to = :myid
         union all
         select mail_to as remote, mail_date from mail where mail_from = :myid
      )
   group by remote
   ) lastmails
join mail on
   lastmails.remote = mail.mail_from and lastmails.mail_date = mails.mail_date
   or
   lastmails.remote = mail.mail_to and lastmails.mail_date = mails.mail_date
no-dashi ★★★★★ ()
Последнее исправление: no-dashi (всего исправлений: 2)
Ответ на: комментарий от pi11

Твой запрос в любой СУБД кроме мастадайSQL (также иногда именуемого иногда MySQL) не скомпилируется и не выполнится. Посмотрим на внутренний запрос:

SELECT mail_id
FROM mail
WHERE mail_id IN (
				SELECT mail_id
				FROM mail
				GROUP BY mail_to
					,mail_from
				ORDER BY mail_from DESC
				)
GROUP BY mail_from * mail_to
			,mail_from + mail_to
Согласно стандарту, при если поле появилось в SELECT-блоке запроса с GROUP BY, то оно должно в обязательном порядке встречаться в блоке GROUP BY. У тебя это правило нарушено относительно поля mail_id, и это как бы приговор.

no-dashi ★★★★★ ()

А UNION разве не спасёт от всех этих монструазных подзапросов?

Kalashnikov ★★★ ()
Ответ на: комментарий от mbippl

Как можно было бы улучшить архитектуру базы на Ваш взгляд?

У тебя ошибка изначальная есть: у письма может быть много получателей, а у тебя - только один. Кроме того, получатели бывают разных типов - в частности To, CC, Bcc. Соответственно, необходимо убрать все упоминания о mail_from, mail_to из таблицы mail, и создать таблицу recipients(id,mail_id,user_id,role_id), где user_id это реципиент, а role_id это его роль, например 1 - sender, 2 - to, 3 - cc, 4 - bcc.

no-dashi ★★★★★ ()
Ответ на: комментарий от Kalashnikov

С того:

автор встал в тупик на тривиальном запросе ->

у него просто недостает опыта с базами данных ->

он не мог не совершить ошибку на этапе проектирования.

no-dashi ★★★★★ ()
Ответ на: комментарий от no-dashi

Твой запрос в любой СУБД кроме мастадайSQL (также иногда именуемого иногда MySQL) не скомпилируется и не выполнится

Я в курсе. К тому же я везде postgreSQL использую.

pi11 ★★★★★ ()
select u.login
  ,(select top 1 m1.text
    from mail m1
    where m1.mail_date in
       (select max(m2.mail_date)
        from mail m2
        where u.id in(m2.mail_to, m2.mail_from))
   ) mail_text
  ,(select top 1 m1.mail_date
    from mail m1
    where m1.mail_date in
       (select max(m2.mail_date)
        from mail m2
        where u.id in(m2.mail_to, m2.mail_from))
   ) mail_date
from user u
order by 3 desc
anonymous ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.