Повторно использовать вычисляемое поле в том же запросе
У меня есть этот запрос.
SELECT carte.nome, sum(amount) AS total, (1500-sum(amount)) AS residuo
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id
WHERE data BETWEEN '2019-05-01' AND '2019-05-31'
GROUP by banca
Цель этого запроса — суммировать некоторые суммы (сумма(сумма)) за период времени. Я также установил нижний предел этой суммы на уровне 1500, поэтому я хочу посчитать в третьем поле. я пытался сделать
SELECT carte.nome, sum(amount) AS total, (1500-total) AS residuo
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id
WHERE data BETWEEN '2019-05-01' AND '2019-05-31'
GROUP by banca
но mysql жалуется, что totale не является известным полем (это производное). Первый запрос работает, но неэффективен. Чего мне не хватает, чтобы второй работал?
@scaisEdge я этого не знал
Ответы 4
Mysql не поддерживает использование переменных, если только они не находятся внутри оператора HAVING, поэтому, чтобы это работало, вы должны переписать его как
SELECT carte.nome, sum(amount) AS total, (1500-sum(amount)) AS residuo
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id
WHERE data BETWEEN '2019-05-01' AND '2019-05-31'
GROUP by banca
Это то, чем я сейчас занимаюсь
так что да, это в основном единственный способ сделать это, это звучит неэффективно в смысле программирования, но это способ сделать это в mysql, и он не будет пересчитывать его на самом деле дважды, поскольку у него есть некоторое внутреннее хранилище переменных, но вы просто не можете используй их.
О да, так что вы делаете :( Моя ошибка
Вы можете использовать Common Table Expression (CTE):
WITH cte_query AS (
SELECT carte.nome AS nome, sum(amount) AS total
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id
WHERE data BETWEEN '2019-05-01' AND '2019-05-31'
GROUP by banca
)
SELECT nome, total, (1500- total) AS residuo
FROM cte_query
Подзапрос также будет работать:
SELECT nome, total, (1500- total) AS residuo
FROM (
SELECT carte.nome AS nome, sum(amount) AS total
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id
WHERE data BETWEEN '2019-05-01' AND '2019-05-31'
GROUP by banca
) A
Я предпочитаю делать это в одном запросе. Но спасибо за подсказку CTE
Вы можете исправить это как
SELECT
nome ,
total,
residuo AS residuo_TMP,
(1500-total) AS residuo
FROM
(SELECT
carte.nome,
SUM(amount) AS total,
(1500- SUM(amount)) AS residuo
FROM
movimenti_carta
JOIN carte
ON movimenti_carta.banca = carte.id
WHERE DATA BETWEEN '2019-05-01'
AND '2019-05-31'
GROUP BY banca ) aa
Мой первый запрос работает и является более простым подходом по сравнению с подзапросом
ооо классно так держать //
Псевдонимы столбцов нельзя повторно использовать в SELECT, где они определены — и по простой причине. MySQL (в частности) и SQL в целом не гарантируют порядок вычисления выражений в SELECT.
В вашем случае самое простое решение — повторить выражение, потому что оно такое простое.
Однако у вас есть еще одна проблема в вашем запросе. Вы собираете по banca, но выбираете только nome.
Вот лучший способ написать запрос:
SELECT c.nome, sum(?.amount) AS total,
(1500 - sum(?.amount)) AS residuo
FROM movimenti_carta mc JOIN
carte c
ON mc.banca = c.id
WHERE ?.data >= '2019-05-01' AND
?.data < '2019-06-01'
GROUP by c.nome;
Обратите внимание на изменения:
- Все ссылки на столбцы должны быть уточнены.
?для псевдонима таблицы, из которой берется столбец. - Используйте псевдонимы таблиц, которые являются сокращениями имен таблиц.
- Неагрегированные столбцы в
SELECTнаходятся вGROUP BY. - Арифметика дат работает как для дат, так и для значений даты/времени.
«SQL в целом не гарантирует порядок оценки выражений в SELECT». - но если стандарт SQL определил это, тогда мы мог повторно используем выражения столбцов в предложениях SELECT того же уровня и избегаем (текущего) чрезмерного уровня повторяемости и многословия в подобных запросах - так почему бы и нет? Это было бы немедленным и огромным улучшением эргономики для всех.
что вы имеете ввиду под "неэффективно"..??? использование двух или более значений времени для одной и той же функции агрегирования (на основе одного и того же значения) не снижает производительность.