一句话:select type as 类型,jhdh as 单号,dj as 单价,sl as 数量,ze as 总额
from
(
select j.gysbh,'进货' as type,j.jhdh,dj,sl,dj*sl as ze, 2 as OrderType
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
union all
select t.gysbh,'退货' as type,t.thdh,dj,sl,dj*sl as ze, 3 as OrderType
from thmxb m,thdb t
where m.thdh=t.thdh
union all
select gysbh,'小计' as type,null as thdh,null as dj,null as sl,sum(ze) as ze, 4 as OrderType
from (
select j.gysbh,sum(dj*sl) as ze
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
group by j.gysbh
union all
select t.gysbh,sum(dj*sl) as ze
from thmxb m,thdb t
where m.thdh=t.thdh
group by t.gysbh
) as a
group by gysbh
union all
select gysbh,gysname as type,null as thdh,null as dj,null as sl,null as ze,1 as OrderType
from gysb
union all
select gysbh,null as type,null as thdh,null as dj,null as sl,null as ze, 5 as OrderType
from gysb
) as t
order by gysbh,OrderType
from
(
select j.gysbh,'进货' as type,j.jhdh,dj,sl,dj*sl as ze, 2 as OrderType
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
union all
select t.gysbh,'退货' as type,t.thdh,dj,sl,dj*sl as ze, 3 as OrderType
from thmxb m,thdb t
where m.thdh=t.thdh
union all
select gysbh,'小计' as type,null as thdh,null as dj,null as sl,sum(ze) as ze, 4 as OrderType
from (
select j.gysbh,sum(dj*sl) as ze
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
group by j.gysbh
union all
select t.gysbh,sum(dj*sl) as ze
from thmxb m,thdb t
where m.thdh=t.thdh
group by t.gysbh
) as a
group by gysbh
union all
select gysbh,gysname as type,null as thdh,null as dj,null as sl,null as ze,1 as OrderType
from gysb
union all
select gysbh,null as type,null as thdh,null as dj,null as sl,null as ze, 5 as OrderType
from gysb
) as t
order by gysbh,OrderType
修改:
select type as 类型,jhdh as 单号,dj as 单价,sl as 数量,ze as 总额
from
(
select j.gysbh,'进货' as type,j.jhdh,dj,sl,dj*sl as ze, 2 as OrderType
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
AND jdate='2002-5-2'
AND J.gysbh=1
union all
select t.gysbh,'退货' as type,t.thdh,dj,sl,dj*sl as ze, 3 as OrderType
from thmxb m,thdb t
where m.thdh=t.thdh
AND Tdate='2002-5-2'
AND T.gysbh=1
union all
select gysbh,'小计' as type,null as thdh,null as dj,null as sl,sum(ze) as ze, 4 as OrderType
from (
select j.gysbh,sum(dj*sl) as ze
from jhmxb m,jhdb j
where m.jhdh=j.jhdh
AND jdate='2002-5-2'
AND J.gysbh=1
group by j.gysbh
union all
select t.gysbh,sum(dj*sl) as ze
from thmxb m,thdb t
where m.thdh=t.thdh
AND Tdate='2002-5-2'
AND T.gysbh=1
group by t.gysbh
) as a
group by gysbh
union all
select gysbh,gysname as type,null as thdh,null as dj,null as sl,null as ze,1 as OrderType
from gysb
WHERE gysbh=1
union all
select gysbh,null as type,null as thdh,null as dj,null as sl,null as ze, 5 as OrderType
from gysb
WHERE gysbh=1
) as t
order by gysbh,OrderType
An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
select t.gysbh,'退货' as type,t.thdh,dj,sl,dj*sl as ze, 3 as OrderType
from thmxb m,thdb t
where m.thdh=t.thdh
AND Tdate='2002-5-2' //这里,我把这改为thdate=就行了。
AND T.gysbh=1
union all