MySQL.RU - Webboard

Re: как совместить джойн и группировку (барабан) 10/09/2013 - 08:55:51

> Original message text:
> From: барабан - 04/09/2013 - 21:56:13
> Subject:как совместить джойн и группировку
> -----------------
>
> Приветствую, товарищи. Что-то ума у меня не хватает как это сделать.
> Раньше делал в несколько проходов: сохранял промежуточные результаты
> в виде таблицы и использовал ее в следующем запросе. Но то было на
> MS SQL, там можно сделать SELECT INTO TABLE, а в MYSQL нельзя. Да и
> другие причины есть. Уж с понедельника не могу осилить и сделтать
> работающий запрос. Что есть: две таблицы. Первая - список звонков
> сотрудников, вторая содержит коды городов и названия. Задача проста:
> подставить коды городов.
>
> таблицы
>
> calls:
> dst time
> -------------------------------
> 302299867 21
> 302299864 83
> 302289643 56
> 342289364 18
> 342209834 10
> 342298347 34
> 342290384 87
> 343239847 43
> 343277463 49
> 343243666 75
> 343349875 14
> 343375384 48
>
> dest
> code name
> -------------------------------
> 3 Russia
> 4 Russia
> ...
> 302 Russia Chita reg
> 342 Russia Perm reg
> 343 Russia Yekaterinburg reg
> 3022 Russia Chita
> 3422 Russia Perm
> 3432 Russia Yekaterinburg
> 3433 Russia Yekaterinburg
>
>
> на выходе надо получить что-то вроде
> code total name
> -------------------------------
> 3022 160 Russia Chita
> 3422 149 Russia Perm
> 3432 167 Russia Yekaterinburg
> 3433 62 Russia Yekaterinburg
>
> но сейчас у меня получается что-то вроде такого:
> code total name
> -------------------------------
> 3 160 Russia
> 302 149 Russia Chita reg
> 342 149 Russia Perm reg
> 343 62 Russia Yekaterinburg reg
> 3022 160 Russia Chita
> 3422 149 Russia Perm
> 3432 167 Russia Yekaterinburg
> 3433 62 Russia Yekaterinburg
>
> т.е. не выбирается по максимальной величине кода
>
> Запрос типа такой
> SELECT
> c.time,
> MAX(d.code) AS code,
> d.name
> FROM calls AS c
> LEFT JOIN dest AS d
> ON c.dst LIKE CONCAT(d.code,'%')
> GROUP BY code
>
> Будьте добры, наставьте на путь истиный, а то с джойнами у меня совсем туго
> Спасибо!
>
> PS: данные и запрос приближены к реальным
>

From: барабан - 05/09/2013 - 22:08:49
Subject:как совместить джойн и группировку
-----------------
Я знаком с SQL с 99 года, еще на фокспро кодили. БД - не основное мое занятие, и хотя я пишу по десятку запросов в месяц, я многого не знаю.

В общем моя задача на 100% верно решилась вот так:

<pre>
-- таблица с ценами
CREATE TABLE IF NOT EXISTS `price` (
`code` int(10) NOT NULL,
`price` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `price` (`code`, `price`) VALUES
(302, 5),
(342, 2),
(343, 1);

SELECT
-- тут собираем данные в кучу из двух запросов
data.code AS dest,
dest.name AS name,
COUNT(data.code) AS calls,
@billsec:=data.time AS billsec,
@cost:=(SELECT -- в этом селекте получаем цену
price -- на основе кода города из таблицы dest
FROM price
WHERE code IN ( -- тут отбираем совпадения, быстрее LIKE в 100 раз
SUBSTRING(data.code,1,1),
SUBSTRING(data.code,1,2),
SUBSTRING(data.code,1,3),
SUBSTRING(data.code,1,4)
)
ORDER BY LENGTH(code) DESC -- сортируем и возвращаем одно значение
LIMIT 1 ) AS cost,
@cost * @billsec AS total -- сумму пришлось считать переменными
FROM
(SELECT -- тут загружаем данные о звонках
SUM(c.time) AS time, -- общее время разговора
(SELECT -- тут ищем префикс в таблице с названиями
code
FROM dest
WHERE code IN (
SUBSTRING(c.dst,1,1),
SUBSTRING(c.dst,1,2),
SUBSTRING(c.dst,1,3),
SUBSTRING(c.dst,1,4)
)
ORDER BY LENGTH(code) DESC
LIMIT 1 ) AS code
FROM calls AS c
GROUP BY code) AS data, -- группируем по найденному коду
price, dest
WHERE data.code=dest.code -- связываем выборку звонков с префиксами

GROUP BY dest

</pre>

синтетический ключ не оптимален. У нас там три ключа на поля (дата, id, dst) и один композитный. В общем работает довольно быстро.

Спасибо за помощь!

[Это сообщение - спам!]

Последние сообщения из форума

Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL, версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь. 42865

РЕКЛАМА НА САЙТЕ

Создание сайтов | |