Как выбрать группу объектов с наибольшей общей стоимостью
У меня есть три таблицы, как это:
Объекты (NumId равен Основной ключ)
| NumId | Категория_объекта | Цена |
|---|---|---|
| 1 | постоянный | 20 |
| 2 | заимствованный | 30 |
| 3 | заимствованный | 10 |
Коллекция (ColName — Основной ключ)
| ColName |
|---|
| коллекция_альфа |
| collection_betha |
заимствовано
(NumObj — это Внешний ключ/первичный ключ, ссылающийся на таблицу объектов, а Collection_name — это Внешний ключ, ссылающийся на таблицу Collection)
| NumObj | Collection_name |
|---|---|
| 1 | коллекция_альфа |
| 2 | collection_betha |
| 3 | коллекция бета |
Мне нужно сделать запрос: «Какие коллекции (за исключением постоянных) имеют самую высокую общую стоимость заимствованных объектов?»
моя текущая попытка (это не работает):
SELECT ColName FROM Colecao
WHERE (
SELECT MAX((SUM(Price)) FROM Objects AS Num
JOIN Borrowed ON NumObj = Objects.NumId
JOIN Collection ON ColName = Collection_name
WHERE Num > COUNT(NumId) FROM Objects
WHERE Object_category = "permanent"
);
Он возвращает сообщение: «синтаксическая ошибка»
Ответы 1
"Which collections [...] have the highest total cost of borrowed objects?"
select
b.Collection_name,
sum(o.price) Worth
from
Objects o
inner join Borrowed b on b.NumObj = o.NumId
where
o.Object_category = 'borrowed'
group by
b.Collection_name
order by
sum(o.price) desc
дает вам список всех коллекций, упорядоченных по убыванию общей стоимости заимствованных объектов. Вы можете добавить limit 1, чтобы показать верхний элемент списка.
Но представьте, что есть две коллекции, которые имеют одинаковую общую стоимость. Они будут оба иметь «наибольшая общая стоимость заемных объектов», поэтому оба должны быть возвращены. Приведенный выше запрос с limit 1 пропустит один из них.
В этом случае мы могли бы классифицировать коллекций в соответствии с их ценностью и вернуть все строки с рангом = 1.
select
*
from
(
select
*,
rank() over (order by Worth desc) rnk
from
(
select
b.Collection_name,
sum(o.price) Worth
from
Objects o
inner join Borrowed b on b.NumObj = o.NumId
where
o.Object_category = 'borrowed'
group by
b.Collection_name
) collections
) collections_ranked
where
rnk = 1
order by
Collection_name
В качестве альтернативы мы могли бы настроить КТР и выразить то же самое следующим образом:
with
collections as (
select
b.Collection_name,
sum(o.price) Worth
from
Objects o
inner join Borrowed b on b.NumObj = o.NumId
where
o.Object_category = 'borrowed'
group by
b.Collection_name
)
select
Collection_name,
Worth
from
collections
where
Worth = (select max(worth) from collections)
order by
Collection_name