blocks|key|2095214|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2095215|select+c1.pid+
from+Cata+c1
where+not+exists+(
++++select+c2.pid+
++++from+Cata+c2+
++++where+c2.cost+>+c1.f.cost
);|code-block|syntax|javascript|2095216|如果您想知道查询有什么问题，请注意内部SELECT正在返回0行，因为您正在将项目的成本与其自身进行比较，因此c.cost总是等于f.cost，因此<比较失败，因此内部select返回0行，因此所有行的"not+in“条件都是正确的|2095217|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this one:

<pre><code>select c1.pid 
from Cata c1
where not exists (
 select c2.pid 
 from Cata c2 
 where c2.cost &gt; c1.f.cost
);
</code></pre>

If you are wondering what is wrong with your query, notice that the inner SELECT is returning 0 rows, because you are comparing the cost of the items with themselves, so c.cost is always equal to f.cost, so the &lt; comparation fails, so the inner select returns 0 rows, so the "not in" condition is true for all the rows

blocks|key|569492|text|如果您想要具有单个最高成本的pid：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|569493|SELECT+TOP+1+WITH+TIES
++++c.pid,
++++c.cost
FROM
++++Cata+AS+c
ORDER+BY+
++++c.cost+DESC|code-block|syntax|javascript|569494|如果您想要五个成本最高的pids，请将其第一行更改为：|569495|SELECT+TOP+5+WITH+TIES|569496|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you want the pid with the single highest cost:

<pre><code>SELECT TOP 1 WITH TIES
 c.pid,
 c.cost
FROM
 Cata AS c
ORDER BY 
 c.cost DESC
</code></pre>

If you want the five highest cost pids, change the first line of that to:

<pre><code>SELECT TOP 5 WITH TIES
</code></pre>

blocks|key|3507619|text|我想这就是你要找的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3507620|++++SELECT
+++++p.PID
+++++,+MAX(c.COST)

++++FROM
+++++Parts+p
++++LEFT+JOIN
+++++Cata+c
++++ON+p.PID+=+c.PID

++++GROUP+BY
+++++p.PID

++++ORDER+BY
+++++MAX(c.COST)|code-block|syntax|javascript|3507621|这将返回您最昂贵的部分按PID。|3507622|祝好运!|3507623|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I think this is what you are looking for 

<pre><code> SELECT
 p.PID
 , MAX(c.COST)

 FROM
 Parts p
 LEFT JOIN
 Cata c
 ON p.PID = c.PID

 GROUP BY
 p.PID

 ORDER BY
 MAX(c.COST)
</code></pre>

This will return you the most expensive part per PID

Good luck!

blocks|key|2095273|text|你想要|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2095274|查找最昂贵的部件的pids|blockquote|2095275|因为你没有清楚地提到你的要求，|2095276|我给出了两个解决方案|2095277|解决方案1+:为每个供应商寻找最广泛的部件|ordered-list-item|offset|length|style|BOLD|2095278|解决方案2:在所有部件中寻找最昂贵的部件的|2095279|用最适合你的东西。|2095280|解决方案1:|2095281|SELECT+Cata.pid+FROM+Cata
LEFT+OUTER+JOIN+(SELECT+Cata.sid,+MAX(Cata.cost)+cost+FROM+Cata+GROUP+BY+Cata.sid)+MostExpensive
ON+Cata.sid+=+MostExpensive.sid+AND+Cata.cost+=+MostExpensive.cost|code-block|syntax|javascript|2095282|查询说明：|2095283|首先，你应该试着根据每个sid的成本找出最贵的东西。|unordered-list-item|2095284|然后，一旦得到了最昂贵的表，就可以找到与同一sid中的成本相匹配的pids。|2095285|如果两个部分之间存在平分关系，则此查询将返回这两个部分的pids。|2095286|解决方案2:如果您在所有供应商中寻找最广泛的部件，那么查询可以简化如下。|2095287|SELECT+Cata.pid+FROM+Cata
WHERE+Cata.cost+=+(SELECT+MAX(cost)+cost+FROM+Cata)|2095288|entityMap^0|0|0|0|0|0|7|0|0|L|0|0|0|6|0|0|0|5|0|0|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1G|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|1H|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1I|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|1J|8|@$L|1K|M|1L|N|O]]|9|@]|A|$]]|$1|P|3|Q|5|K|7|1M|8|@$L|1N|M|1O|N|O]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1P|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1Q|8|@$L|1R|M|1S|N|O]]|9|@]|A|$]]|$1|V|3|W|5|X|7|1T|8|@]|9|@]|A|$Y|Z]]|$1|10|3|11|5|6|7|1U|8|@$L|1V|M|1W|N|O]]|9|@]|A|$]]|$1|12|3|13|5|14|7|1X|8|@]|9|@]|A|$]]|$1|15|3|16|5|14|7|1Y|8|@]|9|@]|A|$]]|$1|17|3|18|5|14|7|1Z|8|@]|9|@]|A|$]]|$1|19|3|1A|5|6|7|20|8|@$L|21|M|22|N|O]]|9|@]|A|$]]|$1|1B|3|1C|5|X|7|23|8|@]|9|@]|A|$Y|Z]]|$1|1D|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|1E|$]]

You want to 

<blockquote>
 Find the pids of the most expensive parts
</blockquote>

As you have not mentioned your requirement clearly, 

I have given 2 solutions

<ol>
<li>Solution 1 : to find most expansive parts for each supplier</li>
<li>Solution 2 : to find most expensive part amongst all the parts</li>
</ol>

Use which ever suits you. 

Solution 1:

<pre><code>SELECT Cata.pid FROM Cata
LEFT OUTER JOIN (SELECT Cata.sid, MAX(Cata.cost) cost FROM Cata GROUP BY Cata.sid) MostExpensive
ON Cata.sid = MostExpensive.sid AND Cata.cost = MostExpensive.cost
</code></pre>

Query explanation:

<ul>
<li>First you should try to find what is most expensive as per cost for each sid</li>
<li>then once you have that got that most expensive table derived, find pids which matches the cost in the same sid. </li>
<li>If there is a tie between 2 parts for being most expensive, this query will return pids for both the parts. </li>
</ul>

Solution 2: 
If you looking for most expansive parts across all the suppliers then query could be simplified as below. 

<pre><code>SELECT Cata.pid FROM Cata
WHERE Cata.cost = (SELECT MAX(cost) cost FROM Cata)
</code></pre>

Here is my schema
Suppliers(​sid ,​sname,address)
Cata(sid,pid,cost)
Parts(pid,pname,color)

bolded are primary keys
I am trying to write a query
"Find the pids of the most expensive parts" 
I am using set difference here is my query however its returning all the pids in the catalogue not the one with the highest cost

<pre><code>select Cata.pid 
from Cata 
where pid not in(
 select c.pid 
 from Cata c, Cata f 
 where c.sid=f.sid AND c.pid=f.pid AND c.cost&lt;f.cost
);
</code></pre>

Database query- find most expensive part

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

这是我的模式Suppliers(​sid，​名称，地址) Cata(sid，pid，cost)部件(pid，pname，color)粗体是我正在尝试编写的查询“查找最昂贵部件的pids”的主键，我在这里使用的是set差异，这里是我的查询，但是它返回目录中的所有pids，而不是成本最高的select Cata.pid from Cata where pid not in(    select c.p

问数据库查询-找到最昂贵的部分
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数据库查询-找到最昂贵的部分EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数据库查询-找到最昂贵的部分
EN