select MAX(num) from dzhw_pcd
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where (a.派车单状态 = '未派' or a.派车单状态 = '已派') and a.单据状态=0 order by a.派车单号 desc,a.派车单状态 asc
UNION All
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where a.派车单状态 = '已派' and a.单据状态=1 order by a.派车单号 desc,a.派车单状态 asc
3 个解决方案
#1
select MAX(num) from dzhw_pcd
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where (a.派车单状态 = '未派' or a.派车单状态 = '已派') and a.单据状态=0
UNION All
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where a.派车单状态 = '已派' and a.单据状态=1 order by a.派车单号 desc,a.派车单状态 asc
#2
select MAX(num) from dzhw_pcd
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where (a.派车单状态 = '未派' or a.派车单状态 = '已派') and a.单据状态=0
UNION All
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where a.派车单状态 = '已派' and a.单据状态=1 order by a.派车单号 desc,a.派车单状态 asc
#3
UNION ALL 的两个集合不要加order by ，把order by 放到UNION ALL的结合之外
#1
select MAX(num) from dzhw_pcd
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where (a.派车单状态 = '未派' or a.派车单状态 = '已派') and a.单据状态=0
UNION All
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where a.派车单状态 = '已派' and a.单据状态=1 order by a.派车单号 desc,a.派车单状态 asc
#2
select MAX(num) from dzhw_pcd
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where (a.派车单状态 = '未派' or a.派车单状态 = '已派') and a.单据状态=0
UNION All
select a.* from (
SELECT cast(0 as bit) 选择,(case when 派车单状态=0 then '未派'
when 派车单状态=1 then '已派'
when 派车单状态=2 then '作废'
when 派车单状态=3 then '审核' else '未知' end) as 派车单状态, b.派车单号,a.出库票号, a.数量, a.产品名称, a.规格, a.单位,a.地址, a.出库时间,
b.指派承运商名称, b.指派承运商编码, b.指派联系人编码, b.指派联系人名称,
b.指派承运商地址, b.当前联系人编码, b.当前承运商编码, b.当前承运商名称, a.客户全称, a.手机, a.单价, a.批号,
a.拣货状态, a.拣货人, a.拣货时间, a.单据状态, a.操作人, a.操作时间, a.存放位置, a.人员编号, a.人员名称, b.司机编码,
b.司机, b.联系电话, b.派车类型, b.车牌号, b.计费方式,b.运费单价,b.车身尺寸,b.始发地,b.目的地,b.总体积 所选总体积, b.运费总额, b.调整差额, b.实际运费, b.备注, b.创建时间,
b.num FID, a.库房, c.FparentBm, b.PKID PKID, a.产品编号, a.出库小号
FROM dbo.dzhw_ackc AS a LEFT OUTER JOIN
dbo.dzhw_pcd AS b ON a.出库小号 = b.出库小号 LEFT OUTER JOIN
dbo.T_Basic_CYS AS c ON b.当前承运商编码 = c.Fcysbh
WHERE (a.承运商 IS NOT NULL)
) a where a.派车单状态 = '已派' and a.单据状态=1 order by a.派车单号 desc,a.派车单状态 asc
#3
UNION ALL 的两个集合不要加order by ，把order by 放到UNION ALL的结合之外