从另一个表中的每个ID的表中选择Top 1
表结构是:
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,
)
每个水果可以分配给多个客户创建水果分配记录。 每个fruit_allocation记录可以有多个测量。
我想为给定水果ID的每个fruit_allocation选择最新的度量值
到目前为止,我有以下几点:
select *
from measurement
where fruit_allocation_id in (select id
from fruit_allocation
where fruit_id = 10)
这将返回该水果的所有测量结果,我想只返回1个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
链接地址: http://www.djcxy.com/p/86213.html
上一篇: Select Top 1 From a Table For Each Id in another Table
下一篇: What is the difference between LATERAL and a subquery in PostgreSQL?