Select Top 1 From a Table For Each Id in another Table
Table Structure is:
create table fruit (
id int identity(1,1),
name varchar(max)
)
create table fruit_allocation (
id int identity(1,1),
fruit_id int references fruit(id),
customer_id int references store(id),
amount float,
)
create table measurement (
fruit_allocation_id int references fruit_allocation(id),
measurement_date datetime,
measurement float,
)
Each fruit can be allocated to more than one customer creating a fruit_allocation record. Each fruit_allocation record can have multiple measurements.
I want to select the latest measurement for each fruit_allocation given a fruit id
So far I have the following:
select *
from measurement
where fruit_allocation_id in (select id
from fruit_allocation
where fruit_id = 10)
This returns all measurements for that fruit, I want to just return 1 measurement per fruit_allocation.
你可以交叉应用
select a.*, m.*
from fruit_allocation a
cross apply (
select top 1 *
from measurement m
where m.fruit_allocation_id = a.id
order by m.measurement_date desc
) m
where a.fruit_id = 10
假设你正在使用SQL Server 2005+
With RankedMeasurements As
(
Select M.fruit_allocation_id
, M.measurement_date
, M.measurement
, Row_Number() Over ( Partition By M.fruit_allocation_id
Order By M.measurement_date Desc ) As Rnk
From measurement As M
Where Exists (
Select 1
From fruit_allocation As FA1
Where FA1.id = M.fruit_allocation_id
And FA1.fruit_id = 10
)
)
Select RM.fruit_allocation_id
, RM.measurement_date
, RM.measurement
From RankedMeasurements As RM
Where Rnk = 1
创建一个子查询以查找每个分配的最新度量值，然后加入该子查询，就好像它是一个真正的表。
select * from measurement meas
join
(
SELECT fruit_allocation_id,
MAX(measurement_date) as max_date
FROM measurement meas2
JOIN fruit_allocation alloc
ON alloc.id = meas2.fruit_allocation_id
where fruit_id = 10
) max_meas
on meas.fruit_allocation_id = max_mes.fruit_allocation_id
and meas.measurement_date = max_meas.max_date
上一篇: 交叉应用与表值功能限制性能