SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 14.09.2012 06:34:30

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Ломаю голову над тем как составить запрос

Есть таблица

CREATE TABLE IF NOT EXISTS `tech_professions_names` (
  `profession_id` bigint(20) unsigned NOT NULL,
  `language` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `profession_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `profession_language_unique` (`profession_id`,`language`),
  KEY `profession_id` (`profession_id`),
  KEY `profession_name` (`profession_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Надо выбрать по одной записи к каждому profession_id который будет указан в IN (...), с такой добавкой, если есть название профессии на языке юзера, то выбрать на его языке (там типа как с доменами ru en de etc), если же нету, то выбрать на английском, если нету и его то на языке который есть. Я вижу запрос примерно таким
SELECT *
FROM `way`.`tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1 )
AND (
`language` = 'en'
OR `language` = 'de'
OR `language` LIKE '%'
)
GROUP BY `profession_id`

либо
SELECT *
FROM `way`.`tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1 )
AND (
`language` = 'en'
OR `language` = 'de'
OR `language` LIKE '%'
)
LIMIT 1

Привычка с ПХП думать, что в перечислениях ИЛИ если срабатывает первое условие, то дальше не ищется, но тут походу не такой расклад sad


Скажи миру - НЯ!

Неактивен

 

#2 17.09.2012 05:26:44

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

SELECT `profession_id`, SUBSTR(min(CONCAT(`r`,`language`),2) `language` FROM
(SELECT `profession_id`, `language`, if(`language`='язык юзера',1,if(`language`='en'),2,3)  r
FROM `way`.`tech_professions_names` WHERE `tech_professions_names`.`profession_id` IN (..)) t
GROUP BY `profession_id`

Неактивен

 

#3 17.09.2012 10:17:20

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

SELECT `profession_id`, SUBSTR(min(CONCAT(`r`,`language`),2) `language` FROM
(SELECT `profession_id`, `language`, if(`language`='ru',1,if(`language`='en'),2,3)  r
FROM `way`.`tech_professions_names` WHERE `tech_professions_names`.`profession_id` IN (1,2,3,4)) t
GROUP BY `profession_id`

Выдаёт
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2) `language` FROM (SELECT `profession_id`, `language`, if(`language`='ru',1,if' at line 1


Скажи миру - НЯ!

Неактивен

 

#4 17.09.2012 13:41:09

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

Скобку потерял, нужно
SUBSTR(min(CONCAT(`r`,`language`)),2)

Неактивен

 

#5 17.09.2012 13:58:57

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

SELECT `profession_id`, SUBSTR(min(CONCAT(`r`,`language`)),2) `language` FROM
(SELECT `profession_id`, `language`, if(`language`='ru',1,if(`language`='en'),2,3)  r
FROM `way`.`tech_professions_names` WHERE `tech_professions_names`.`profession_id` IN (1,2,3,4)) t
GROUP BY `profession_id`

Выдало
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),2,3) r FROM `way`.`tech_professions_names` WHERE `tech_professions_names`.`p' at line 2


Скажи миру - НЯ!

Неактивен

 

#6 17.09.2012 15:11:20

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

if(`language`='ru',1,if(`language`='en',2,3))
вот что значит ночью писать

Неактивен

 

#7 17.09.2012 22:02:01

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language`
FROM (

SELECT `profession_id` , `language` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 )
)t
GROUP BY `profession_id`

Очень даже хорошо работает, только вот я сколько не вчитывалась в мануал по этим командам, я не могу понять схему действий sad
Если не затруднит, можете объяснить, что там происходит, чтоб я могла если нужно редактировать под нужные мне изменения?
Особенно не понятно, что происходит тут "SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 )" и тут "if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) )"


Скажи миру - НЯ!

Неактивен

 

#8 18.09.2012 00:40:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
создает доп колонку по имени `r`, в которой находится 1 если язык русский, 2 если английский и 3 во всех остальных случаях.


(SELECT `profession_id` , `language` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 )
)t
Образует временную таблицу `t`, в которой содержится id профессии, язык и доп поле r.
Осталось только сгруппировать эту таблицу по id профессии. Но выбирать поля, не входящие в группировку, без группирующей функции нельзя. Т.е. если мы напишем
SELECT `profession_id` , `language` , min(r) ... GROUP BY `profession_id`
то значение `language` будет произвольное, а не соответствующее min(r).


Поэтому объединяем число со строкой CONCAT( `r` , `language` ), выбираем минимальное значение, а затем отрезаем первый символ, который является значением поля `r` и получаем на выходе нужный `language`.

Неактивен

 

#9 18.09.2012 01:24:03

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

я правильно поняла, что вот тут добавляя вложения, можно увеличить количество языков?

if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
if( `language` = 'ru', 1, if( `language` = 'en', 2, if( `language` = 'de', 3, 4 ) ) ) r

?


Скажи миру - НЯ!

Неактивен

 

#10 18.09.2012 01:43:40

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

Я настолько была рада результату, что проморгала исчезновение третьего столбца. Сейчас добавила его, и остолбенела, имена из разных строчек смешаны. Язык указан один, а слово выдаётся на совсем другом

SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language` , `profession_name`
FROM (

SELECT `profession_id` , `language` , `profession_name` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 )
)t
GROUP BY `profession_id`


Скажи миру - НЯ!

Неактивен

 

#11 18.09.2012 02:11:55

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

animegirl написал:

я правильно поняла, что вот тут добавляя вложения, можно увеличить количество языков?

if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
if( `language` = 'ru', 1, if( `language` = 'en', 2, if( `language` = 'de', 3, 4 ) ) ) r

?

Да.

Неактивен

 

#12 18.09.2012 02:15:57

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

animegirl написал:

Я настолько была рада результату, что проморгала исчезновение третьего столбца. Сейчас добавила его, и остолбенела, имена из разных строчек смешаны. Язык указан один, а слово выдаётся на совсем другом

SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language` , `profession_name`
FROM (

SELECT `profession_id` , `language` , `profession_name` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 )
)t
GROUP BY `profession_id`

