|
|
|
| Здравствуйте!
Имеется таблица messages:
----------------------------------------------------------------
| id | thred | from_id | to_id | time | text |
----------------------------------------------------------------
| 21 | 5 | 3 | 8 | 142931 | blah-blah|
| 22 | 6 | 5 | 3 | 142957| tatatatata|
| 23 | 5 | 8 | 3 | 142988 |gugugugu|
| 24 | 9 | 3 | 4 | 143071 | huh-huh | - я написал Вовану
* * *
|
И таблица clients:
--------------------------------------------------------------------
| id | name | slogan |
--------------------------------------------------------------------
| 3 | ivan | veni, vidi, vici |
| 4 | vovan | errare humanum est |
| 8 | kolyan | per aspera ad astra |
* * *
|
Где в табл. messages from_id & to_id - это id клиентов из таблицы clients
Переписка клиентов объединена в треды, то есть у двух конкретных собеседников один конкретный номер диалога (thred).
К кпримеру (табл 1) , id=21 - я, Иван (from_id), написал Коляну (to_id), а id=23 - он мне.
И нужно так построить запрос, чтобы выводило:
Колян | veni, vidi, vici | 2 | 142988 |
То есть, если в треде последняя реплика принадлежит Коляну, то выводится его имя, МОЙ слоган, количество реплик в треде (2) и время последней реплики.
Если же в треде последняя реплика (или единственная) моя, то, соответственно, выводятся такие данные (табл. 1, id=24):
Иван | errare humanum est | 1 | 143071 |
то есть, Иван написал челу со слоганом errare humanum est , количество реплик - 1, время последней (MAX(`time`)) - 143071
Помогите, пожалуйста, составить запрос MySQL.
Спасибо. | |
|
|
|
|
|
|
|
для: Deed
(06.04.2015 в 15:38)
| | Может замудрил но работает )
SELECT
`clients`.`name`,
`clients`.`slogan`,
`messages`.`time`,
(SELECT COUNT(`id`) FROM `messages` WHERE `messages`.`thread`=5) AS `count`
FROM `messages`
LEFT JOIN `clients` ON `clients`.`id`=`messages`.`from_id`
WHERE `messages`.`thread`=5
ORDER BY `messages`.`time` DESC
LIMIT 1
|
| |
|
|
|
|
|
|
|
для: KPETuH
(06.04.2015 в 18:13)
| | Спасибо большое за ответ!
Но мы не знаем номер треда. Это поле вообще нужно лишь для удаления его записей любым из участников диалога. Оно досталось "в наследство".
Я по-другому сформулирую вопрос: Как сначала выбрать все диалоги, в которых MAX(`time`) - время последней реплики принадлежит моему собеседнику? То есть, последняя реплика была его. Типа, прочитанные мной сообщения.
Ну, а как сделать выборку со своей последней репликой (написанные мной мессаги), я пойму :)
В итоге, мне нужно вывести GROUP BY по своим собеседникам. Только имя собеседника, количество реплик в данном диалоге (COUNT(messages1.id)) и время последней реплики.
Но это не проблема. Я только не знаю, как в запросе проверить, кому из собеседников принадлежит MAX(`time`) - последняя реплика в диалоге. | |
|
|
|
|
|
|
|
для: Deed
(06.04.2015 в 18:27)
| | С учетом что ваш `client`.`id`=3 смог выродить вот такую конструкцию ))
SELECT *,
(SELECT `messages`.`from_id` FROM `messages` WHERE `messages`.`time`=`t`.`max_time`) AS `from_id`
FROM (SELECT
`messages`.`thread`,
MAX(DISTINCT `messages`.`time`) AS `max_time`
FROM `messages`
GROUP BY `messages`.`thread`) AS `t`
WHERE (SELECT `messages`.`from_id` FROM `messages` WHERE `messages`.`time`=`t`.`max_time`)=3
|
| |
|
|
|
|
|
|
|
для: KPETuH
(07.04.2015 в 11:09)
| | Гигантское спасибо!
Сейчас начну "обмозговывать" Вашу подсказку. | |
|
|
|
|
|
|
|
для: Deed
(06.04.2015 в 18:27)
| | > Как сначала выбрать все диалоги, в которых MAX(`time`) - время последней реплики принадлежит моему собеседнику?
> вывести GROUP BY по своим собеседникам. Только имя собеседника, количество реплик в данном диалоге (COUNT(messages1.id)) и время последней реплики.
-- <?
SELECT
c.name, -- Только имя собеседника,
t.co, -- количество реплик в данном диалоге
m.time -- и время последней реплики
FROM(
SELECT
thred, -- номер диалога
MAX(`time`) AS m_time, -- максимальное время
COUNT(*) AS co -- количество сообщений
FROM
messages
WHERE
3 IN(from_id, to_id) -- в диалогах, где учавствуете вы (id=3)
GROUP BY
thred -- сгруппировав по номеру диалога
)t
JOIN
messages AS m -- самообъединение чтобы извлечь неагрегированные данные
ON -- из последнего сообщения в группе
t.thred = m.thred AND -- по номеру диалога
m.time = m_time -- и времени последнего сообщения
JOIN
clients AS c -- тут я думаю все понятно
ON
from_id = c.id
WHERE
m.from_id != 3 -- и убираем диалоги, в которых последнее сообщение не ваше (id=3)
|
P.S. Пожалуйста, прикладывайте дамп:
CREATE TABLE `clients` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`slogan` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `clients` (`id`, `name`, `slogan`) VALUES
(3, 'ivan', 'veni, vidi, vici'),
(4, 'vovan', 'errare humanum est'),
(8, 'kolyan', 'per aspera ad astra');
CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`thred` int(11) NOT NULL,
`from_id` int(11) NOT NULL,
`to_id` int(11) NOT NULL,
`time` int(11) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `messages` (`id`, `thred`, `from_id`, `to_id`, `time`, `text`) VALUES
(21, 5, 3, 8, 142931, 'blah-blah'),
(22, 6, 5, 3, 142957, 'tatatatata'),
(23, 5, 8, 3, 142988, 'gugugugu'),
(24, 9, 3, 4, 143071, 'huh-huh'),
(25, 7, 4, 8, 144931, 'blah-blah'),
(26, 6, 3, 5, 145957, 'tatatatata'),
(27, 5, 3, 8, 146988, 'gugugugu'),
(28, 9, 4, 3, 147071, 'huh-huh');
| Это ведь не сложно, а время очень экономит. | |
|
|
|
|
|
|
|
для: Sfinks
(08.04.2015 в 22:01)
| | Нет слов, масса Sfinks!!!
MAX(`спасибо`)!!! | |
|
|
|
|
|
|
|
для: Sfinks
(08.04.2015 в 22:01)
| | Eсли у Вас есть время, то вот:
-- Структура таблицы `messages1`
--
CREATE TABLE IF NOT EXISTS `messages1` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`thred` int(6) NOT NULL,
`cor_id` int(6) NOT NULL,
`to_id` int(6) NOT NULL,
`to_log` varchar(65) NOT NULL,
`anon` varchar(11) NOT NULL,
`text` text NOT NULL,
`time` int(12) NOT NULL,
`is_read` int(1) NOT NULL DEFAULT '0',
`cor_name` varchar(65) NOT NULL,
`cor_log` varchar(65) NOT NULL,
`cor_link` varchar(265) NOT NULL,
`cor_pict` varchar(122) NOT NULL,
`cor_mail` varchar(110) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
--
-- Дамп данных таблицы `messages1`
--
INSERT INTO `messages1` (`id`, `thred`, `cor_id`, `to_id`, `to_log`, `anon`, `text`, `time`, `is_read`, `cor_name`, `cor_log`, `cor_link`, `cor_pict`, `cor_mail`) VALUES
(1, 1, 42, 41, 'hec', '1', 'оозозо ощлщлзлз зозлзщз лхлзщоошозлхздхзд<br>', 1428490694, 0, 'Тортилла', 'tor', 'http://znaydu.ok/wow/42/ya-sama-bida-takoyu-300-let-tomu-nazad', 'http://i.imgur.com/YVnC6bQ.jpg', ''),
(2, 1, 42, 41, 'hec', '1', 'ррррощо', 1428490704, 0, 'Тортилла', 'tor', 'http://znaydu.ok/wow/42/ya-sama-bida-takoyu-300-let-tomu-nazad', 'http://i.imgur.com/YVnC6bQ.jpg', ''),
(11, 10, 41, 24, 'jes', '1', 'вияи екырыке srhydrs yhdhd<br>', 1428505372, 1, 'Гектор Саламанка', 'hec', 'http://znaydu.ok/wow/41/krasavets-muzhchina', 'http://i.imgur.com/R5XYUIu.jpg', ''),
(4, 4, 16, 41, 'hec', '1', 'тзщзшл mpjmpojkpo jjiho зоорщ озолз', 1428490773, 1, 'David Gilmour', 'dav', 'http://znaydu.ok/wow/16/nichegolichnogoprostoproverimperepi skunasayte', 'http://i.imgur.com/mYfmO6s.jpg', ''),
(5, 4, 16, 41, 'hec', '1', 'mklkml долтошошош отшщошош', 1428490782, 1, 'David Gilmour', 'dav', 'http://znaydu.ok/wow/16/nichegolichnogoprostoproverimperepi skunasayte', 'http://i.imgur.com/mYfmO6s.jpg', ''),
(6, 4, 16, 41, 'hec', '1', 'ргршгр и8 7н9н979р гощшощшрпа', 1428490791, 1, 'David Gilmour', 'dav', 'http://znaydu.ok/wow/16/nichegolichnogoprostoproverimperepi skunasayte', 'http://i.imgur.com/mYfmO6s.jpg', ''),
(9, 4, 16, 41, 'hec', '1', 'мык купукперкер рекрнонео к епкпукп', 1428504814, 1, 'David Gilmour', 'dav', 'http://znaydu.ok/wow/16/nichegolichnogoprostoproverimperepi skunasayte', 'http://i.imgur.com/mYfmO6s.jpg', ''),
(13, 10, 41, 24, 'jes', '1', 'vidfzvi vdfhsh мы лыармгрымщ<br>', 1428505698, 1, 'Гектор Саламанка', 'hec', 'http://znaydu.ok/wow/41/krasavets-muzhchina', 'http://i.imgur.com/R5XYUIu.jpg', ''),
(14, 10, 41, 24, 'jes', '1', 'сяшгрвшрсш duhdhd uhc<br>', 1428505909, 1, 'Гектор Саламанка', 'hec', 'http://znaydu.ok/wow/41/krasavets-muzhchina', 'http://i.imgur.com/R5XYUIu.jpg', '');
-- Структура таблицы `clients`
--
CREATE TABLE IF NOT EXISTS `clients` (
`id` int(7) NOT NULL AUTO_INCREMENT,
`moder` int(1) NOT NULL DEFAULT '0',
`claim` varchar(65) NOT NULL,
`claim_text` varchar(256) NOT NULL,
`category` varchar(65) NOT NULL,
`status` int(1) NOT NULL DEFAULT '1',
`ban` int(10) NOT NULL,
`boss` int(1) NOT NULL,
`hash` varchar(65) NOT NULL,
`login` varchar(65) NOT NULL,
`name` varchar(256) NOT NULL,
`since` varchar(65) NOT NULL,
`last_time` int(10) NOT NULL,
`zodiac` varchar(15) NOT NULL,
`age` int(3) NOT NULL,
`slogan` varchar(256) NOT NULL,
`crumb` varchar(256) NOT NULL,
`about_me` text NOT NULL,
`alls` text NOT NULL,
`about_lower_case` text NOT NULL,
`mail` varchar(67) NOT NULL,
`repa` varchar(88) NOT NULL,
`sex` varchar(15) NOT NULL,
`sex_for_search` varchar(15) NOT NULL,
`link_page` varchar(15) NOT NULL,
`link_name` varchar(15) NOT NULL,
`areal` varchar(65) NOT NULL,
`city` varchar(65) NOT NULL,
`ignore_id` text NOT NULL,
`view` int(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
FULLTEXT KEY `all` (`alls`),
FULLTEXT KEY `about_lower_case` (`about_lower_case`),
FULLTEXT KEY `alls` (`alls`),
FULLTEXT KEY `about_lower_case_2` (`about_lower_case`),
FULLTEXT KEY `about_lower_case_3` (`about_lower_case`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=43 ;
--
INSERT INTO `clients` (`id`, `moder`, `claim`, `claim_text`, `category`, `status`, `ban`, `boss`, `hash`, `login`, `name`, `since`, `last_time`, `zodiac`, `age`, `slogan`, `crumb`, `about_me`, `alls`, `about_lower_case`, `mail`, `repa`, `sex`, `sex_for_search`, `link_page`, `link_name`, `areal`, `city`, `ignore_id`, `view`) VALUES
(37, 0, '', '', 'легкие отношения', 1, 0, 0, 'd36cdd3378e747d', 'ronni', 'Рональд Рейган', '15 марта 2015г, 16:05', 1426428314, '', 0, 'Мужчина ищет женщину', 'muzhchina-ischet-zhenschinu', '', 'рональд рейган мужчина женщину легкие отношения мужчина ищет женщину muzhchina-ischet-zhenschinu', 'about_lower_case', '', 'http://i.imgur.com/aQ4NM3y.jpg', 'мужчина', 'женщину', 'relations', 'on-yeye', '', '', '', 6),
(39, 0, '', '', 'хобби', 1, 0, 0, 'd8148832aad7271', 'dim', 'Димочка', '15 марта 2015г, 17:19', 1426523010, 'Близнецы', 0, 'Люблю тонко резать газету!', 'lyublyu-tonko-rezat-gazetu', 'Такие звуки, что... Мммм!.. :)', 'димочка мужчина женщину близнецы хобби запорожская балабино люблю тонко резать газету! lyublyu-tonko-rezat-gazetu', 'about_lower_case', 'fao@bao.pu', 'http://i.imgur.com/quzA6qm.jpg', 'мужчина', 'женщину', 'hobby', 'on-yeye', 'Запорожская', 'Балабино', '', 0),
(40, 0, '', '', 'дружба', 1, 0, 0, '35a41c05ac76a7f', 'raz', 'Разраб', '23 марта 2015г, 15:49', 1427194089, '', 0, 'проверка скрипта', 'proverka-skripta', 'проверка скрипта после удаления второго агримента', 'разраб мужчина женщину дружба кемеровская луч проверка скрипта proverka-skripta', 'about_lower_case', 'raz@po.lo', 'http://i.imgur.com/erKj3ox.jpg', 'мужчина', 'женщину', 'friendship', 'on-yeye', 'Луганская', 'Счастье', '', 4);
|
И вот такую наваял в ожидании ответа "камасутру":
<?php
$allmessages=mysql_query("(SELECT COUNT(messages1.id) AS countmess, messages1.thred AS thread, MAX(messages1.time) AS max_time, MAX(messages1.id) AS max_id, messages1.anon AS newanon, MIN(messages1.is_read) AS is_read, messages1.to_id AS to_id, messages1.cor_id AS cor_id, messages1.cor_log AS cor_log, messages1.cor_name AS cor_name, messages1.cor_link AS cor_link, clients.slogan AS cor_slogan, clients.id AS notice_id, clients.name AS to_name, clients.crumb AS notice_crumb, 'to_me' AS `direct` FROM `messages1` LEFT JOIN `clients` ON messages1.to_id=clients.id WHERE `to_log`='$user' GROUP BY messages1.cor_name ORDER BY messages1.id DESC)
UNION All
(SELECT COUNT(messages1.id) AS countmess, messages1.thred AS thread, MAX(messages1.time) AS max_time, MAX(messages1.id) AS max_id, messages1.anon AS newanon, MIN(messages1.is_read) AS is_read, messages1.to_id AS to_id, messages1.cor_id AS cor_id, messages1.cor_log AS cor_log, messages1.cor_name AS cor_name, messages1.cor_link AS cor_link, clients.slogan AS cor_slogan, clients.id AS notice_id, clients.name AS to_name, clients.crumb AS notice_crumb, 'from_me' AS `direct` FROM `messages1` LEFT JOIN `clients` ON messages1.to_id=clients.id WHERE `cor_log`='$user' GROUP BY messages1.to_log ORDER BY messages1.id DESC) ORDER BY max_time DESC
",$bd)
?>
|
Сплошная интуиция...
Разбираю Ваш ответ! | |
|
|
|
|