这是我写的一段程序,但有问题.select Type as 类型,工序名称,C.money money, OrderType
From
work_procedure
B inner join
worktype
A on B.workbh=A.workbh
inner join
(select Wpbh,Null as type,工序名称=(select wpname From work_procedure where wpbh=jjgz.wpbh), money, 2 as OrderType
From jjgz
union all
select wpbh,'小计' as type,Null as 工序名称,money,3 as OrderType --把小计写加上去.
From jjgz
Group by wpbhUNION all
Select top 1 wpbh,Null as type ,Null as 工序名称,Null as money, 4 as ordertype --在小计后加上一空行.
From work_procedure
--Union ALL
--SELECT wpbh,workname as type,Null as money,1 as ordertype --在第一行显示 (销售 NULL NULL NULL) 不会写写得有些问题.
--From worktype) C
on B.wpbh=C.wpbhorder by C.wpbh,ordertype
From
work_procedure
B inner join
worktype
A on B.workbh=A.workbh
inner join
(select Wpbh,Null as type,工序名称=(select wpname From work_procedure where wpbh=jjgz.wpbh), money, 2 as OrderType
From jjgz
union all
select wpbh,'小计' as type,Null as 工序名称,money,3 as OrderType --把小计写加上去.
From jjgz
Group by wpbhUNION all
Select top 1 wpbh,Null as type ,Null as 工序名称,Null as money, 4 as ordertype --在小计后加上一空行.
From work_procedure
--Union ALL
--SELECT wpbh,workname as type,Null as money,1 as ordertype --在第一行显示 (销售 NULL NULL NULL) 不会写写得有些问题.
--From worktype) C
on B.wpbh=C.wpbhorder by C.wpbh,ordertype
解决方案 »
- SQL Server2000 主从表外键约束与触发器的疑问
- 求一存储过程
- 100分送,急,SQL Server 2000误更新了数据(update语句)如何用日志恢复操作前的数据??
- 怎么向datetime类型字段插入数值。
- SQL SERVER存储图片
- 我要疯了!上海招SQL SERVRE DBA的太少了!谁介绍工作我给现金!至少500!你定个价!
- 如何用SQL 语句创建 MDB 文件
- 将字段设置成带2位小数有多少种设置方法?
- 强烈建议大家提问前先搜一下老贴子的同类问题,利人利己的 ^_^ 也散点分
- sql十万级数据如何查询优化???
- 如何访问远程的MS_SQLServer!!!!!!!!!!
- 我是一个菜鸟,有一个SQL语句想请教大家
insert @worktype values(1,'销售')
insert @worktype values(2,'生产')declare @work_procedure table(workbh int,wpbh int,wpname varchar(100))
insert @work_procedure values(1,1,'销售1')
insert @work_procedure values(1,2,'销售2')
insert @work_procedure values(2,3,'生产1')declare @jjgz table (personid int,wpbh int,money int)
insert @jjgz values(1,1,30)
insert @jjgz values(1,1,30)
insert @jjgz values(2,2,50)
insert @jjgz values(3,3,100)
select 类型,工序名称,员工号,money from (
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money from @worktype union all
select a.workbh id,2 tid,null 类型,b.wpname 工序名称,c.personid 员工号,c.money from @worktype a join @work_procedure b on a.workbh=b.workbh join @jjgz c on b.wpbh=c.wpbh union all
select a.workbh id,3 tid,'小计' 类型,null 工序名称,null 员工号,sum(c.money) money from @worktype a join @work_procedure b on a.workbh=b.workbh join @jjgz c on b.wpbh=c.wpbh group by a.workbh
) 结果 order by id,tid
销售 NULL NULL NULL
NULL 销售1 1 30
NULL 销售1 1 30
NULL 销售2 2 50
小计 NULL NULL 110
生产 NULL NULL NULL
NULL 生产1 3 100
小计 NULL NULL 100
还少一行空行.销售 NULL NULL NULL
NULL 销售1 1 30
NULL 销售1 1 30
NULL 销售2 2 50
小计 NULL NULL 110
NULL NULL NULL Null
生产 NULL NULL NULL
NULL 生产1 3 100
小计 NULL NULL 100
Null Null Null Null
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money from @worktype union all
select a.workbh id,2 tid,null 类型,b.wpname 工序名称,c.personid 员工号,c.money from @worktype a join @work_procedure b on a.workbh=b.workbh join @jjgz c on b.wpbh=c.wpbh union all
select a.workbh id,3 tid,'小计' 类型,null 工序名称,null 员工号,sum(c.money) money from @worktype a join @work_procedure b on a.workbh=b.workbh join @jjgz c on b.wpbh=c.wpbh group by a.workbh union all
select 100,100,null,null,null,null
) 结果 order by id,tid
不可以是其它的吗? 改为
select 99999,99999,null,null,null,null
也可以的。另外如果我要过滤怎么办。
假设我按
workbh=1 同时
wpbh=2
是不是都得加.
insert @worktype values(1,'销售')
insert @worktype values(2,'生产')declare @work_procedure table(workbh int,wpbh int,wpname varchar(100))
insert @work_procedure values(1,1,'销售1')
insert @work_procedure values(1,2,'销售2')
insert @work_procedure values(2,3,'生产1')declare @jjgz table (personid int,wpbh int,money int)
insert @jjgz values(1,1,30)
insert @jjgz values(1,1,30)
insert @jjgz values(2,2,50)
insert @jjgz values(3,3,100)
select 类型,工序名称,员工号,money from (
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money
from
@worktype union allselect a.workbh id,2 tid,null 类型,b.wpname 工序名称,c.personid 员工号,c.money
from
@worktype
a join
@work_procedure
b on a.workbh=b.workbh join
(select * from @jjgz where personid=3)
c on b.wpbh=c.wpbh union allselect a.workbh id,3 tid,'小计' 类型,null 工序名称,null 员工号,sum(c.money) money
from
@worktype
a join
@work_procedure
b on a.workbh=b.workbh join
(select * from @jjgz where personid=3)c on b.wpbh=c.wpbh
group by a.workbh union all
select 100,100,null,null,null,null
) 结果 order by id,tid得到结果是这样的.
销售 NULL NULL NULL --这一行应该不存在的。怎样去掉
生产 NULL NULL NULL
NULL 生产1 3 100
小计 NULL NULL 100
NULL NULL NULL NULL