MySql：获取最新记录，然后按字母顺序分组

debugcn 发表于 Dev

mpdc

我有以下查询：

SELECT * FROM form_advertisers fa
INNER JOIN form_settings fs USING(form_id)
WHERE fa.advertiser_id = $adv_id
GROUP BY fs.ukip_title_id
ORDER BY fs.form_title ASC, fs.form_id DESC

简化：

SELECT form_id, form_title FROM form_advertisers
WHERE advertiser_id = 135
GROUP BY ukip_title_id
ORDER BY form_title ASC, form_id DESC

但这只是对form_titlethen的结果集进行排序form_id，而我需要它form_id为每个结果查找最新的结果，$adv_id 然后按字母顺序对它们进行排序。

我读到的关键词HAVING，也（看起来像是）一个SELECT内部的SELECT，但它确实相当混乱。任何人都可以让我的查询正常工作，甚至可以抽出时间来解释它的作用吗？

结果（按字母顺序排列，但不是最新的form_id）：

form_id   form_title
4         Automotive Testing
756       Electric & Hybrid Marine
22        Electric & Hybrid Vehicle
11        Engine
21        European Automotive
721       Magnetics
24        Professional MotorSport
220       Transmission

建议结果（form_id DESC之前form_title ASC）（由于仅对输出进行排序，因此仍然不正确）：

form_id   form_title
756       Electric & Hybrid Marine
721       Magnetics
220       Transmission
24        Professional MotorSport
22        Electric & Hybrid Vehicle
21        European Automotive
11        Engine
4         Automotive Testing

预期结果（最新form_id）：

form_id   form_title
610       Automotive Testing
756       Electric & Hybrid Marine
940       Electric & Hybrid Vehicle
912       Engine
132       European Automotive
720       Magnetics
780       Professional MotorSport
332       Transmission

完整数据：

4   Automotive Testing International
11  Engine Technology International
22  Electric & Hybrid Vehicle Technology International
21  European Automotive Components
24  Professional Motorsport World
25  Automotive Testing International
43  Engine Technology International
57  Engine Technology International
62  European Automotive Components
78  European Automotive Components
82  Engine Technology International
92  Electric & Hybrid Vehicle Technology International...
106 Engine Technology International
109 Professional Motorsport World
118 Engine Technology International
132 European Automotive Components
144 Electric & Hybrid Vehicle Technology International...
218 Electric & Hybrid Vehicle Technology International
220 Transmission Technology International
226 Engine Technology International
252 Engine Technology International
278 Engine Technology International
284 Electric & Hybrid Vehicle Technology International
330 Engine Technology International
332 Transmission Technology International
358 Electric & Hybrid Vehicle Technology International
392 Engine Technology International
428 Engine Technology International
434 Professional Motorsport World
458 Engine Technology International
470 Electric & Hybrid Vehicle Technology International
570 Electric & Hybrid Vehicle Technology International
584 Engine Technology International
610 Automotive Testing International
618 Engine Technology International
638 Electric & Hybrid Vehicle Technology International
720 Magnetics Technology International
752 Electric & Hybrid Vehicle Technology International
764 Engine Technology International
780 Professional Motorsport World
756 Electric & Hybrid Marine Technology International
800 Engine Technology International
820 Electric & Hybrid Vehicle Technology International
852 Engine Technology International
912 Engine Technology International
940 Electric & Hybrid Vehicle Technology International

约翰·鲁德尔

试试这个……让我知道它是否有效：

SELECT * FROM( 
    SELECT form_id as form_id,
        mag_logo_url, 
        cover_date, 
        fs.ukip_title_id as title_id, 
        fs.form_title as form_title 
    FROM form_advertisers fa 
    INNER JOIN form_settings fs 
    USING ( form_id ) 
    WHERE fa.advertiser_id =135 
    ORDER BY fs.form_id DESC 
)as temp 
GROUP BY title_id 
ORDER BY form_title ASC , form_id DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。