mysql - 从多个表中获取具有条件的最新值

Question

我正在使用 Left Joins 从多个表进行查询，目前我的问题是我正在搜索一个名为 Design Code 的唯一值，但一个 Design Code 可以在多个 Deal 中。例如:

id  designcode_id   deal_id used
206 100192  34  1
16816   100192  1789    1
20419   100192  2237    1
20797   100192  2298    1

现在，当我在我的代码上进行搜索时，我得到了这 4 个交易，在这种情况下我希望只获得最新的交易:

id  designcode_id   deal_id used
20797   100192  2298    1

因此，对于具有最高交易 ID(例如 2298)的每个设计代码，应该只放置一行。

这是我的代码:

我尝试了 Functions Distinct 和 MAX，但对我不起作用。

SELECT DISTINCT
deals.deal_id as "Deal ID",
accounts.account_name as "Account",
deals.deal_name as "Deal Name",
designcodes.id as "Designcode ID",
styles.style_number as "Style ID",
styles.style_name "Style Type",
case when artjobs.type_id = 10 then 'Prodcution' 
when artjobs.type_id = 1 then 'Concept'
when artjobs.type_id = 2 then 'Draft'
when artjobs.type_id = 3 then 'Revision'
when artjobs.type_id = 4 then 'Store Images'
else 'Unknown Type' end as "Type of Artjob",
case when artjobs.status_id = 100 then 'Complete' else 'Unknown Status' end as "Art Job Status",
case when artjobs.due_date >= DATE_SUB(NOW(), INTERVAL 25 DAY) then 'Under 25 Days'
when artjobs.due_date < DATE_SUB(NOW(), INTERVAL 25 DAY) then 'Over 25 Days'
end as "Date Evaluation",
artjobs.due_date as "Due Date"
from artjobs
left join arttasks on artjobs.id = arttasks.artjob_id
left join designcodes on arttasks.designcode_id = designcodes.id
left join designcode_deal on designcodes.id = designcode_deal.designcode_id
left join deals on designcode_deal.deal_id = deals.deal_id
left join accounts on deals.account_id = accounts.account_id
left join styles on designcodes.style_id = styles.style_id
WHERE 
designcode_deal.designcode_id in (
100192,
100194,
100232
)
-- and artjobs.due_date>= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- and artjobs.type_id = 10
-- and artjobs.status_id =100
order by artjobs.type_id asc 

最后我希望它只显示最新的信息。

Answer 1

你可以使用 order by "your_deal_id"desc 然后你得到最新的，假设你有一个增量 id