|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Встретился с интересной ситуацией, которую пока не могу объяснить. Есть структура для организации "Личной комнаты" - один ведущий, много участников, каждый общается тет-а-тет с ведущим. У участника обязательно должен быть Login, а UserName заполняется им по желанию.
Есть дамп (utf-8)
Задача: Одним запросом вывести список пользователей, для каждого пользователя вывести помимо Login и UserName дату последнего сообщения и UserState пользователя, оставившего сообщение (если это не "ведущий", то значит, есть неотвеченные вопросы).
mysql> SELECT
u.id,
u.UserName,
u.Login,
(SELECT DATE_FORMAT(MAX(m.AddDate), '%d.%m.%Y %H:%i')
FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um
ON m.id_from_user = um.id
WHERE m.id_for_user = u.id) AS LastMessageDate,
IFNULL((SELECT um.UserState
FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um
ON m.id_from_user = um.id
WHERE m.id_for_user = u.id ORDER BY m.AddDate DESC LIMIT 0, 1), 2)
as LastUserState
FROM mmmod_PA_Users u
WHERE u.UserState = 2 ORDER BY u.Login;
+----+----------+-------+------------------+---------------+
| id | UserName | Login | LastMessageDate | LastUserState |
+----+----------+-------+------------------+---------------+
| 2 | Макс | m | 12.04.2009 12:46 | 1 |
| 3 | mmm2 | m2 | 10.04.2009 13:20 | 2 |
| 4 | | m3 | NULL | 2 |
+----+----------+-------+------------------+---------------+
3 rows in set (0.01 sec)Получаем ожидаемый результат. А теперь добавим сортировку по полю UserName
mysql> SELECT
u.id,
u.UserName,
u.Login,
(SELECT DATE_FORMAT(MAX(m.AddDate), '%d.%m.%Y %H:%i')
FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um
ON m.id_from_user = um.id
WHERE m.id_for_user = u.id) AS LastMessageDate,
IFNULL((SELECT um.UserState
FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um
ON m.id_from_user = um.id
WHERE m.id_for_user = u.id ORDER BY m.AddDate DESC LIMIT 0, 1), 2)
as LastUserState
FROM mmmod_PA_Users u
WHERE u.UserState = 2 ORDER BY u.UserName, u.Login;
+----+----------+-------+-----------------+---------------+
| id | UserName | Login | LastMessageDate | LastUserState |
+----+----------+-------+-----------------+---------------+
| 4 | | m3 | NULL | 2 |
| 3 | mmm2 | m2 | NULL | 2 |
| 2 | Макс | m | NULL | 1 |
+----+----------+-------+-----------------+---------------+
3 rows in set (0.00 sec)Т.е. все значения LastMessageDate стали равны значению NULL - вернее, значению, которое возвращается для пользователя, у которого нет ни одного сообщения. Вопроса в том, как исправить не стоит. Вопрос - почему так происходит?
Отредактированно Magz (13.04.2009 11:46:49)
Неактивен
У меня работает 
[celestia] root test > SELECT u.id, u.UserName, u.Login, (SELECT DATE_FORMAT(MAX(m.AddDate), '%d.%m.%Y %H:%i') FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um ON m.id_from_user = um.id WHERE m.id_for_user = u.id) AS LastMessageDate, IFNULL((SELECT um.UserState FROM mmmod_PA_Messages m JOIN mmmod_PA_Users um ON m.id_from_user = um.id WHERE m.id_for_user = u.id ORDER BY m.AddDate DESC LIMIT 0, 1), 2) as LastUserState FROM mmmod_PA_Users u WHERE u.UserState = 2 ORDER BY u.UserName, u.Login; +----+----------+-------+------------------+---------------+ | id | UserName | Login | LastMessageDate | LastUserState | +----+----------+-------+------------------+---------------+ | 4 | | m3 | NULL | 2 | | 3 | mmm2 | m2 | 10.04.2009 13:20 | 2 | | 2 | Макс | m | 12.04.2009 12:46 | 1 | +----+----------+-------+------------------+---------------+ 3 rows in set (0.00 sec) [celestia] root test > \s -------------- mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (x86_64) using readline 5.2 Connection id: 161 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.67-0ubuntu6 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 42 min 44 sec Threads: 1 Questions: 616 Slow queries: 0 Opens: 1498 Flush tables: 1 Open tables: 64 Queries per second avg: 0.240 --------------
Встречный вопрос — через обычный JOIN плохо работает?
SELECT u. ..., IF (MAX(m1.AddDate) > MAX(m2.AddDate), MAX(m1.AddDate), MAX(m2.AddDate))
FROM mmmod_PA_Users u
LEFT JOIN mmmod_PA_Messages m1 ON m1.id_from_user = u.id
LEFT JOIN mmmod_PA_Messages m2 ON m2.id_to_user = u.ud
WHERE u.UserState = 2
GROUP BY u.id
Неактивен
paulus написал:
У меня работает
Да, проверил на юниксе - тоже заработал. Значит, это глюки виндовой версии.
paulus написал:
Встречный вопрос — через обычный JOIN плохо работает?
Честно говоря, мне не пришел в голову такой способ. Однако, мне кажется, что делать LEFT JOIN два раза таблицы, которая будет априори очень большей (ведь в mmmod_PA_Messages будут лежать все записи всех пользователей) - не самый лучший способ. Поправьте меня, если я не прав.
Неактивен
Вот это надо потестировать. Кажется, что если будет ключик на (id, AddDate), то будет
выбираться одна строка с максимальной AddDate (надо смотреть explain, правда ли это).
Неактивен
Страниц: 1
