select b.huowname as '部门名称',a.chkje as '销售出库成本',
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/a.chkje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chkje-ml) as chkje
from splsk
where chkje >0 group by hw) a ,huoweizl b,hwsp c
where a.hw=c.hw
and a.hw=b.hw
and a.hw='HWI00000001'
group by b.huowname,a.chkje
order by b.huowname
union
select a.huowname as '部门名称',b.chbje as '销售出库成本',
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/b.chbje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chbje) as chbje
from retmxls
where rq>='2008-04-01'
and rq<='2008-04-10'
and chbje>0
group by hw) b,huoweizl a,hwsp c
where a.hw=b.hw
and b.hw=c.hw
group by a.huowname,b.chbje
order by a.huowname
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/a.chkje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chkje-ml) as chkje
from splsk
where chkje >0 group by hw) a ,huoweizl b,hwsp c
where a.hw=c.hw
and a.hw=b.hw
and a.hw='HWI00000001'
group by b.huowname,a.chkje
order by b.huowname
union
select a.huowname as '部门名称',b.chbje as '销售出库成本',
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/b.chbje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chbje) as chbje
from retmxls
where rq>='2008-04-01'
and rq<='2008-04-10'
and chbje>0
group by hw) b,huoweizl a,hwsp c
where a.hw=b.hw
and b.hw=c.hw
group by a.huowname,b.chbje
order by a.huowname
from splsk
?
--使用union或者union all进行连接时,语句不能进行排序
select b.huowname as '部门名称',a.chkje as '销售出库成本',
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/a.chkje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chkje-ml) as chkje
from splsk
where chkje >0 group by hw) a ,huoweizl b,hwsp c
where a.hw=c.hw
and a.hw=b.hw
and a.hw='HWI00000001'
group by b.huowname,a.chkje
--order by b.huowname
union
select a.huowname as '部门名称',b.chbje as '销售出库成本',
cast(sum(c.hwje) as decimal(10,2)) as '现库存金额',
cast(cast(sum(c.hwje)/b.chbje as decimal(10,3))*
(datediff(day,'2008-04-01','2008-04-10')) as int(8)) as '周转天数','本期'as '本期'
from (select hw,sum(chbje) as chbje
from retmxls
where rq>='2008-04-01'
and rq <='2008-04-10'
and chbje>0
group by hw) b,huoweizl a,hwsp c
where a.hw=b.hw
and b.hw=c.hw
group by a.huowname,b.chbje
--order by a.huowname
前面的有order by
后面还有order by
..........
--如果一定要排序,可以在外面进行排序,如:
select * from
(select * from a
union
select * from b)m
order by huowname
--正确写法:
select * from
(select * from a
union
select * from b)m
order by huowname
--错误写法:
select * from a
order by huowname
union
select * from b
order by huowname
union all
select col2 as '我要排名' from b
order by '我要排名'
pt正解
不用在外面套select
看8楼
不要光想着抢分
看你这句"--使用union或者union all进行连接时,语句不能进行排序"是多么的误人子弟
union 既然合成一个,
保留最后一个order by 就行了