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

animegirl · 14.09.2012 06:34:30

Есть таблица

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

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

vasya · 17.09.2012 05:26:44

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`

animegirl · 17.09.2012 10:17:20

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

vasya · 17.09.2012 13:41:09

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

animegirl · 17.09.2012 13:58:57

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

vasya · 17.09.2012 15:11:20

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

animegirl · 17.09.2012 22:02:01

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`

Очень даже хорошо работает, только вот я сколько не вчитывалась в мануал по этим командам, я не могу понять схему действий

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

vasya · 18.09.2012 00:40:27

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`.

animegirl · 18.09.2012 01:24:03

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

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

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

?

animegirl · 18.09.2012 01:43:40

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

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`

vasya · 18.09.2012 02:11:55

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

?

Да.

vasya · 18.09.2012 02:15:57

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` ....

animegirl · 19.09.2012 14:54:34

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" в базе ничего подобного не существует

vasya · 20.09.2012 11:39:17

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

animegirl · 20.09.2012 16:07:35

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

vasya · 20.09.2012 22:28:15

Комбинация (`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`;

animegirl · 21.09.2012 13:56:25

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

animegirl · 21.09.2012 18:29:50

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

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`

?

vasya · 21.09.2012 19:07:32

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

animegirl · 21.09.2012 19:52:46

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

vasya · 21.09.2012 20:05:39

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

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

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

animegirl · 22.09.2012 00:21:02

vasya написал:
Можно, но ухудшит.

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

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

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

#1 14.09.2012 06:34:30

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

#2 17.09.2012 05:26:44

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

#3 17.09.2012 10:17:20

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

#4 17.09.2012 13:41:09

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

#5 17.09.2012 13:58:57

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

#6 17.09.2012 15:11:20

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

#7 17.09.2012 22:02:01

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

#8 18.09.2012 00:40:27

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

#9 18.09.2012 01:24:03

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

#10 18.09.2012 01:43:40

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

#11 18.09.2012 02:11:55

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

animegirl написал:

#12 18.09.2012 02:15:57

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

animegirl написал:

#13 19.09.2012 14:54:34

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

#14 20.09.2012 11:39:17

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

#15 20.09.2012 16:07:35

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

#16 20.09.2012 22:28:15

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

#17 21.09.2012 13:56:25

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

#18 21.09.2012 18:29:50

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

#19 21.09.2012 19:07:32

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

#20 21.09.2012 19:52:46

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

#21 21.09.2012 20:05:39

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

#22 22.09.2012 00:21:02

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

vasya написал:

Board footer