select 类型,工序名称,员工号,money from (
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money from @worktype where workbh in (select Distinct workbh from @work_procedure where wpbh in (select Distinct wpbh from @jjgz where personid=3)) 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 (select * from @jjgz where personid=3)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 (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
解决方案 »
- 查询不重复的记录值
- 几个sql问题
- sql server 2005 service pack3更新装不上
- SQLSERVER千万级数据查询, 求优化方法?
- 如何把某个字段中的所有值全部导入另一个字段中
- 请问如何用存储过程创建SQL作业啊?得到答案便结帖..谢谢各位高手
- 在存储过程中,给output 参数传值 ,新手,请帮忙
- hepl me 关于sql server 数据库恢复的问题
- 关于ORACLE的简单问题,谁能帮我???
- 急急急急急急急急急急急急急急急急急急急急急急急急急急急急急急急!
- 请问: 如何对年龄分段查询 如20-30,30-40,40-50 并统计个段个数
- 关于 《如何提取最新的记录?》
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 where workbh in (select Distinct workbh from @work_procedure where wpbh in (select Distinct wpbh from @jjgz where personid=3)) 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 (select * from @jjgz where personid=3)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 (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
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money from @worktype where workbh in (select Distinct workbh from @work_procedure where wpbh in (select Distinct wpbh from @jjgz where personid=3)) 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 c.personid=3 and 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 c.personid=3 and b.wpbh=c.wpbh group by a.workbh union all
select 100,100,null,null,null,null
) 结果 order by id,tid
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
where workbh in
(select Distinct workbh from @work_procedure where wpbh in
(select Distinct wpbh from @jjgz
where personid=3
))
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
c.personid=3
and 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
c.personid=3
and b.wpbh=c.wpbh group by a.workbh
union all
select 100,100,null,null,null,null
) 结果 order by id,tid谢谢大力。
如果再加上条件按工种编号、工序编号过滤,应该加在哪个位置。
select workbh id,1 tid,workname 类型,null 工序名称,null 员工号,null money
from
@worktype
where workbh in
(select Distinct workbh from
@work_procedure
where wpbh in
(select Distinct wpbh from @jjgz
where personid=3
)
)
union all
select a.workbh id,2 tid,null 类型,b.wpname 工序名称,c.personid 员工号,c.money
from
@worktype
a join
--(select * from work_procedure where workbh=1)//替换@work_procedure
@work_procedure
b on a.workbh=b.workbh join
@jjgz
c on
c.personid=3
and b.wpbh=c.wpbh
union all
select a.workbh id,3 tid,'小计' 类型,null 工序名称,null 员工号,sum(c.money) money
from
@worktype
a join
--(select * from work_procedure where workbh=1)
@work_procedure
b on a.workbh=b.workbh
join
@jjgz c on
c.personid=3
and b.wpbh=c.wpbh group by a.workbh
union all
select 100,100,null,null,null,null
) 结果
order by id,tid
--(select * from work_procedure where workbh=1)//替换@work_procedure
这样写没有错吧 .结果为生产 NULL NULL NULL
NULL NULL NULL NULL