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

moose · 17.05.2011 12:22:59

Добрый день ВСЕМ!
Помогите, или подскажите как дописать запрос, я на одном месте завис, и не знаю что делать, знания СКЛ иссякли ))
Есть таблицы users, main, plans, periods, tarifs, hollidays

-------------------------------------
таблица users
------------------
| id | uid |
------------------
| test | 123 |

таблица main
----------------
| p_id | uid |
----------------
| 2 | 123 |

таблица plans
--------------------
| id | p_id |
--------------------
| 2 | 23 |

таблица periods
-----------------------------------------------------
| id | interval_id | begin | end | day |
-----------------------------------------------------
| 2 | 45 | 10:00:00 | 24:00:00 | 1 |

таблица tarifs
------------------------------------
| in_speed | out_speed |interval_id |
------------------------------------
| 128 | 128 | 3 |

таблица hollidays
-----------
| day |
-----------
| 3-8 |
| 1-7 |
| 2-23 |

В общем затор у меня на выборе нужного дня
Так-как в таблице periods может быть 1. day=1..7 -первый запрос 2. day=0 -второй запрос 3. day =0 и 8 где 8-праздинчный день
4. 1..8 каждый день + праздничные

Нужен запрос который работал бы при таких условиях:

Первый запрос: если periods.day например 1-понедельник, 3-среда, тогда учитывал эти дни по DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = per.day

Второй запрос: если periods.day=0 тоесть все дни - тогда просто смотрел на интервалы времени

Третий запрос: если в periods.day кроме первых двух вариантов есть еще 8 -праздничные дни, тогда смотрел в таблицу hollidays и сравнивал с текущей датой (месяц-день) если нету делал первый или второй вариант запроса

----------------------------------------
Первый запрос: Если есть в таблице periods несколько дней тогда выбирается то поле где день равняется сегодняшнему дню
AND DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = per.day
и происходит поиск скорости

SELECT tt.in_speed, tt.out_speed, intv.day

FROM trafic t

LEFT JOIN periods per ON (t.interval_id = per.id)

LEFT JOIN plans p ON (t.p_id = per.t_id)

LEFT JOIN main dv ON (dv.p_id = p.id )

LEFT JOIN users u ON (dv.uid = u.uid )

WHERE u.id= 'test' AND per.begin <= DATE_FORMAT( NOW(), '%H:%i:%S' ) AND per.end >= DATE_FORMAT( NOW(), '%H:%i:%S' )

AND DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = per.day

------------------------------------------
Второй запрос: Если есть в таблице periods всего один день который равняется=0 , тогда день не учитывается и происходит поиск скорости

SELECT t.in_speed, t.out_speed, per.day

FROM tarifs t

LEFT JOIN periods per ON (t.interval_id = per.id)

LEFT JOIN plans p ON (p.p_id = per.p_id)

LEFT JOIN main dv ON (dv.p_id = p.id )

LEFT JOIN users u ON (dv.uid = u.uid )

WHERE u.id= 'test' AND per.begin <= DATE_FORMAT( NOW(), '%H:%i:%S' ) AND per.end >= DATE_FORMAT( NOW(), '%H:%i:%S' )

order by per.day

limit 1

П.С. Мне советовали сделать несколько запросов в переменные,

Я думаю что нужно чтото типа

per.day IN (if per.day=0 then 0 else select per.day from periods per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day )

но это не работает так как такого синтаксиса нету )

Большое спасибо что дочитали до конца,) как мог детально так и описал

deadka · 17.05.2011 16:29:29

Попробуйте заменить

per.day IN (if per.day=0 then 0 else select per.day from periods per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day )

на

per.day IN (

if (per.day=0,0,select per.day from periods per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day )

)

Отредактированно deadka (17.05.2011 16:30:11)

moose · 18.05.2011 10:15:30

Я добавил Ваш вариант в запрос но он выдает ошибку, также я делаю пробный запрос только с вашим вариантом таже ошибка

select per.day from intervals as intv where

per.day IN (if (per.day=0,0,select per.day from periods per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ))

