带有where子句的SQL MIN函数

This is my Project Table

这是我的项目表

 Project Table
JNo Name    City
J1  Proj1   London
J2  Proj2   Paris
J3  Proj3   Athens
J4  Proj4   India

And this is my shipment table

这是我的货运表

Shipment
SNo PNo JNo Qty
S1  P1  J1  50
S1  P1  J2  90
S1  P2  J1  40
S1  P3  J3  20
S2  P1  J3  110
S2  P2  J2  30
S2  P4  J3  10
S2  P3  J1  100
S3  P1  J3  80
S3  P4  J2  70
S3  P4  J2  70
S4  P1  J3  20
S4  P2  J1  60

I want to name of the project having minimum quantity supplied.

我想要提供最小数量的项目名称。

I tried. But its return only minimum qty value this is my code

我试过了。但它只返回最小数值，这是我的代码

select min(qty) from shipment where jno IN(select jno from project)

5 个解决方案

#1

select p.name from Project p, Shipment s where s.JNo=p.JNo and s.Qty in (select min(qty) from shipment)

#2

This should work as you say

这应该像你说的那样工作

select p.Name, s.Qty 
from Project p, Shipment s
where p.Jno=s.Jno
and s.Qty in(select min(s.Qty) from Shipment s);

Would display Project Name from the Project table and minimum Qty from the shipment table.

将显示项目表中的项目名称和出货表中的最小数量。

#3

Without using MIN:

不使用MIN：


    SELECT p.Name, s.Qty
    FROM `project` p
    INNER JOIN `shipment` s ON `p`.`jno` = `s`.`jno`
    ORDER BY `s`.`qty` ASC
    LIMIT 1

#4

The query that you should use is

您应该使用的查询是

SELECT project.Name, min(qty) FROM Project 
LEFT JOIN Shipment ON project.JNO = Shipment.JNO

I hope that this can help you.

我希望这可以帮到你。

#5

For the project with the single smallest shipment, try:

对于发货量最小的项目，请尝试：

select p.name
from project p
join shipment s on p.jno=s.jno
order by s.qty
limit 1

For the project with the smallest total quantity shipped, try:

对于总出货量最小的项目，请尝试：

select name from
(select p.name, sum(s.qty) total_shipped
 from project p
 join shipment s on p.jno=s.jno
 group by p.name
 order by 2) sq
limit 1

#1

select p.name from Project p, Shipment s where s.JNo=p.JNo and s.Qty in (select min(qty) from shipment)

#2