排序和case、then

排序

order by left(orderNo,2) desc,orderNo asc
//

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_getallinfo_tq]

as
begin
SELECT * FROM
(
SELECT * FROM
(
select
case dt.status
WHEN ‘1’ THEN ‘1.等待进厂’ when ‘2’ then ‘2.通知进厂’ when ‘3’ then ‘3.厂内等待’
when ‘4’ then ‘4.通知入库’ when ‘5’ then ‘5.进入料库’ when ‘6’ then ‘6.皮重计量’
when ‘7’ then ‘7.正在装料’ when ‘8’ then ‘8.装料完成’ when ‘9’ then ‘9.出库’
when ‘10’ then ‘10.等待洗车’ when ‘80’ then ‘80.申请转库’ when ‘81’ then ‘81.等待转库’
when ‘90’ then ‘90.允许出厂’ when ‘93’ then ‘93.空车出厂’ when ‘100’ then ‘100.已出厂’
ELSE dt.status END 状态
,dt.status
,dt.docket_no 记录编号
,dt.orderNo 排队序号
,dt.orderNo
,dt.truck 车号
,dr.drivername 驾驶员
,case t.trucktype
WHEN ‘1’ THEN ‘销售车辆’ when ‘2’ then ‘采购车辆’ when ‘3’ then ‘行政车辆’
when ‘4’ then ‘矿粉车辆’ when ‘5’ then ‘水渣车辆’ when ‘6’ then ‘访客车辆’
ELSE t.trucktype end 车辆类型
,t.trucktype
,dt.billofladingid 订单编号
,dt.VIPbillofladingid VIP订单号
,customer.name 客户名称
,site.name 收货地址
,p.name 产品规格
,dt.source 料库
,dt.insourcedate 进入时间
,dt.team 运输公司
, dt.inweight 第一次称重
, dt.inbridgedate 第一次称重时间
, dt.outweight 第二次称重
,dt.outbridgedate 第二次称重时间
,dt.cusweight 净重
,dt.washtime 洗车时间
,c.shipments 可发货量
,o.inDate 开始时间
,o.outDate 结束时间
,dt.ratedload 额定装载量
,dt.insourcedate 入库时间
,dt.outsourcedate 出库时间
,dt.indate 进厂时间
,(select distinct(bridgediscription) from config where bridge=dt.door_no_in) 进厂闸口
from Document dt
left join Driver dr on dt.driverid=dr.driverid
left join customer on dt.customer =customer.code
left join site on dt.site =site.code
left join product p on dt.product=p.code
left join truck t on t.code=dt.truck
left join contract c on c.contractId=dt.contract
left join OutStoreQA o on o.ID=dt.docket_no
where dt.status between 1 and 99 and t.trucktype <>6
– order by trucktype,dt.status,left(orderNo,2) desc,orderNo asc

) AS TABLEA1

union 

SELECT * FROM
(
select
case dt.status
WHEN ‘1’ THEN ‘1.等待进厂’ when ‘2’ then ‘2.通知进厂’ when ‘3’ then ‘3.厂内等待’
when ‘4’ then ‘4.通知入库’ when ‘5’ then ‘5.进入料库’ when ‘6’ then ‘6.皮重计量’
when ‘7’ then ‘7.正在装料’ when ‘8’ then ‘8.装料完成’ when ‘9’ then ‘9.出库’
when ‘10’ then ‘10.等待洗车’ when ‘80’ then ‘80.申请转库’ when ‘81’ then ‘81.等待转库’
when ‘90’ then ‘90.允许出厂’ when ‘93’ then ‘93.空车出厂’ when ‘100’ then ‘100.已出厂’
ELSE dt.status END 状态
,dt.status
,dt.docket_no 记录编号
,dt.orderNo 排队序号
,dt.orderNo
,dt.truck 车号
,dr.drivername 驾驶员
,case t.trucktype
WHEN ‘1’ THEN ‘销售车辆’ when ‘2’ then ‘采购车辆’ when ‘3’ then ‘行政车辆’
when ‘4’ then ‘矿粉车辆’ when ‘5’ then ‘水渣车辆’ when ‘6’ then ‘访客车辆’
ELSE t.trucktype end 车辆类型
,t.trucktype
,dt.billofladingid 订单编号
,dt.VIPbillofladingid VIP订单号
,customer.name 客户名称
,site.name 收货地址
,p.name 产品规格
,dt.source 料库
,dt.insourcedate 进入时间
,dt.team 运输公司
, dt.inweight 第一次称重
, dt.inbridgedate 第一次称重时间
, dt.outweight 第二次称重
,dt.outbridgedate 第二次称重时间
,dt.cusweight 净重
,dt.washtime 洗车时间
,c.shipments 可发货量
,o.inDate 开始时间
,o.outDate 结束时间
,dt.ratedload 额定装载量
,dt.insourcedate 入库时间
,dt.outsourcedate 出库时间
,dt.indate 进厂时间
,(select distinct(bridgediscription) from config where bridge=dt.door_no_in) 进厂闸口
from Document dt
left join Driver dr on dt.driverid=dr.driverid
left join customer on dt.customer =customer.code
left join site on dt.site =site.code
left join product p on dt.product=p.code
left join truck t on t.code=dt.truck
left join contract c on c.contractId=dt.contract
left join OutStoreQA o on o.ID=dt.docket_no
where dt.status between 1 and 99 and DATEDIFF(dd,dt.indate,GETDATE())=0 and t.trucktype=6
– order by t.trucktype,dt.status,left(dt.orderNo,2) desc,dt.orderNo asc

)
AS TABLEA2

) AAA order by AAA.trucktype,AAA.status,left(AAA.orderNo,2) desc,AAA.orderNo asc

end