[3] ERROR_SQL /select per.day from periods as per where

per.day IN (if (per.day=0,0,select per.day from periods as per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = per.day )) / 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 'select per.day from periods as per where DATE_FORMAT( NOW() + INTERVAL 1 DAY' at line 2/

deadka · 18.05.2011 11:23:52

Приведите пожалуйста дампы таблиц intervals и periods (show create table ) и данные из таблиц).

moose · 18.05.2011 14:24:18

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

select per.day from periods as per where
per.day IN (if (per.day=0,0,select per.day from periods per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ))

[3] ERROR_SQL /select per.day from periods as per where
per.day IN (if (per.day=0,0,select per.day from periods as per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = per.day )) / 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 'select per.day from periods as per where DATE_FORMAT( NOW() + INTERVAL 1 DAY' at line 2/

 CREATE TABLE `periods` (

  `p_id` smallint(6) unsigned NOT NULL DEFAULT '0',

  `begin` time NOT NULL DEFAULT '00:00:00',

  `end` time NOT NULL DEFAULT '00:00:00',

  `tarif` varchar(7) NOT NULL DEFAULT '0',

  `day` tinyint(4) unsigned DEFAULT '0',

  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id`),

  UNIQUE KEY `id` (`id`),

  UNIQUE KEY `p_intervals` (`p_id`,`begin`,`day`)

) ENGINE=MyISAM AUTO_INCREMENT=196 DEFAULT CHARSET=cp1251

Данные из таблицы

SELECT per.*

FROM trafic t

LEFT JOIN periods per ON (t.interval_id = per.id)

LEFT JOIN plans p ON (p.tp_id = per.p_id)

LEFT JOIN dv_main dv ON (dv.tp_id = p.id )

LEFT JOIN users u ON (dv.uid = u.uid )

WHERE u.id= 'test2'

tp_id begin end tarif day id
100 00:00:00 24:00:00 0 3 191 #Вт
100 00:00:00 24:00:00 0 4 192 #Ср
100 00:00:00 24:00:00 0 5 195 #Чт

если включить в periods праздничные дни для plans который соотвествует данному пользователю , тогда появится еще одно поле

100 00:00:00 24:00:00 0 8 196 #праздничные

а если указать другой тарифный план plans для пользователя где ВСЕ дни и праздничные тогда будет такое
tp_id begin end tarif day id
100 00:00:00 24:00:00 0 0 197 #все дни
100 00:00:00 24:00:00 0 8 198 #праздничные

deadka · 18.05.2011 14:58:18

В такой форме запрос работает - некорректно алиасы стояли.

CREATE TABLE `t_4264_periods` (

  `p_id` smallint(6) unsigned NOT NULL DEFAULT '0',

  `begin` time NOT NULL DEFAULT '00:00:00',

  `end` time NOT NULL DEFAULT '00:00:00',

  `tarif` varchar(7) NOT NULL DEFAULT '0',

  `day` tinyint(4) unsigned DEFAULT '0',

  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id`),

  UNIQUE KEY `id` (`id`),

  UNIQUE KEY `p_intervals` (`p_id`,`begin`,`day`)

) ENGINE=MyISAM AUTO_INCREMENT=196 DEFAULT CHARSET=cp1251;

insert into `t_4264_periods` values(1,'12:00:00','20:00:00',2,3,1);

select intv.day from `t_4264_periods` intv where intv.day IN (select if (intv.day=0,0,(     select per.day from `t_4264_periods` per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day      ) ));

Отредактированно deadka (18.05.2011 14:58:51)

moose · 18.05.2011 19:03:07

Спасибо запрос работает но только для таких случаев
day=1..7 (тоесть любой день недели)
day=0 (все дни)

а если
day=0,8 (все +праздничные) и сегодня праздник, тогда не работает
или day=1,8 (Воскресенье +праздничные) и сегодня праздник, тогда не работает

Надо еще както учесть если в интервалах кроме первых двух случаев, есть еще day='8' тогда проверить таблицу праздников есть ли там сегодняшняя дата, если есть тогда выбрать скорость которая прописана для 8

