多个结果集union后保持各自原有排序，该怎么写SELECT

[zjroland]

多个结果集union后保持各自原有排序，该怎么写SELECT

SELECT *
FROM (SELECT '002', '语文', '' FROM DUAL ORDER BY 1)
UNION ALL
SELECT *
FROM (SELECT '001', '数学', '' FROM DUAL ORDER BY 1)
UNION ALL
SELECT * FROM (SELECT '003', '英语', '' FROM DUAL ORDER BY 1)

这样写会报错误，该如何写？

[骑着蜗牛去爬山]

SELECT *
  FROM (SELECT  top 99.9999999999999 percent '002' as c1, '语文' as c2, '' as c3 FROM DUAL  ORDER BY 1)t1
UNION ALL
SELECT *
  FROM (SELECT  top 99.9999999999999 percent '001' as c1, '数学' as c2, '' as c3 FROM DUAL  ORDER BY 1)t2
UNION ALL
SELECT * FROM (SELECT  top 99.9999999999999 percent '003' as c1, '英语' as c2, '' as c3 FROM DUAL  ORDER BY 1)t3

[卖水果的net]

-- 多写一层 select * from (select * from a order by id) a union all select * from (select * from b order by id) b

[卖水果的net]

把内层查询，取一个别名 ； PS：你是 oracle 还是 sql server ？

[zjroland]

sql server

[二月十六]

SELECT  *
FROM    ( SELECT TOP 99.9999 PERCENT
                    '002' AS a ,
                    '语文' AS b ,
                    '' AS c
          FROM      DUAL
          ORDER BY  1
        ) t
UNION ALL
SELECT  *
FROM    ( SELECT TOP 99.9999 PERCENT
                    '001' AS a ,
                    '数学' AS b ,
                    '' AS c
          FROM      DUAL
          ORDER BY  1
        ) t
UNION ALL
SELECT  *
FROM    ( SELECT TOP 99.9999 PERCENT
                    '003' AS a ,
                    '英语' AS b ,
                    '' AS c
          FROM      DUAL
          ORDER BY  1
        ) t

[zjroland]

如果是 oracle这语句就对了。。。

[二月十六]

引用 1 楼 hjywyj 的回复:

SELECT *
  FROM (SELECT  top 99.9999999999999 percent '002' as c1, '语文' as c2, '' as c3 FROM DUAL  ORDER BY 1)t1
UNION ALL
SELECT *
  FROM (SELECT  top 99.9999999999999 percent '001' as c1, '数学' as c2, '' as c3 FROM DUAL  ORDER BY 1)t2
UNION ALL
SELECT * FROM (SELECT  top 99.9999999999999 percent '003' as c1, '英语' as c2, '' as c3 FROM DUAL  ORDER BY 1)t3

好好上班，逛什么论坛

[骑着蜗牛去爬山]

引用 7 楼 sinat_28984567 的回复:

[quote=引用 1 楼 hjywyj 的回复:]

SELECT *
  FROM (SELECT  top 99.9999999999999 percent '002' as c1, '语文' as c2, '' as c3 FROM DUAL  ORDER BY 1)t1
UNION ALL
SELECT *
  FROM (SELECT  top 99.9999999999999 percent '001' as c1, '数学' as c2, '' as c3 FROM DUAL  ORDER BY 1)t2
UNION ALL
SELECT * FROM (SELECT  top 99.9999999999999 percent '003' as c1, '英语' as c2, '' as c3 FROM DUAL  ORDER BY 1)t3

好好上班，逛什么论坛 [/quote]

[zjroland]

果然加了一层就可以了，呵呵，多谢高手。。。

[空城旧巷]

我写的怎么还是不对呢