sql union order by 排序问题

wwttqq85538649 2011-05-13 04:30:43

当我使用union 以及order by 进行分组排序时，若出现多个union ，那么排序就会有问题，先上代码

以下代码是：先按type分组，然后再排序，最后合并。但当运行时，出现type的顺序是1,1,1,1,1,2,2,2,3,2,2,2,3，但是当我把type1删掉时，显示type的顺序为：2，2,2,2,2,2,3,3.这说明各个分组是相互影响的。请高手帮忙，看问题出现在哪？

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 1 and year(exportTime) = year(getdate())
order by businessNum,billNum) as a

union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 2 and year(exportTime) = year(getdate())
order by businessNum,billNum) as b

union
select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 3 and year(exportTime) = year(getdate())
order by businessNum,billNum) as c
union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 4 and year(exportTime) = year(getdate())
order by businessNum,billNum)as d
union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 5 and year(exportTime) = year(getdate())
order by businessNum,billNum)as e

union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type =6 and year(exportTime) = year(getdate())
order by businessNum,billNum) as f
union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 7 and year(exportTime) = year(getdate())
order by case when arrivalTime is null then 1 else 0 end,businessNum,billNum)as g
union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 8 and year(exportTime) = year(getdate())
order by case when arrivalTime is null then 1 else 0 end,businessNum,billNum)as h

union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 9 and year(exportTime) = year(getdate()) )as i
union

select * from(
select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,type
from counter
where type = 10 and year(exportTime) = year(getdate()) )as j

...全文