SQL查询-在UNION中使用Order By

Question

从两个表中提取数据时，如何以编程方式对联合查询进行排序？例如，SELECT table1.field1 FROM table1 ORDER BY table1.field1UNIONSELECT table2.field1 FROM table2 ORDER BY table2.field1引发异常注意：正在MS Access Jet数据库引擎上尝试这样做

Answer 1

有时您需要ORDER BY在每个部分中将与组合在一起UNION。

在这种情况下

SELECT * FROM 

(

  SELECT table1.field1 FROM table1 ORDER BY table1.field1

) DUMMY_ALIAS1

UNION ALL

SELECT * FROM

( 

  SELECT table2.field1 FROM table2 ORDER BY table2.field1

) DUMMY_ALIAS2

Answer 2

SELECT field1 FROM table1

UNION

SELECT field1 FROM table2

ORDER BY field1