将多个select 语句使用 Union All 联合后,查询结果并未次序显示。例如:
sql1:
select * from t where type=1 order by id;
sql2:
select * from t where type=2 order by id;
sql3:
select * from t where type=3 order by id;
sql1 union all sql2 union all sql3;
sql1、sql2和sql3分别查询的时候是正确的,union all 后顺序就打乱了,怎么才能让他保持原来的顺序呢?即:先显示sql1的结果集,再显示sql2的结果集,然后显示sql3
本人这样解决的:在三个sql中都加入临时列,别名Sort_index,值依次递增,如下:
sql1:
select 1 Sort_index,* from t where type=1 order by id;
sql2:
select 2 Sort_index,* from t where type=2 order by id;
sql3:
select 3 Sort_index,* from t where type=3 order by id;
之后在外层加入Order by Sort_index,如下:
select * from (sql1 union all sql2 union all sql3) order by Sort_index;
执行结果顺序显示。