|
Задавайте вопросы, мы ответим
Вы не зашли.
Доброго времени суток.
Есть у меня такой запрос
SELECT t2.id_zaim,t2.start_data,t2.summa_zaima,t3.id_zaim,t3.ostatok_baza,t3.data_time
FROM zaim AS t2
LEFT JOIN vznos as t3 ON t2.id_zaim = t3.id_zaim AND t3.data_time=t2.start_data
WHERE t2.start_data BETWEEN "2012-05-01" AND "2012-05-15"
AND t2.n_point="1" AND (t2.real_end_data > "2012-05-15" OR t2.real_end_data = "0000-00-00")
GROUP BY t2.id_zaim
Но очень странная вещь. LEFT JOIN присоединение отображает пустые поля.
Хотя если прямо прописать
LEFT JOIN vznos as t3 ON t2.id_zaim = t3.id_zaim AND t3.data_time="2012-05-15"
все начинает правильно показывать.
Тип полей t2.start_data и t3.data_time - DATE
Почему так происходи???
Отредактированно xenar (13.11.2012 19:47:09)
Неактивен
Вы делаете группировку, а поля выбираете без группирующей функции.
Неактивен
vasya написал:
Вы делаете группировку, а поля выбираете без группирующей функции.
Мозг уже поломал, бьюсь уже не один день. Если не сложно можно точно показать где я ошибся. Я не понял если честно (((
Просто в Join некуда да и вроде бы незачем груп. функцию а куда еще.....
Отредактированно xenar (13.11.2012 22:19:48)
Неактивен
При использовании группировки в части перечисления полей (после ключевого слова select) могут быть только те колонки по которым производится группировка или они используются с группирующей ф-ей (min,sum,count и т.д.)
У вас же t2.start_data,t2.summa_zaima,t3.id_zaim,t3.ostatok_baza,t3.data_time выбираются без группирующей функции. Так нельзя.
В качестве решения искать только t2.id_zaim, а потом по нему выбирать остальные поля.
Неактивен
Давайте я объясню что я хочу в конце концов добится может все можно решить проще....
То что я пытаюсь сделать является подзапросом в таком запросе:
SELECT o.*,
(o.ostatok+o.vneseno-o.zabrano-(SELECT COALESCE(SUM(summa_zaima),0) from zaim WHERE zaim.start_data=o.date AND zaim.n_point=o.point)+(SELECT COALESCE(SUM(summa_vznosa),0) FROM vznos WHERE vznos.data_time = o.date AND vznos.n_point=o.point)) AS kassa,
(SELECT COUNT(*) FROM zaim za WHERE (SELECT COUNT(*) FROM zaim z1 WHERE z1.id_clienta=za.id_clienta) = "1" AND za.start_data = o.date AND za.n_point=o.point) AS new_client
............
FROM otchet o
WHERE o.point = "1" AND o.date LIKE "2012-09-%" ORDER BY o.date
Данным позапросом мне неoбходимо вывести суммирующее значение из двух таблиц которое я сделал вот таким корявым подзапросом
(SELECT SUM( if(t2.ostatok_baza<t1.summa_zaima,t2.ostatok_baza,t1.summa_zaima))
FROM (SELECT id_zaim,summa_zaima FROM zaim WHERE start_data <= o.date AND n_point=o.point AND (real_end_data > o.date OR real_end_data = "0000-00-00")) as t1
LEFT JOIN (SELECT zaim.id_zaim, MIN(vznos.ostatok_baza) AS ostatok_baza FROM vznos,zaim WHERE vznos.data_time <= o.date AND vznos.ostatok_baza <> "0" AND vznos.id_zaim=zaim.id_zaim AND (zaim.real_end_data >= o.date OR zaim.real_end_data = "0000-00-00") GROUP BY vznos.id_zaim) as t2 on t1.id_zaim = t2.id_zaim) as portvel
Но я столкнулся с тем что не могу передать в коррелирующий подзапрос значение o.date и o.point
Получаю ошибку: Unknown column 'o.date' in 'where clause'
Видимо тут срабатывает принцип "вассала"
Хотя может быть я ошибаюсь в этом и все таки можно передать значение ну скажем с помощью переменной?? Не до красоты кода, просто уже фактически неделю ничего не могу сделать .......
Отредактированно xenar (14.11.2012 07:46:43)
Неактивен
Давайте вы приведете структуру таблиц (в виде create table ...), а также набор тестовых данных (в виде insert into) из десятка строк. И объясните словами суть задачи (и какой результат на этих данных должен быть получен и почему), а то глядя на куски запросов сложно что-либо посоветовать.
Неактивен
Необходимо получить сумму которая будет складываться из zaim.summa_zaima за период времени фильтруемый по следующим принципам.
В таблице zaim.id_zaim поле уникально, а vznos.id_zaim может быть несколько
Если zaim.id_zaim=vznos.id_zaim -->> вместо значения из zaim.summa_zaima брать значение из vznos.ostatok_baza причем брать его по принципу что нужная запись должна быть самой последней по полю vznos.data_time к дате из переданной внешним запросом
Если zaim.id_zaim<>vznos.id_zaim -->>> то такая замена не проводится и берется стандартное значение и zaim.id_zaim
Ну и на основную таблица zaim накладывается выборка по началу временного диапазона (2012-05-01) по конкретную дату (2012-02-15) и в результате мы должный получить сумму из zaim.summa_zaima с учетом вышеуказанного.
Вообщем то все мною изложенное я уже делал в двух различных вариантов, но дело осложняется тем что этот запрос является подзапросом и значение даты на него должен передаваться с основного запроса который предназначен для построения отчета за месяц.
DDL прикладываю
3.sql
P.S Очень корявое конечно объяснение но как смог.
Отредактированно xenar (14.11.2012 14:56:32)
Неактивен
xenar написал:
Необходимо получить сумму которая будет складываться из zaim.summa_zaima за период времени фильтруемый по следующим принципам.
В таблице zaim.id_zaim поле уникально, а vznos.id_zaim может быть несколько
Если zaim.id_zaim=vznos.id_zaim -->> вместо значения из zaim.summa_zaima брать значение из vznos.ostatok_baza причем брать его по принципу что нужная запись должна быть самой последней по полю vznos.data_time к дате из переданной внешним запросом
Если zaim.id_zaim<>vznos.id_zaim -->>> то такая замена не проводится и берется стандартное значение и zaim.id_zaim
Таблица тест это внешний запрос из которого будет передана дата.
Неактивен
Огромное спасибо, хотя не совсем получается тот результат что я хотел, видимо херово объяснил )), но очень близко к цели и уже смогу сам рашпилем доработать.
>>Кстати, относительно ошибки из первого сообщения см FAQ №16
Класс, теперь все понятно !
Отредактированно xenar (15.11.2012 10:27:21)
Неактивен
Рано радовался (
Есть еще одна проблема. В сформированном результата отсутствуют записи при условии когда в таблице vznos нет записей за указанный период (скажем они более позднего периода) времени с v.id_zaim = z.id_zaim
Хотя насколько я понимаю условие or v.data_time is null должно было как раз решать эту проблему....
Отредактированно xenar (15.11.2012 13:35:48)
Неактивен
xenar написал:
Рано радовался (
Есть еще одна проблема. В сформированном результата отсутствуют записи при условии когда в таблице vznos нет записей за указанный период (скажем они более позднего периода) времени с v.id_zaim = z.id_zaim
Хотя насколько я понимаю условие or v.data_time is null должно было как раз решать эту проблему....
Понял, тогда ограничение на дату нужно ставить в другом месте.
Неактивен
Предыдущий запрос был с ошибкой 
Давайте по порядку.
Объединяем таблицы zaim, vznos с ограничением на vznos.data_time
Неактивен
1) Добавил фильтр по дате (выделил жирным) так как иначе выдавали все записи по таблице zaim
2) С включенным внешним запросом считает итоговую сумму почему то не правильно.
SELECT o.*,
@a:=o.date, (select SUM(ifnull(v.ostatok_baza,z.summa_zaima))
from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim
where v.data_time =
(select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1)
or v.data_time is null AND z.start_data <= @a) as `сумма` FROM otchet1 o
WHERE o.point = "1" AND o.date LIKE "2012-05-%" ORDER BY o.date
Даже если в переменную ставлю конкретную дату
SELECT o.*,
@a:="2012-05-21"
Все равно итоговую цифру выдает на порядок выше.
______________________
P/S Проверил запрос с EXPLAIN и получил во второй строке
2 UNCACHEABLE SUBQUERY system 0 const row not found
Кажется так не должно быть?
Отредактированно xenar (15.11.2012 23:44:47)
Неактивен
xenar написал:
1) Добавил фильтр по дате (выделил жирным) так как иначе выдавали все записи по таблице zaim
Забыли скобки поставить. AND имеет приоритет перед OR
Неактивен
Создал для эксперимента такую же внешнюю таблицу как вы указали, но все осталось без изменений
Цифры не правильные.
mysql> select t.*, @a:=t.dt, (select sum(ifnull(v.ostatok_baza,z.summa_zaima))
-> from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim
-> where z.start_data <= @a and (v.data_time =
-> (select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1)
-> or v.data_time is null)) as `сумма` from test t where t.dt like "2012-05-%" order by t.dt;
+------------+------------+------------+
| dt | @a:=t.dt | сумма |
+------------+------------+------------+
| 2012-05-15 | 2012-05-15 | 208500.00 |
| 2012-05-21 | 2012-05-21 | 292500.00 |
+------------+------------+------------+
2 rows in set, 1 warning (0.02 sec)
С EXPLAIN
mysql> explain select t.*, @a:=t.dt, (select sum(ifnull(v.ostatok_baza,z.summa_zaima))
-> from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim
-> where z.start_data <= @a and (v.data_time =
-> (select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1)
-> or v.data_time is null)) as `сумма` from test t where t.dt like "2012-05-%" order by t.dt\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t
type: index
possible_keys: dt-date
key: dt-date
key_len: 4
ref: NULL
rows: 2
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: UNCACHEABLE SUBQUERY
table: <derived3>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
*************************** 3. row ***************************
id: 2
select_type: UNCACHEABLE SUBQUERY
table: z
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1599
Extra: Using where
*************************** 4. row ***************************
id: 4
select_type: DEPENDENT SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
*************************** 5. row ***************************
id: 5
select_type: DERIVED
table: vznos
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2275
Extra: Using where
*************************** 6. row ***************************
id: 3
select_type: DERIVED
table: vznos
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2275
Extra: Using where
6 rows in set, 1 warning (0.01 sec)
MySQL версии 5.1.63
PS Попробовал без внешне запроса и сразу же стало считать все правильно
mysql> select sum(ifnull(v.ostatok_baza,z.summa_zaima)) '2012-05-21'
-> from zaim z left join (select * from vznos where data_time <= "2012-05-21") v on z.id_zaim =v.id_zaim
-> where z.start_data <= "2012-05-21" and (v.data_time =
-> (select v1.data_time from (select * from vznos where data_time <= "2012-05-21") v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1)
-> or v.data_time is null)\G
*************************** 1. row ***************************
2012-05-21: 207860.00
1 row in set (0.02 sec)
mysql> explain
-> select sum(ifnull(v.ostatok_baza,z.summa_zaima)) '2012-05-21'
-> from zaim z left join (select * from vznos where data_time <= "2012-05-21") v on z.id_zaim =v.id_zaim
-> where z.start_data <= "2012-05-21" and (v.data_time =
-> (select v1.data_time from (select * from vznos where data_time <= "2012-05-21") v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1)
-> or v.data_time is null) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: z
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1600
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 36
Extra: Using where
*************************** 3. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: <derived4>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 36
Extra: Using where; Using filesort
*************************** 4. row ***************************
id: 4
select_type: DERIVED
table: vznos
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2277
Extra: Using where
*************************** 5. row ***************************
id: 2
select_type: DERIVED
table: vznos
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2277
Extra: Using where
5 rows in set (0.01 sec)
мистика какая то .....
Отредактированно xenar (16.11.2012 09:06:46)
Неактивен
А что это за warning возникает?
После выполнения запроса сделайте
show warnings;
Неактивен
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2012-05-%' for column 'dt' at row 2284 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
Неактивен
Последняя стабильная версия для ветки 5.1 это 5.1.66
Думаю стоит обновиться на неё.
Неактивен
Выявил такую особенность.
Если сначала делать запрос как
mysql> select t.*, @a:=t.dt, (select sum(ifnull(v.ostatok_baza,z.summa_zaima))from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim where z.start_data <= @a and (v.data_time = (select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1) or v.data_time is null)) as `сумма` from test t where t.dt like "2012-05-15" order by t.dt\G
*************************** 1. row ***************************
dt: 2012-05-15
@a:=t.dt: 2012-05-15
сумма: 169680.00
1 row in set (0.01 sec)
а потом сразу же:
mysql> select t.*, @a:=t.dt, (select sum(ifnull(v.ostatok_baza,z.summa_zaima))from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim where z.start_data <= @a and (v.data_time = (select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1) or v.data_time is null)) as `сумма` from test t where t.dt like "2012-05-%" order by t.dt\G
*************************** 1. row ***************************
dt: 2012-05-15
@a:=t.dt: 2012-05-15
сумма: 169680.00
*************************** 2. row ***************************
dt: 2012-05-21
@a:=t.dt: 2012-05-21
сумма: 253680.00
2 rows in set, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2012-05-%' for column 'dt' at row 2287 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
То как видно сумма за 2012-05-15 правильная (за 2012-05-15 должно быть 169680.00 но на 2012-05-21 уже не правильно. (должно быть 207860.00)
Что то не так с переменными??
ЗЫ А если делать запрос из браузерной оболочки (webmin) то итоговые суммы вообще на порядок выше
Вывод из команды SQL select t.*, @a:=t.dt, (select sum(ifnull(v.ostatok_baza,z.summa_zaima))from zaim z left join (select * from vznos where data_time <= @a) v on z.id_zaim =v.id_zaim where z.start_data <= @a and (v.data_time = (select v1.data_time from (select * from vznos where data_time <= @a) v1 where v1.id_zaim=v.id_zaim order by data_time desc limit 1) or v.data_time is null)) as `сумма` from test t where t.dt like "2012-05-%" order by t.dt ..
dt @a:=t.dt сумма
____________________________________________
2012-05-15 2012-05-15 208500.00
2012-05-21 2012-05-21 292500.00
Отредактированно xenar (16.11.2012 11:51:24)
Неактивен
Выявлять особенности работы с переменными лучше все-таки на стабильной версии.
Если такое поведение будет в 5.1.66, то тогда можно будет писать в баг репорт, но подозреваю, что там всё будет правильно.
Неактивен
Проверил на версии 5.5.28
Все тоже самое. Если делать запрос с t.dt like "2012-05-%" считает не правильно
а если с t.dt like "2012-05-21" то по отдельности считает правильно.
Как быть?
Неактивен
Писать в баг репорт. Или переходить на марию.
У меня на 5.3.5-MariaDB считает правильно.
Или переписать запрос разбив его на несколько частей. Например, сначала выполняете внешний и в цикле для каждой строки внешнего запроса по точному значению dt ищите.
Неактивен
vasya написал:
Писать в баг репорт. Или переходить на марию.
У меня на 5.3.5-MariaDB считает правильно.
Это слишком радикально будет
Или переписать запрос разбив его на несколько частей. Например, сначала выполняете внешний и в цикле для каждой строки внешнего запроса по точному значению dt ищите.
Черт, даже представить не могу как это сделать. ((
Отредактированно xenar (19.11.2012 13:18:48)
Неактивен
Например, во внешнем приложении (php скрипт), тогда вообще никакие переменные в запросе не понадобятся. Или через хранимую процедуру, если хотите решить только с помощью mysql.
Неактивен
