MySQL 带有 where 子句的 SQL MIN 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9617453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL MIN Function with where clause
提问by Mifas
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)
回答by Andreas
SELECT p.name
FROM Project p, Shipment s
WHERE s.JNo = p.JNo
AND s.Qty in (SELECT MIN(qty) FROM shipment)
回答by Sudhir Bastakoti
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
回答by Lion
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 Projecttable and minimum Qty from the shipmenttable.
将显示Project表中的项目名称和表中的最小数量shipment。
回答by Aristotelis Kostopoulos
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.
我希望这可以帮助你。
回答by Aristotelis Kostopoulos
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
