我有两个表:
materials:
int id
text title
costs:
int id
int material_id
int cost
datetime created_at
数据:
materials:
1 Wood
2 Steel
3 ...
costs:
1,1,200,2015-07-02
2,1,205,2015-07-03
3,1,210,2015-07-04
我想要最新成本的材料。这样的:
id title cost
1 Wood 210
我试试这个:
select materials.id, title, cost from materials
left join (
select * from costs
order by costs.id DESC
) as costs on costs.material_id = materials.id
group by materials.id
:
select materials.id, materials.title, c1.cost from costs c1
join (select material_id, max(id) as id from costs group by material_id) AS c2
on c1.id = c2.id
left join materials on materials.id = c1.material_id
但是cost有时(!)不正确。
返回一个值的正确开销:
select cost, material_id
from costs
where material_id = 2
order by id DESC
limit 1
现在我只需要在PHP中获取材料列表和成本foreach循环(每行+1查询)
请帮帮我!
select m.id, m.title, c1.cost
from materials m
join costs c1 on c1.material_id = m.id
join
(
select material_id, max(created_at) as dt
from costs
group by material_id
) c2 on c2.material_id = m.id and c2.dt = c1.created_at