select khbh,type as 类型,xkdh as 单号,Summoney as 欠款金额,OrderType
from
(
select khbh,'开单' as type,xkdh as xkdh,Summoney as Summoney,2 as OrderType
From xskdunion all
select khbh,'小计' as type,null as xkdh,sum(summoney) as Summoney,4 as OrderType
from (
select xskd.khbh,sum(isnull(summoney,0)) as summoney
from xskd
group by xskd.khbh) as a
group by khbh
union all
select khbh,khname as type,null as xkdh,null as summoney,1 as OrderType
from ytkhzlb
union all
select khbh,null as type,null as xkdh,null as summoney, 5 as OrderType
from ytkhzlb) as t
order by khbh,OrderType这是我写的那段程序.
大家看看怎么改.
from
(
select khbh,'开单' as type,xkdh as xkdh,Summoney as Summoney,2 as OrderType
From xskdunion all
select khbh,'小计' as type,null as xkdh,sum(summoney) as Summoney,4 as OrderType
from (
select xskd.khbh,sum(isnull(summoney,0)) as summoney
from xskd
group by xskd.khbh) as a
group by khbh
union all
select khbh,khname as type,null as xkdh,null as summoney,1 as OrderType
from ytkhzlb
union all
select khbh,null as type,null as xkdh,null as summoney, 5 as OrderType
from ytkhzlb) as t
order by khbh,OrderType这是我写的那段程序.
大家看看怎么改.
但只要把gysb表改为ytkhzlb
gysbh->khbh, gysname->khname) as a
group by khbh
union all
select khbh,khname as type,null as xkdh,null as summoney,1 as OrderType
from ytkhzlb //问题在这里。他把ytkhzlb里的所有内容都取出来了.
我想用left join,但在这段程序里怎么用。
select 供货单位,单号,sum(总额) from xskd group by 供货单位,单号
with rollup
它会产生一张各种情况的汇总表(可将它做为一个视图),在它的基本可以再细查
select * into #table1 From
(select distinct khbh from xskd)
) b然后在
union all
select khbh,khname as type,null as xkdh,null as summoney,1 as OrderType
from ytkhzlb where khbh in (select khbh from #table1)这种方法可以得到结果。 但是从效率,算法,应该不好。运行了两次.(在
(
select xskd.khbh,sum(isnull(summoney,0)) as summoney
from xskd
group by xskd.khbh) as a运行了一次, 其实这里过滤了。
(gysbh int,
iid char(1),
gysname char(10),
xkdh char(20),
summoney numeric(10,2))insert #temp
select gysb.gysbh, '0', gysb.gysname,'',0 from xskd
left join gysb on gysb.gysbh=xskd.gysbh group by gysb.gysbh, gysb.gysname
insert #temp
select gysbh, '9', '小计','',sum(summoney) from xskd group by gysbh
insert #temp
select gysbh, '1', '开单', 'xk-'+xkdh, summoney from xskd
select gysname,xkdh,summoney from #temp order by gysbh, iid gysname xkdh summoney
---------- -------------------- ------------
张三 .00
开单 xk-2002-12-01-0001 100.00
开单 xk-2002-12-01-0002 300.00
小计 400.00
李四 .00
开单 xk-2002-12-01-0003 50.00
开单 xk-2002-12-01-0004 10.00
小计 60.00
王五 .00
开单 xk-2002-12-01-0005 100.00
小计 100.00(11 row(s) affected)
group by khbh
union all
select khbh,khname as type,null as xkdh,null as summoney,1 as OrderType
from ytkhzlb --〉) as a
group by khbh
union all
select gysname,gysname as type,null as xkdh,null as summoney,1 as OrderType
from gysb
你的表结构和语句对不上,看起来很累。
把gysb改为
ytkhzlb
khbh, khname
1 张三
2 李四
3 王五
4 王六
5 王七