order by union 应用实例 mssql

order by union 应用实例 mssql

 

跨库查询 分页问题烦了我一阵子了。

 找不到不影响速度的方法，主要原因是 UNION 与ORDER BY写在一起报错。

 

因为order by不能直接出现在union的子句中，试着将它写在子句的子句中。于是问题解决了。

至今不 知道SQL为什么不能把order by 写在union了句中。

 

报错代码如下：

 

　select '加密锁' as xxmc ,ssh from a709.dbo.ts_gcb


union all select '测试加密锁' as xxmc ,ssh from a710.dbo.ts_gcb

 

union all select '金思维演示第三图书馆' as xxmc ,ssh from jswlibs1.dbo.ts_gcb

 

代码如下：

 

SELECT * FROM (

SELECT TOP 5 * FROM (

SELECT TOP 40 * from (

 select '加密锁' as xxmc ,ssh from a709.dbo.ts_gcb

union all select '测试加密锁' as xxmc ,ssh from a710.dbo.ts_gcb

union all select '金思维演示第三图书馆' as xxmc ,ssh from jswlibs1.dbo.ts_gcb

) as tempdb ORDER BY "ssh" desc

) AS inner_tbl ORDER BY "ssh" ASC

) AS outer_tbl ORDER BY "ssh" desc