如何选择最高总值的对象组

Question

我有三张表格：

对象（numId is primary键）

object_category	价格	1
永久	20	2
借用	30	3
借用	10	（

primary键）

is em
colname
numId

借用

（numobj是 emere键/主键参考对象表， collection_name is em efiral键 rector collection表）

numobj	collection_name
1	collection_alpha
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"
);

它返回消息：“语法错误”

Answer 1

“哪些集合[...]的总成本最高？”

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

为您列出所有按借入的对象的总体价值下降的收集列表。您可以添加限制1以显示列表的顶部项目。

但是，想象一下，有两个收藏恰好具有相同的总价值。他们将 都具有“借入对象的最高总成本” ，因此应退还两者。上面具有限制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

或者，我们可以设置 cte 并表达了类似的东西：

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