таблица hollidays

CREATE TABLE `holidays` (

  `day` varchar(5) NOT NULL DEFAULT '',

  `descr` varchar(100) NOT NULL DEFAULT '',

  PRIMARY KEY (`day`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251

-----------
| day |
-----------
| 3-8 |
| 1-7 |

где
"3-8" -8 марта
"1-7" -Рождество

deadka · 18.05.2011 19:52:14

Итак, мы убедились, что конструкция if в запросе работает - при этом можно что в случае выполнения, что невыполнения условия в if выбирать что число, что результат запроса .

Далее никто не мешает несколько усложнить логику в if condition.

Например:

select if (intv.day=0 or intv.day=8,0,(     select per.day from `t_4264_periods` per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day      ) )

moose · 19.05.2011 00:45:34

Большое спасибо. Но есть два "но"
1. При

select if (intv.day=0 or intv.day=8,0,(     select per.day from `t_4264_periods` per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day      ) )

если в таблице
tp_id begin end tarif day id
100 00:00:00 24:00:00 0 4 192
100 00:00:00 24:00:00 0 3 191
100 00:00:00 24:00:00 0 5 195
100 00:00:00 24:00:00 0 8 196

то выбирает не 8 а сегодняшний день 4,

2. мне нужно если есть day='8' чтобы проверяло есть ли сегодняшняя дата в таблице holidays
если нету то выбирало день из остальных . Тоесть 8 имеет найбольшый приоритет, потом 0 и 1-7

Так же само если есть
tp_id begin end tarif day id
100 00:00:00 24:00:00 0 0 192
100 00:00:00 24:00:00 0 8 196

Нужно чтобы проверяло есть ли сегодняшняя дата в holidays
если нету то выбирало день ='0'

deadka · 19.05.2011 00:51:32

Ну так и составьте условие if нужным образом.

Что-то вроде

if ( (select count(*) from holidays where intv.day>=day and intv.day<=day)>0, intv.day, 0)

Отредактированно deadka (19.05.2011 00:52:01)

moose · 19.05.2011 12:11:02

Спасибо, почти разобрался, только еще вопросик
если в таблице
tp_id begin end tarif day id
100 00:00:00 24:00:00 0 4 192
100 00:00:00 24:00:00 0 3 191
100 00:00:00 24:00:00 0 5 195
100 00:00:00 24:00:00 0 8 196

и когда делаю так

intv.day IN (select case when intv.day=8 then 8 else ( 

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) end )

или по вашему так

select intv.day from `t_4264_periods` intv where intv.day IN (select if (intv.day=8,8,(select per.day from `t_4264_periods` per where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day      ) ));

в двух вариантах выводит

tp_id begin end tarif day id
100 00:00:00 24:00:00 0 5 195 #сегодняшний день "ответ НЕТ "
100 00:00:00 24:00:00 0 8 196 #праздик "ответ ДА"

а мне нужно чтобы только 8 выбрало
я конечно могу сделать сортировку по большому значению и лимит 1 но, почему оно показует два значения, Я так думаю что они два истиных так как intv.day=8 и intv.day=5 , но все же как сделать чтобы одно было

deadka · 19.05.2011 12:20:18

Если выборка даёт несколько рядов, а вам нужен только один, то да - limit годится для того, чтобы отсечь лишнее.

А почему нужно, чтобы именно строка с day=8 была выбрана? Чем строка с day = 5 не годится?

moose · 19.05.2011 13:46:53

пробовал разные варианты считаю что должен быть такой запрос :

ищет intv.day В ( если intv.day=8 тогда проверяет таблицу holidays если там есть нужная дата, тогда intv.day=8 если нету тогда подзапрос (если intv.day=0 тогда 0 если нет тогда поиск текущей даты) если intv.day не равняется 8 тогда также проверка intv.day=0 тогда 0 если нет тогда поиск текущей даты )

вот как оно выглядит:

select intv.day from intervals as intv where intv.day IN (select if (intv.day=8,( select if (select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

(select if (intv.day=0,0,( 

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))),

( 

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))

но он выдает ошибку и не могу найти где, уже все скобки проверил, не знаю где ошибка.
Мне все же кажется ошибка из-за того что несколько данных выдает intv.day в подзапросах (

[3] ERROR_SQL /select intv.day from intervals as intv where intv.day IN (select if (intv.day=8,( select if (select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

(select if (intv.day=0,0,(

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))),

(

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))

/ 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 'select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

' at line 1/

moose · 19.05.2011 14:19:14

даже не так

select intv.day from intervals as intv where intv.day IN (select if (intv.day=8,( select if (select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

(select if (intv.day=0,0,(

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))),

(

select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))

а так

select intv.day from intervals as intv where intv.day IN 

(select if (intv.day=8, 

(select if 

(select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

(select if (intv.day=0,0,

(select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))),

(select if (intv.day=0,0,

(select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w' ) = intv.day ) ))))

Но все равно таже ошибка (

/ 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 'select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,
' at line 4/

Отредактированно moose (19.05.2011 14:19:56)

deadka · 19.05.2011 15:37:09

Таблицу intervals Вы ранне не упоминали.

Приведите
show create table intervals;
show create table holidays;

И дампы этих таблиц плиз в виде insert into intervals... и insert into holidays.
Чтоб полностью воссоздать .

Что еще - проследите за тем, чтобы запросы, который в if возвращали одну строку, у Вас это не очевидно в нескольких подзапросах.

(select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,
Тут точно = day?
может быть = intv.day?

moose · 19.05.2011 16:23:21

deadka написал:
Таблицу intervals Вы ранне не упоминали.

Это тоже самое что и periods я просто так ее назвал

(select count(*) from holidays where DATE_FORMAT( NOW(), '%c-%e' ) = day)>0,
Выводит все верно

Дамп

CREATE TABLE `holidays` (

  `day` varchar(5) NOT NULL DEFAULT '',

  `descr` varchar(100) NOT NULL DEFAULT '',

  PRIMARY KEY (`day`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251

INSERT INTO `holidays` VALUES ('5-18',''),('5-19','');

CREATE TABLE `intervals` (

  `tp_id` smallint(6) unsigned NOT NULL DEFAULT '0',

  `begin` time NOT NULL DEFAULT '00:00:00',

  `end` time NOT NULL DEFAULT '00:00:00',

  `tarif` varchar(7) NOT NULL DEFAULT '0',

  `day` tinyint(4) unsigned DEFAULT '0',

  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id`),

  UNIQUE KEY `id` (`id`),

  UNIQUE KEY `tp_intervals` (`tp_id`,`begin`,`day`)

) ENGINE=MyISAM AUTO_INCREMENT=197 DEFAULT CHARSET=cp1251;

intervals большая таблица так как там для каждого тарифа свои интервалы показую часть и в четырех экземлярах для четырех случаев

1. Вариант : дни + праздник

INSERT INTO `intervals` VALUES  (100,'00:00:00','24:00:00','0',3,191),(100,'00:00:00','24:00:00','0',4,192),(100,'00:00:00','24:00:00','0',5,195),(100,'00:00:00','24:00:00','0',8,196);

2. Вариант : дни

INSERT INTO `intervals` VALUES  (100,'00:00:00','24:00:00','0',3,191),(100,'00:00:00','24:00:00','0',4,192),(100,'00:00:00','24:00:00','0',5,195);

3. Вариант : Все дни

INSERT INTO `intervals` VALUES  (101,'00:00:00','24:00:00','0',0,194);

 

2. Вариант : Все дни + праздник

INSERT INTO `intervals` VALUES  (101,'00:00:00','24:00:00','0',0,194),(100,'00:00:00','24:00:00','0',8,196);

deadka · 19.05.2011 21:05:11

Нда, запросец, конечно, получился такой, что без поллитры, в крайнем случае без форматирования табами не разберешься.

Ваш запрос не компилируется потому, что в нем со скобками непорядок.

Так компилируется.

select intv.day from intervals as intv where intv.day IN

(

select 

    if (

        intv.day=8,

        (select 

            if (

                (select    count(*) from    holidays where     DATE_FORMAT( NOW(), '%c-%e' ) = intv.day)>0, 

                8,

                (select 

                    if

                    (

                        intv.day=0,

                        0,

                        (select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w') = intv.day )

                    )

                )

            )

        ),

        (select 

            if

            (

                intv.day=0,

                0,

                (select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w') = intv.day )

            )

        )

    )

);

На всякий случай прикладываю запрос, отформатированный табами, он хоть как-то читается.

Ну и опять же, проследите за тем, чтобы подзапросы, которые должны возвращать скаляр не возвращали колонку. С помощью того же liimt 1.

Отредактированно deadka (19.05.2011 21:16:33)

Прикрепленные файлы:

query.sql, Размер: 580 байт, Скачано: 760

moose · 20.05.2011 14:54:29

Большое СПАСИБО deadka! Вы настоящий друг! )

конечный запрос имеет вид

SELECT t.in_speed, t.out_speed, intv.day

FROM trafic t

LEFT JOIN periods per ON (t.interval_id = per.id)

LEFT JOIN plans p ON (p.p_id = per.t_id)

LEFT JOIN main dv ON (dv.p_id = p.id )

LEFT JOIN users u ON (dv.uid = u.uid )

WHERE u.id= 'test' AND per.begin <= DATE_FORMAT( NOW(), '%H:%i:%S' ) AND per.end >= DATE_FORMAT( NOW(), '%H:%i:%S' )

and intv.day IN 

(select if ( intv.day=8, 

(select if ((select    count(*) from    holidays where     DATE_FORMAT( NOW(), '%c-%e' ) = day)>0, 8,

(select if (intv.day=0, 0, 

(select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w') = intv.day ))))),

(select if (intv.day=0, 0,

(select intv.day from intervals as intv where DATE_FORMAT( NOW() + INTERVAL 1 DAY, '%w') = intv.day )))))

order by intv.day desc

limit 1

Как вы считает можно его еще оптимизровать?
может создать несколько функций ?

deadka · 20.05.2011 15:18:49

Пожалуйста . Касательно оптимизации - если наличествуют индексы на тех полях, по которым производится join, то вроде и так ничего навскидку не назову.. И не забудьте в коде поставить к нему подробный комментарий .

Отредактированно deadka (20.05.2011 15:19:00)

moose · 20.05.2011 16:56:33

Спасибо, коментарий - обязательно))

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

#1 17.05.2011 12:22:59

Помогите дописать запрос

#2 17.05.2011 16:29:29

Re: Помогите дописать запрос

#3 18.05.2011 10:15:30

Re: Помогите дописать запрос

#4 18.05.2011 11:23:52

Re: Помогите дописать запрос

#5 18.05.2011 14:24:18

Re: Помогите дописать запрос

#6 18.05.2011 14:58:18

Re: Помогите дописать запрос

#7 18.05.2011 19:03:07

Re: Помогите дописать запрос

#8 18.05.2011 19:52:14

Re: Помогите дописать запрос

#9 19.05.2011 00:45:34

Re: Помогите дописать запрос

#10 19.05.2011 00:51:32

Re: Помогите дописать запрос

#11 19.05.2011 12:11:02

Re: Помогите дописать запрос

#12 19.05.2011 12:20:18

Re: Помогите дописать запрос

#13 19.05.2011 13:46:53

Re: Помогите дописать запрос

#14 19.05.2011 14:19:14

Re: Помогите дописать запрос

#15 19.05.2011 15:37:09

Re: Помогите дописать запрос

#16 19.05.2011 16:23:21

Re: Помогите дописать запрос

deadka написал:

#17 19.05.2011 21:05:11

Re: Помогите дописать запрос

#18 20.05.2011 14:54:29

Re: Помогите дописать запрос

#19 20.05.2011 15:18:49

Re: Помогите дописать запрос

#20 20.05.2011 16:56:33

Re: Помогите дописать запрос

Board footer