从2个表中选择记录并对其进行排序 - Select records from 2 tables and ordering them

Select records from 2 tables and ordering them

本文关键字：排序记录 2个选择 | 更新日期: 2023-09-27

我有两个表A和B，它们有以下记录

--TABLE A--  
id        date          value  
1        09/01/2012    Reward  
2        09/01/2012    Purchase  
3        07/01/2012    Reward  
4        07/01/2012    Purchase
--TABLE B--  
id   id_from_a     date           value  
1       1         10/01/2012     Generated Rewrd   
2       3         08/01/2012     Generated Reward

现在我希望结果像低于

id        date          value  
1         10/01/2012    Generated Reward  
1         09/01/2012    Reward  
2         09/01/2012    Purchase  
3         08/01/2012    Generated Reward  
3         07/01/2012    Reward  
4         07/01/2012    Purchase

我知道使用UNIONS会合并这两个表，但我如何像上面提到的那样排序？

UNION可以在末尾指定一个ORDER BY子句，该子句应用于整个组合结果集。

SELECT id, date, value
    FROM table_A
UNION
SELECT id, date, value
    FROM table_B
ORDER BY id, date DESC

如果您不需要UNION操作的重复数据消除功能，UNIONALL将产生更好的性能。

SELECT id, date, value
    FROM table_A
UNION ALL
SELECT id, date, value
    FROM table_B
ORDER BY id, date DESC

看起来您需要来自table_a的id和来自table_b的id_from_a。您只能在ORDER BY子句中使用id，不过：

SELECT 
 id, date, value
FROM table_a
UNION ALL
SELECT
  id_from_a, date, value
FROM table_b
ORDER BY id, date DESC

(SELECT id, date, value FROM Table_A
UNION ALL
SELECT id, date, value FROM Tabble_B)
ORDER BY id, date DESC

为了安全起见，请确保使用UNION ALL。UNION ALL就是UNION，SELECT DISTINCT就是SELECT。。。UNION ALL将显示重复记录的所有实例，而不是仅显示一个实例。

您可以对并集的结果进行排序，检查下面的语法

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a;

另请看http://dev.mysql.com/doc/refman/5.0/en/union.html