SQL 按最近日期选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/189213/
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 selecting rows by most recent date
提问by jgreep
Using the following query and results, I'm looking for the most recent entry where the ChargeId and ChargeType are unique.
使用以下查询和结果，我正在寻找 ChargeId 和 ChargeType 唯一的最新条目。
select chargeId, chargeType, serviceMonth from invoice
CHARGEID CHARGETYPE SERVICEMONTH
1 101 R 8/1/2008
2 161 N 2/1/2008
3 101 R 2/1/2008
4 101 R 3/1/2008
5 101 R 4/1/2008
6 101 R 5/1/2008
7 101 R 6/1/2008
8 101 R 7/1/2008
Desired:
期望：
CHARGEID CHARGETYPE SERVICEMONTH
1 101 R 8/1/2008
2 161 N 2/1/2008
回答by Mitchel Sellers
You can use a GROUP BYto group items by type and id. Then you can use the MAX()Aggregate function to get the most recent service month. The below returns a result set with ChargeId, ChargeType, and MostRecentServiceMonth
您可以使用GROUP BY按类型和 ID 对项目进行分组。然后您可以使用MAX()Aggregate 函数获取最近的服务月份。下面返回一个带有 ChargeId、ChargeType 和 MostRecentServiceMonth 的结果集
SELECT
CHARGEID,
CHARGETYPE,
MAX(SERVICEMONTH) AS "MostRecentServiceMonth"
FROM INVOICE
GROUP BY CHARGEID, CHARGETYPE
回答by tvanfosson
So this isn't what the requester was asking for but it is the answer to "SQL selecting rows by most recent date".
所以这不是请求者所要求的，而是“SQL 按最近日期选择行”的答案。
Modified from http://wiki.lessthandot.com/index.php/Returning_The_Maximum_Value_For_A_Row
修改自http://wiki.lessthandot.com/index.php/Returning_The_Maximum_Value_For_A_Row
SELECT t.chargeId, t.chargeType, t.serviceMonth FROM(
SELECT chargeId,MAX(serviceMonth) AS serviceMonth
FROM invoice
GROUP BY chargeId) x
JOIN invoice t ON x.chargeId =t.chargeId
AND x.serviceMonth = t.serviceMonth
回答by Ben Hoffstein
SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth
FROM invoice
GROUP BY chargeId, chargeType
回答by sujeet
I see most of the developers use inline query without looking out it's impact on huge data.
我看到大多数开发人员使用内联查询而没有注意它对大量数据的影响。
in simple you can achieve this by:
简单来说，您可以通过以下方式实现：
select a.chargeId, a.chargeType, a.serviceMonth
from invoice a
left outer join invoice b
on a.chargeId=b.chargeId and a.serviceMonth <b.serviceMonth
where b.chargeId is null
order by a.serviceMonth desc
回答by pari elanchezhiyan
select to.chargeid,t0.po,i.chargetype from invoice i
inner join
(select chargeid,max(servicemonth)po from invoice
group by chargeid)t0
on i.chargeid=t0.chargeid
The above query will work if the distinct charge id has different chargetype combinations.Hope this simple query helps with little performance time into consideration...
如果不同的费用 id 具有不同的费用类型组合，则上述查询将起作用。希望这个简单的查询有助于考虑很少的性能时间...
