Форум: Форум PHPФорум ApacheФорум Регулярные ВыраженияФорум MySQLHTML+CSS+JavaScriptФорум FlashРазное
Новые темы: 0000000
PHP Puzzles. Авторы: Кузнецов М.В., Симдянов И.В. PHP 5. На примерах. Авторы: Кузнецов М.В., Симдянов И.В., Голышев С.В. Самоучитель MySQL 5. Авторы: Кузнецов М.В., Симдянов И.В. Социальная инженерия и социальные хакеры. Авторы: Кузнецов М.В., Симдянов И.В. PHP на примерах (2 издание). Авторы: Кузнецов М.В., Симдянов И.В.
ВСЕ НАШИ КНИГИ
Консультационный центр SoftTime

Форум MySQL

Выбрать другой форум

 

Здравствуйте, Посетитель!

вид форума:
Линейный форум Структурный форум

тема: Сортировка внутри выборки
 
 автор: Deed   (06.04.2015 в 15:38)   письмо автору
 
 

Здравствуйте!
Имеется таблица 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.
Спасибо.

  Ответить  
 
 автор: KPETuH   (06.04.2015 в 18:13)   письмо автору
 
   для: 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

  Ответить  
 
 автор: Deed   (06.04.2015 в 18:27)   письмо автору
 
   для: KPETuH   (06.04.2015 в 18:13)
 

Спасибо большое за ответ!
Но мы не знаем номер треда. Это поле вообще нужно лишь для удаления его записей любым из участников диалога. Оно досталось "в наследство".
Я по-другому сформулирую вопрос: Как сначала выбрать все диалоги, в которых MAX(`time`) - время последней реплики принадлежит моему собеседнику? То есть, последняя реплика была его. Типа, прочитанные мной сообщения.
Ну, а как сделать выборку со своей последней репликой (написанные мной мессаги), я пойму :)

В итоге, мне нужно вывести GROUP BY по своим собеседникам. Только имя собеседника, количество реплик в данном диалоге (COUNT(messages1.id)) и время последней реплики.
Но это не проблема. Я только не знаю, как в запросе проверить, кому из собеседников принадлежит MAX(`time`) - последняя реплика в диалоге.

  Ответить  
 
 автор: KPETuH   (07.04.2015 в 11:09)   письмо автору
 
   для: 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

  Ответить  
 
 автор: Deed   (07.04.2015 в 12:43)   письмо автору
 
   для: KPETuH   (07.04.2015 в 11:09)
 

Гигантское спасибо!
Сейчас начну "обмозговывать" Вашу подсказку.

  Ответить  
 
 автор: Sfinks   (08.04.2015 в 22:01)   письмо автору
 
   для: 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_idto_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');
Это ведь не сложно, а время очень экономит.

  Ответить  
 
 автор: Deed   (09.04.2015 в 14:02)   письмо автору
 
   для: Sfinks   (08.04.2015 в 22:01)
 

Нет слов, масса Sfinks!!!
MAX(`спасибо`)!!!

  Ответить  
 
 автор: Deed   (09.04.2015 в 14:20)   письмо автору
 
   для: 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', 'мык купукперкер рекрнонео к &nbsp;епкпукп', 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)
?>


Сплошная интуиция...
Разбираю Ваш ответ!

  Ответить  
Rambler's Top100
вверх

Rambler's Top100 Яндекс.Метрика Яндекс цитирования