使用 MAX() 函数的 SQL 查询在 where 条件和显示数据中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16039100/
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 Query using MAX() function in where condition and display data
提问by Sam
I have a DataTable in which I have LCNo (string), AmmendmentNo (string). I want to retrieve the row which has highest AmmendmentNofor a respective LCNo
我有一个数据表，其中有LCNo (string), AmmendmentNo (string). 我想检索AmmendmentNo各自最高的行LCNo
For example:
例如：
LCNO (123,123,123,123) AmmendmentNo (0,1,2,3)
LCNO (123,123,123,123) AmendmentNo (0,1,2,3)
I want the row which has LCNo: 123 and AmmendmentNo: 3
我想要具有 LCNo: 123 和 AmendmentNo: 3 的行
select * from LCTable where LCNo= '12147001' AND MAX(AmendmentNo);
回答by Gordon Linoff
For this, use limitor topor something similar:
为此，使用limit或top类似的东西：
select *
from LCTable
where LCNo= '12147001'
order by AmendmentNo desc
limit 1
In SQL Server, this would use top:
在 SQL Server 中，这将使用top：
select top 1 *
from LCTable
where LCNo= '12147001'
order by AmendmentNo desc
回答by John Woo
use GROUP BYclause. This will give you all LCNowith its highest AmendmentNo.
使用GROUP BY条款。这会给你所有LCNo的最高AmendmentNo。
SELECT LCNo, MAX(AmendmentNo) AmendmentNo
FROM LCTable
GROUP BY LCNo
but if you want tfor a specific LCNo, you can simply use below. Remember that using the query below gets only one LCNo.
但是如果你想要一个特定的LCNo，你可以简单地在下面使用。请记住，使用下面的查询只会得到一个LCNo.
SELECT LCNo, MAX(AmendmentNo) AmendmentNo
FROM LCTable
WHERE LCNo = '12147001'
