mysql - 带排序依据和限制的sql组查询

Question

我的表结构如下

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    2 | xyz      | 12    | swap    |    2 
|    2 | xyz      | 12    | sag     |    3
|    2 | xyz      | 14    | azr     |    1 
|    3 | pqr      | 2     | ron     |    2 
|    3 | pqr      | 22    | rah     |    1 
|    3 | pqr      | 32    | pra     |    5 

我已经做了如下查询

(select * from test.companies where id = '2' order by ttm desc limit 2)
union
(select * from test.companies where id = '3' order by ttm desc limit 2);

输出结果如下

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    2 | wissen   | 12    | sag     |    3
|    2 | wissen   | 12    | swap    |    2 
|    3 | prolinkd | 32    | pra     |    5
|    3 | prolinkd | 2     | ron     |    2 

但我想要输出如下

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    3 | prolinkd | 32    | pra     |    5
|    3 | prolinkd | 2     | ron     |    2
|    2 | wissen   | 12    | sag     |    3 
|    2 | wissen   | 12    | swap    |    2 

表示哪个公司拥有最大的ttm，它将首先显示

有知道的请回复

谢谢

Answer 1

相当丑陋，听起来也很丑陋，但它应该做你想做的。它通过每条记录检索每个公司的“maxttm”，然后使用它进行排序，让您可以根据最高的 ttm 为公司分配优先级。

SELECT * FROM (
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '2' ORDER BY ttm DESC LIMIT 2)
    UNION
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '3' ORDER BY ttm DESC LIMIT 2)
) AS myunion ORDER BY maxttm, id;

附言。感谢 inti，我无耻地基于其查询。