Я создал SQL-запрос, который возвращает мне элементы из таблицы фильмов, удовлетворяющие одному из двух условий. Это либо самые прибыльные (брутто-бюджетные), либо самые дешевые фильмы.
Однако я хотел добавить в возвращаемый запрос столбец, в котором говорилось «наиболее выгодно» или «наименее дорого» в зависимости от того, какое из условий удовлетворяет кортеж. Я попытался найти решение в Интернете; и я решил попробовать использовать CASE WHEN в части SELECT запроса.
Исходный sql-запрос:
SELECT DISTINCT Films.title, Films.year
FROM Films,
(
SELECT DISTINCT (MAX(Films.gross-Films.budget))AS profit FROM Films
) AS Temp1
WHERE
(
(Films.gross-Films.budget)=Temp1.profit)
OR (Films.budget)=(SELECT DISTINCT Min(Films.budget) FROM Films)
)
СЛУЧАЙ, КОГДА попытка:
SELECT DISTINCT
CASE WHEN Temp1.profit=Max(Temp1.profit) THEN 'most profitable' ELSE 'least expensive' END AS feature,
Films.title,
Films.year
FROM Films,
(
SELECT DISTINCT (MAX(Films.gross-Films.budget))AS profit FROM Films
) AS Temp1
WHERE
(
(Films.gross-Films.budget)=Temp1.profit)
OR (Films.budget)=(SELECT DISTINCT Min(Films.budget) FROM Films)
)
Однако это дало всевозможные ошибки, такие как: столбец «temp1.profit» должен присутствовать в предложении GROUP BY или использоваться в агрегатной функции.
Я не уверен, почему он спрашивает об этом, но я, тем не менее, попытался дать ему то, что он хотел, добавив строку
GROUP BY TEMP1.profit,Films.title,Films.year;
Это заставило ошибку исчезнуть, но теперь все строки получают одно и то же «наиболее прибыльное» значение в столбце функций, даже если они там не для этого!
Я не знаю, есть ли способ заставить это работать, но даже совершенно другой способ заставить эту колонку работать был бы большим подспорьем.
distinct
во внутреннем запросе ("temp1") бесполезен, так как этот запрос никогда не вернет более одной строки.
Вы не можете напрямую повторно использовать вычисление из предложения WHERE в предложении SELECT, но вы можете получить этот эффект, используя WITH запросы или переместив предложение WHERE во внешний выбор. В вашем случае я думаю, что объединение обеих стратегий дает самый чистый результат:
WITH max_profit AS (SELECT MAX(gross-budget) FROM Films),
min_cost AS (SELECT MIN(budget) FROM Films)
SELECT title, year, most_profitable, least_expensive FROM
(SELECT
Films.title, Films.year,
(Films.gross-Films.budget = (SELECT * FROM max_profit)) AS most_profitable,
(Films.budget = (SELECT * FROM min_cost)) AS least_expensive
FROM Films
)
WHERE most_profitable OR least_expensive;
Вывод здесь немного отличается от того, что вы пытались получить: он должен иметь столбцы с именами most_profitable и наименее_дорого, для которых установлено значение true или false, причем хотя бы один из них всегда имеет значение true. Мне кажется, это лучше, потому что, возможно, один и тот же фильм может быть и самым дешевым, и самым прибыльным, но вы можете поместить оператор case обратно во внешнее предложение SELECT, если хотите:
WITH max_profit AS (SELECT MAX(gross-budget) FROM Films),
min_cost AS (SELECT MIN(budget) FROM Films)
SELECT title, year, (CASE WHEN most_profitable THEN 'most profitable' ELSE 'least expensive' END) FROM
(SELECT
Films.title, Films.year,
(Films.gross-Films.budget = (SELECT * FROM max_profit)) AS most_profitable,
(Films.budget = (SELECT * FROM min_cost)) AS least_expensive
FROM Films
)
WHERE most_profitable OR least_expensive;
Это здорово, но я бы очень хотел научиться помещать эту информацию в один столбец. Это может быть потому, что я новичок в sql, но я не понимаю, как использовать внешнее ГДЕ для этого. не мог бы ты показать мне?
Отредактировано, чтобы сделать его более понятным, надеюсь, что это поможет.
Пришлось добавить псевдоним AS1 после подзапроса, чтобы обойти ошибку «Подзапрос в FROM должен иметь псевдоним». Кроме этого, это как раз то, что я искал. Спасибо!
Для начала: вам не нужны эти многочисленные подзапросы к таблице фильмов, чтобы определить наиболее прибыльные и наименее дорогие элементы! Вы можете просто использовать оконные функции. Вот запрос, который ранжирует все фильмы по (уменьшению) прибыли и (возрастанию) бюджета:
select f.title, f.year,
row_number() (order by gross - budget desc) rn_profit,
row_number() (order by budget) rn_budget
from films f
Затем мы можем превратить его в подзапрос и отфильтровать фильмы, которые нам нужны:
select *
from (
select f.title, f.year,
row_number() (order by gross - budget desc) rn_profit,
row_number() (order by budget) rn_budget
from films f
) f
where 1 in (rn_profit, rn_budget)
Это дает вам две строки (или только одну, если один фильм возглавляет обе категории); строка, в которой столбец rn_profit
имеет значение 1
, указывает на самый прибыльный фильм, а значение 1
в rn_budget
указывает на менее дорогой. Вы можете интерпретировать эту информацию в выражении case
в предложении select по своему усмотрению.
Я бы, вероятно, использовал dense_rank()
, чтобы включить фильмы с одинаковой прибылью или бюджетом (хотя не уверен, насколько это вероятно)
Другой вариант — использовать ограничивающее предложение после сортировки по прибыли или багдету:
(
select *, 'most profitable'
from films
order by gross - budget desc
fetch first 1 rows with ties
)
union all
(
select *, 'least expensive'
from films
order by budget
fetch first 1 rows with ties
)
Обычно нет необходимости заключать запросы объединения в круглые скобки, но в этом случае это необходимо, потому что мы должны применить ORDER BY и ограничить каждый из них в отдельности. союз всех
Посмотрите на CTE.