SQL 根据最近的日期选择具有两个唯一列的行

使用以下查询和结果，我将查找 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

期望:

    CHARGEID    CHARGETYPE  SERVICEMONTH
1   101         R           8/1/2008
2   161         N           2/1/2008
382944 次浏览
小开
最佳答案

You can use a 群体 to group items by type and id. Then you can use the 麦克斯 Aggregate function to get the most recent service month. The below returns a result set with ChargeId, ChargeType, and MostRecentServiceMonth

SELECT
CHARGEID,
CHARGETYPE,
MAX(SERVICEMONTH) AS "MostRecentServiceMonth"
FROM INVOICE
GROUP BY CHARGEID, CHARGETYPE
小开 
SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth
FROM invoice
GROUP BY chargeId, chargeType
小开

所以这不是请求者所要求的，而是“ SQL 在最近的日期选择行”的答案。

改编自 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
小开 
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

如果不同的电荷 id 具有不同的 chargetype 组合，那么上述查询将会起作用。希望这个简单的查询可以帮助您在很少的性能时间内考虑..。

小开

我看到大多数开发人员在使用内联查询时没有考虑到它对海量数据的影响。

简单来说，你可以通过以下方法达到这个目的:

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
小开

Sqlfiddle的演示:

  1. 古典方式。
select
chargeid,
chargetype,
SERVICEMONTH
from invoice t0
where t0.SERVICEMONTH = (
select max(SERVICEMONTH)
from invoice t1
where t1.chargeid = t0.chargeid
and t1.chargetype = t0.chargetype
);
  1. 使用窗口函数。
with w_o as (
select
chargeid,
chargetype,
SERVICEMONTH,
row_number() OVER (PARTITION BY chargeid, chargetype ORDER BY SERVICEMONTH DESC) rn
from invoice
)
select
chargeid,
chargetype,
SERVICEMONTH
from w_o
where rn = 1;

理解并能够使用这两种风格是很好的。


提交答案