О чем я и писал выше - нельзя при группировке выбирать поле без группирующей функции.
SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language` , SUBSTR( min( CONCAT( `r` , `profession_name` ) ) , 2 ) `profession_name` ....

Неактивен

 

#13 19.09.2012 14:54:34

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language` , SUBSTR( min( CONCAT( `r` , `profession_name` ) ) , 2 ) `profession_name`
FROM (

SELECT `profession_id` , `language` , `profession_name` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 )
)t
GROUP BY `profession_id`

Выдало белиберду.
1. некоторые language записи не совпадали с profession_name .
2. У одной записи нарисовалось такое в proffession_name "d0a1d182d180d0b5d0bbd18cd0b1d0b0" в базе ничего подобного не существует sad


Скажи миру - НЯ!

Неактивен

 

#14 20.09.2012 11:39:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

Да, действительно, такое может быть. Тогда выбирайте этим запросом только `profession_id` и `language`. А `profession_name` вторым запросом или через join.

Неактивен

 

#15 20.09.2012 16:07:35

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

В каком плане вторым запросом? Мне же нужно, чтоб оно соответствовало друг другу O_o


Скажи миру - НЯ!

Неактивен

 

#16 20.09.2012 22:28:15

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

Комбинация (`profession_id`,`language`) однозначно характеризует строку.Поэтому и `profession_name` будет соответствующим.

select t1.`profession_id`, t1.`language`, t2.`profession_name` from (
SELECT `profession_id` , SUBSTR( min( CONCAT( `r` , `language` ) ) , 2 ) `language`
FROM (
SELECT `profession_id` , `language` , `profession_name` , if( `language` = 'ru', 1, if( `language` = 'en', 2, 3 ) ) r
FROM `tech_professions_names`
WHERE `tech_professions_names`.`profession_id`
IN ( 1, 2, 3, 4 ))t GROUP BY `profession_id`
) t1 join `tech_professions_names` t2 on t1.`profession_id`=t2.`profession_id` and  t1.`language`= t2.`language`;

Неактивен

 

#17 21.09.2012 13:56:25

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

Аааааа, супер, работает как надо, теперь засела разбираться как функционирует.
Спасябки!


Скажи миру - НЯ!

Неактивен

 

#18 21.09.2012 18:29:50

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

Правила тут чуток запрос, чтоб потом если, что понят, о чём речь, и параллельно усваивала, ново вы ученное. Да вот задумалась над самым начальным запросом, а там нужно вообще выбирать вот эти вещи?

SELECT `tmp_professions_names_sorted_grouped`.`profession_id`,`tmp_professions_names_sorted_grouped`.`profession_language`,`tmp_professions_names_full`.`profession_name` FROM
    (
    SELECT `profession_id`,SUBSTR( min( CONCAT( `language_priority`,`language`)),2) `profession_language`
    FROM
        (
        SELECT `profession_id`,`profession_language`,`profession_name`,IF(`profession_language`='ru',1,IF(`profession_language`='en',2,3)) `language_priority`
        FROM `way`.`tech_professions_names`
        WHERE `tech_professions_names`.`profession_id`
        IN (1,2,3,4)
        ) `tmp_professions_names_language_priority`
    GROUP BY `profession_id`
    ) `tmp_professions_names_sorted_grouped`
join `way`.`tech_professions_names` `tmp_professions_names_full` on `tmp_professions_names_sorted_grouped`.`profession_id`=`tmp_professions_names_full`.`profession_id` and  `tmp_professions_names_sorted_grouped`.`profession_language`= `tmp_professions_names_full`.`profession_language`;


SELECT `profession_id`,`profession_language`,`profession_name`,IF(`profession_language`='ru',1,IF(`profession_language`='en',2,3)) `language_priority`

?


Скажи миру - НЯ!

Неактивен

 

#19 21.09.2012 19:07:32

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

Нет, не нужно (это осталось не убранным из предыдущей редакции запроса).

Неактивен

 

#20 21.09.2012 19:52:46

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

Я вот над чем задумалась, мы в самом начале процесса делаем выборку по ИД, а в конце джойним целую таблицу. А можно джойнить только первую выборку? И увеличит ли это скорость?


Скажи миру - НЯ!

Неактивен

 

#21 21.09.2012 20:05:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Ломаю голову над тем как составить запрос

Можно, но ухудшит.

Во-первых, сейчас мы в вначале выбираем только (`profession_id`,`language`), которые являются уникальным ключом, т.е. обращение к данным не происходит (вся необходимая информация содержится в индексе). А в предлагаемом вами варианте придется сразу выбирать (`profession_id`,`language`,`profession_name`).

Второе, объединяя таблицу с первой выборкой используется индекс (первая выборка это временная таблица и данные из неё по ключу ищутся в основной таблице). Т.е. операция быстрая. А если объединять выборку с самой собой, то будет перебор (так как временная таблица индекса не содержит).

Неактивен

 

#22 22.09.2012 00:21:02

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

Re: Ломаю голову над тем как составить запрос

vasya написал:

Можно, но ухудшит.

А если объединять выборку с самой собой, то будет перебор (так как временная таблица индекса не содержит).

С одной стороны я вас поняла, с другой стороны посмотрим так.
Таблица на 100М записей. Передано 100 ИД, получено ~500 записей (до группировки).
И того у нас есть два варианта для джойна, таблица в 100М на диске но с индексами, либо таблица на 500 записей в RAM но без индексов.


Скажи миру - НЯ!

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson