Извлечени данных из БД, в зависимсоти от значени одного поля, но с особыми условиями.

Гриша К. · 23 Мар 2006

Извлечени данных из БД, в зависимсоти от значени одного поля, но с особыми условиями.

Извлекаю данные из 2-х таблиц используя left join.

Пример запроса:

PHP:

$query = "SELECT DISTINCT cities.cityid, orders.status
FROM cities LEFT JOIN orders 
ON cities.cityid = orders.cityid";

Пример результата:

PHP:

|-----------|--------|
|  cityrus  | status |
|-----------|--------|
| Морозовск |  NULL  |
| Морони    |     0  |
| Моршанск  |     1  |
| Мосальск  |     2  |
| Москва    |     0  |
| Москва    |     1  |
| Москва    |     2  |
|-----------|--------|

Значение 'status' может иметь значение NULL или 0, 1, 2
Получается, что одному и тому же значению 'cityrus' может соответсвовать либо NULL, либо 0, 1, 2

Мне надо извлеч данные таким образом, что либо status is null либо одно из значений 1 или 2 или 3:
- если status содержит значения 0, 1 и 2, тогда извлекаю только те значения из cityrus, где status = 1; (Если в значениях status есть 1, то извлекаю именно это значение)
- если status содержит значения 0, и 2, тогда извлекаю только те значения из cityrus, где status = 0
- если status содержит значения 2, тогда извлекаю только те значения из cityrus, где status = 1

PHP:

// Вот я могу только так извлеч, возможно ли та как я описал?:
where orders.status is null or orders.status = 1

-~{}~ 24.03.06 00:27:

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Вот нашел в книге раздел:
Функции управления потоком выполнения:
Там есть к примеру примеры

PHP:

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'

Вот как мне применить эти функции к запросу:

PHP:

$query = "SELECT DISTINCT cities.cityid, orders.status 
FROM cities LEFT JOIN orders  
ON cities.cityid = orders.cityid";

Чтобы соответсвовать следующему уловию:
# Если 'status' содержит 1 (и уже неважно что еще оно содержит), то выводит значение 'city' только с status=1

# Если 'status' содержит 0 и не содержит 1 (и уже неважно что еще оно содержит), то выводит значение 'city' только с status=0

# Если 'status' содержит 2, то выводит значение 'city' с status=2

# Ну а в остальных случаях status is nulll

Я могу извлеч только значения к примеру с 1 и с null:
where orders.status is null or orders.status = 1

Никак не могу с этим разобраться, помогите пожалуйста.

baev · 24 Мар 2006

Мне надо извлеч данные таким образом

Описание невнятное, особенно вот это:

если status содержит значения 2, тогда извлекаю только те значения из cityrus, где status = 1

Что, вообще, 'status' означает?

P.S. Вообще, похоже, тут не DISTINCT нужен, а GROUP BY с выборкой максимального значения.

Гриша К. · 24 Мар 2006

==============================
==============================
==============================

Вот какой я попробовал сделать запрос:

PHP:

SELECT DISTINCT cities.cityid
FROM cities
LEFT JOIN orders ON cities.cityid = orders.cityid
WHERE if( orders.status =1, orders.status =1, if( orders.status =0, orders.status =0, orders.status IS NULL ) )

Что я ожидаю от этого запроса:
Если orders.status =1, то извлекаем поле 'cityid' для которого orders.status =1,
в остальных случаях (если условие не выполняется), если orders.status =0, то извлекаем поле 'cityid' для которого orders.status =0, если нет то извлекаем где orders.status IS NULL

Но результат получается такой, что выводятся поля с orders.status = 0 и orders.status = 1.
Ну вот никак немогу извлеч иначе

baev · 24 Мар 2006

Смотрите мой постскриптум выше.

si · 24 Мар 2006

если status содержит значения 2, тогда извлекаю только те значения из cityrus, где status = 1

как это если status=2 то брать status=1 ?

baev · 24 Мар 2006

Тьфу, млин...

Только сейчас дошло, что таблиц — две.

Гриша К. реальный запрос и результат покажите.
(То, что Вы в качестве примера привели, явно выдумано. И ясности это нифига не добавляет...)

Гриша К. · 24 Мар 2006

baev, спасибо за ответ.
Попробую объяснить так:
есть таблица orders(cityid, status) и таблица cities(cityid, city),
Если orders.status = 1, то означет, что информация размещенная для города (его ИД cityid) к примеру Москва, размещена.
Если orders.status = 0, то означет, что информация размещенная для города (его ИД cityid) к примеру Москва, не проверена.
Если orders.status = 2, то означет, что информация размещенная для города (его ИД cityid) к примеру Москва, недопущена.
Так как для одного города есть много информации, то и orders.status для этого города может быть разный. Т.е. status - это пометки модератора (проверено, непроверено, недопущено).
Мне надо извлеч из базы данных список всех городов, я конечно мог их извлеч просто из таблицы "cities". Но я хочу извлеч их так, чтобы те города (cities.cityid - ИД, cities.city - Название), для которых есть проверенная информация (orders.status = 1), выделялиьс синим цветом, остальные города, были черные и чтобы в писке город неповторялся.

PHP:

// Вот как данные выводятся (Результат просто переписал, сделав вывод у себя)
|-----------|--------|
|  cityrus  | status |
|-----------|--------|
| Морозовск |  NULL  |
| Морони    |     0  |
| Морони    |     2  |
| Моршанск  |     1  |
| Мосальск  |     2  |
| Москва    |     0  |
| Москва    |     1  |
| Москва    |     2  |
|-----------|--------|

// Вот как хотелось бы извлеч данные
|-----------|--------|
|  cityrus  | status |
|-----------|--------|
| Морозовск |  NULL  |
| Морони    |     0  |
/*| Морони    |     2  |*/// Без этой строки, потому что уже есть со status = 0
| Моршанск  |     1  |
| Мосальск  |     2  |
/*| Москва    |     0  |*/// Без этой строки потому что уже есть status = 1
| Москва    |     1  |
/*| Москва    |     2  |*/// Без этой строки потому что уже есть status = 1
|-----------|--------|

-------------------
Да уж с объяснениями я наверно намудрил. Distinct, здесь тоже нужен, но он для другого. А вот group by интересно, я никогда непользовался. Но только не по максимальному значению.

baev · 24 Мар 2006

для которых есть проверенная информация (orders.status = 1), выделялиьс синим цветом, остальные города, были черные.

А нафига тогда наличие "0" и "2" проверять?!

Гриша К. · 24 Мар 2006

baev, ну смотрите
если выводить так

PHP:

where orders.status is null or orders.status = 1

То получается, что если orders.status = 2 (только 2 к примеру), то данный город тогда не будет выводиться. А мне же нужно вывести весь список городов, где orders.status = 1, тот выделяю, а если orders.status is null или orders.status =0 или orders.status = 2, то не выделяю. Но в тоже время нельзя чтобы города дублировались.

baev · 24 Мар 2006

Всё равно не понимаю.

Такой вариант:

Код:

SELECT cities.cityrus,
(IF (orders.status = 1, 1, MAX(orders.status))) AS status   
FROM cities, orders
WHERE cities.cityid = orders.cityid
GROUP BY cities.cityrus;

— чем не устраивает?

Гриша К. · 24 Мар 2006

baev, я наверное плоховато объяснял и вас недопонимал. Но ваш запрос извлекает все как надо, только мне надо еще извлекать значения status равные NULL, я по пробовал вот так:

PHP:

SELECT DISTINCT cities.cityid, cities.cityrus, (IF (orders.status = 1, 1, MAX(orders.status))) as status
FROM cities 
LEFT JOIN orders ON cities.cityid = orders.cityid
GROUP BY cities.cityid

Немоглибы вы только написать, если вдруг код некорректный.

Данные выводятся как хотелось, как надо: http://phpclub.ru/talk/showthread.php?postid=592887#post592887

Я щас читал про GROUP BY в разделе Агрегатные функции group by, там упоминается и MAX() (берет максимальное значени, это я понял).
Вот что делает group by, никак немогу понять, в другой книге она описывается вместе с функцией avg(). Никак не пойму, что делает group by.
Ну я так понимаю, что вданном случае, при извлечении трех столбцов из БД
cities.cityid, cities.cityrus, (IF (orders.status = 1, 1, MAX(orders.status))) as status
я могу сделать GROUP BY cities.cityid или GROUP BY cities.cityrus?

И хорошо, что вы привели здесь вариант с 'as status', Я разобрался для чего это и например если я извлекаю кол-во строк, то буду делать так
SELECT count(cityrus) as cityrus from cities

И может быть если будет время, то если возможно в 2-х словах смысл group by ();

baev, огромное вам спасибо. Вы пытались понять, что я говорю и несмотря на мои плохие объяснения, вы всеже разобрались в них, и написали мне правильное готовое решение, вы меня очень выручили.

--------------------------------------------------------------
По поводу GROUP BY, я нашел информацию:
"Если используется GROUP BY, выходные строки сортируются в соответсвии со столбцами указанными в GROUP BY, как если бы было указано ORDER BY для тех же столбцов..."

-~{}~ 24.03.06 03:47:

-----------------
У меня код сократился на 54 строки, потому что я там обрабатывал массивы с этми данными, такими закручинными способами. Щас просто красота. Все с помощью baev.

baev · 24 Мар 2006

если возможно в 2-х словах смысл group by

«В двух словах» — просто достаточно дословно с английского перевести. Получим «группировать по».

В Вашем случае: сначала мы выбрали все названия городов и все статусы заказов, связанные по ID города (условие в WHERE). Потом сгруппировали все статусы по названиям городов. И уже в группах проводим все действия со статусами.

-~{}~ 24.03.06 05:28:

надо еще извлекать значения status равные NULL, я по пробовал вот так:

Так IF'ы, вроде, друг в друга можно вкладывать.
Не пробовали?

Что-то типа:

Код:

SELECT cities.cityrus,
(IF (orders.status = 1, 1, (IF (MAX(orders.status)), MAX(orders.status), 'ничего нету'))) AS status   
FROM cities, orders
WHERE cities.cityid = orders.cityid
GROUP BY cities.cityrus;

— только скобки я не проверял, сами посмотрите...

Гриша К. · 24 Мар 2006

baev, вот теперь я понял смысл GROUP BY, спасибо вы очень хорошо объяснили. Ну ни как я не догонял этой функции.

IF'ы можно вкладывать в друг друга, я проверил.
Попробовал ваш пример, извлечение было какое-то странное, только часть данных, но действительно получилось с пустыми строками.
Ну я сейчас, с вашей помощью, разобрался и оставлю вариант с левосторонним объединением.

У меня вопрос один появился по этой теме:
Если вместо целого названия города, я извлекаю только его первую букуву, то выводятся все строки со status = NULL, 0, 1, 2

PHP:

SELECT DISTINCT MID(cities.cityrus, 1, 1) as cityrus, (IF (orders.status = 1, 1, MAX(orders.status))) as status 
FROM cities  
LEFT JOIN orders ON cities.cityid = orders.cityid 
GROUP BY cityrus

Вот только, что в голову пришла идея, получилось извлеч, в группу я взял также только первую букву города:

PHP:

SELECT DISTINCT MID(cities.cityrus, 1, 1) as cityrus, (IF (orders.status = 1, 1, MAX(orders.status))) as status 
FROM cities  
LEFT JOIN orders ON cities.cityid = orders.cityid 
GROUP BY cityrus MID(cities.cityrus, 1, 1)

Но почему это условие не выполняется: (IF (orders.status = 1, 1, MAX(orders.status))), извлекаются только MAX(orders.status)
Непойму почему.
Столько всяких вариантов попробовал, но не выполняется это условие и все. Попробую с устра подумать.

-~{}~ 24.03.06 17:01:

--------------------------------------------
Вот как же всетаки извлеч данные как показано в коде выше, чтобы условие IF (orders.status = 1, 1, MAX(orders.status))) выполнилось. Подскажите пожалуйста, ну никак не разберусь.

-~{}~ 24.03.06 18:03:

Какой-то брет происходит, если для описанного выше кода сделать такое условие:
(IF (orders.status is null, 1, MAX(orders.status))), то выполняется вот эта часть условия IF (orders.status is null, 1,

Ну почему же вот это условие не выполняется IF (orders.status = 1, 1, MAX(orders.status)))
Бред какой-то, тупая база данных достала.

-~{}~ 24.03.06 18:15:

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
_._._._._._._._._._._._._._._._._._._._._._._._._._._._
======================================

Вот что я заметил, если я извлекаю данные так:

PHP:

SELECT DISTINCT cities.cityrus, orders.status 
FROM cities   
LEFT JOIN orders ON cities.cityid = orders.cityid  
GROUP BY cities.cityrus

То получается, что данные извлекаются точно также как с условием (IF (orders.status = 1, 1, MAX(orders.status))), ну по крайне мере значения status ранвы null или одному из чисел 0, 1, или 2

----------------------------
Если же я извлекаю данные так:

PHP:

SELECT DISTINCT MID(cities.cityrus, 1,1) as cityrus, orders.status 
FROM cities   
LEFT JOIN orders ON cities.cityid = orders.cityid  
GROUP BY MID(cities.cityrus, 1,1)

То данные извлекаются так, что status равен только NULL, для всех значений MID(cities.cityrus, 1,1), хотя для половина значений status равно 0, 1 или 2. Но почему они выводятся как Null и от этого условие не проходит.
Ну вот это невозможно понять.
Разъясните пожалуйста эту проблему.

-~{}~ 24.03.06 21:50:

-------------------------------------------------
||| || ||||| |||| |||||||||||||| |||||||| |||||||||| ||||||||||||||||||||||||||||
|||| |||||||||||||| |||||||| |||||||||| |||| ||| || |||||||||||||||||||||||||||||||
|||| ||| || |||| |||||||||||||| ||||||||||| |||||||| ||||||||||||||||||||||||||
||| || ||||| |||| |||||||||||||| |||||||| |||||||||| ||||||||||||||||||||||||||||||||||
|||| |||||||||||||| |||||||| |||||||||| |||| ||| ||||||||||||||||||||||||||
|||| ||| || |||| |||||||||||||| ||||||||||| |||||||| ||||||||||||||||||||||||||| |||||||||
-------------------------------------------------

[1] Извлекая первую букву из строки (cities.cityrus) в БД приведенным ниже образом, извдекаются все значения 'cities.cityrus' для которых 'orders.status' is NULL:

PHP:

SELECT DISTINCT MID(cities.cityrus, 1,1) as cityrus, orders.status  
FROM cities    
LEFT JOIN orders ON cities.cityid = orders.cityid   
GROUP BY MID(cities.cityrus, 1,1)

[2] Я попробовал извлеч первую букву из из строки (cities.cityrus) в БД, извлекая среднее значение AVG(orders.status), в этом случае, все значени 'cities.cityrus' для которых 'orders.status' было либо число 0, 1, 2, либо NULL

PHP:

SELECT DISTINCT MID(cities.cityrus, 1,1) as cityrus, AVG(orders.status) 
FROM cities    
LEFT JOIN orders ON cities.cityid = orders.cityid   
GROUP BY MID(cities.cityrus, 1,1)

[3] Тогда я решил применить эту функцию в условии (IF (orders.status) = 1, 1, MAX(avg(orders.status)))), в таком случае данные извлекаются как надо:

PHP:

SELECT DISTINCT MID(cities.cityrus, 1,1) as cityrus, (IF (AVG(orders.status) > 0 and AVG(orders.status) <= 1, 1, MAX(avg(orders.status))))
FROM cities    
LEFT JOIN orders ON cities.cityid = orders.cityid   
GROUP BY MID(cities.cityrus, 1,1)

Вот как-то непонятно получается, это все. Так получается, что извлекая данные первым способом [1], Mysql игнорирует числа 0, 1, 2 из поля 'orders.status'.
Но когда я извлекаю данные вторым способом [2], и поле 'orders.status' вместо чисел 0, 1, 2 соедржит к примеру 0.0000, 0.8182, 2.0000, то MySQL их не игнорирует.
Поле orders.status имеет следующий тип: `status` tinyint(4) NOT NULL,

baev · 25 Мар 2006

Гриша К., мне, если честно, во всём Вами написанном разбираться влом.

1. Мне не понятно зачем 'DISTINCT', если там уже есть 'GROUP BY'?

2. Как Вы в поле tinyint(4) умудрились вставить «к примеру 0.0000, 0.8182, 2.0000»?

Гриша К. · 25 Мар 2006

baev, да уж я тут нарассуждал. DISTINCT, забыл убрать.
Вот посмотрите пример, я извлекаю первую букву города MID(cities.cityrus, 1,1), где 'status для города равен 1', либо Максимальное значение. Т.е. тотже пример, что вы мне привели, тольк о извлекаю первую букву города и делаю левосторннее объединение.

Если просто извлекать первую букву города, то извлекаются значения MID(cities.cityrus, 1,1) для которых 'status равен только NULL (остальные значения status почему-то не извлекаются)

PHP:

SELECT MID(cities.cityrus, 1,1) as cityrus, orders.status
FROM cities     
LEFT JOIN orders ON cities.cityid = orders.cityid    
GROUP BY MID(cities.cityrus, 1,1)

Если в описанном выше примере извлекать первую букву города и среднее значение 'status', то отобразятся первые буквы города, для которых 'status' равен NULL или 0.0000, 0.8182, 2.0000 (если делать просто поле INT(4), то тоже самое):

PHP:

SELECT MID(cities.cityrus, 1,1) as cityrus, AVG(orders.status)

И поэтому я решил сделать, SELECT таким:

PHP:

SELECT MID(cities.cityrus, 1,1) as cityrus, (IF (AVG(orders.status) > 0 and AVG(orders.status) <= 1, 1, MAX(avg(orders.status))))

Тогда первые буквы города извлекаются так, если status = 1 (avg(status) > 0 and avg(status) <=1, 1), то извлекаются первые буквы со status = 1, в остальных случаях извлекается масимальное значение status (MAX(avg(orders.status))).

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

baev · 25 Мар 2006

Подсказки:
— группируйте не по первой букве, а по полному названию,
— без IF'ов у Вас выводится случайно выбранное «первое попавшееся» значение статуса.

Гриша К. · 25 Мар 2006

baev, спасибо за подсказки.

— группируйте не по первой букве, а по полному названию,

я вывожу только первую букву, для того чтобы создать список из букв для навигации.
Т.е. городов выводиться 1500. На странице они выводяться по буквам. Список букв выводиться из БД в виде ссылок.
Поэтому мне и надо извлекать именно первую букву.
Если я буду извлекать по целому названию, то у меня выводиться наприме 300 городов на букву 'К'.
Конечно я могу потом в php скрипте это обработать, но это много лишней работы.

Ну вот при таком решении (IF (AVG(orders.status) > 0 and AVG(orders.status) <= 1, 1, MAX(avg(orders.status)))), буквы выводяться как надо. Хотя мне не совсем понятна функция AVG, среднее значение как она берет?

— без IF'ов у Вас выводится случайно выбранное «первое попавшееся» значение статуса.

Спасибо за разъяснение, теперь понятно.

Извлечени данных из БД, в зависимсоти от значени одного поля, но с особыми условиями.

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

si

Administrator

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок

baev

‹°°¬•

Гриша К.

Новичок