如果你的金钱库(t_money)表的顺序是部门在前,员工在后的话,就可以简化很多
你试试下面的语句select min(m_key) as m_key,a.id,max(b.dept) as name,sum(cmoney) as money
from t_money a inner join t_dept b on a.id=b.id
group by a.id
union
select min(m_key) as m_key,a.id,max(name),sum(cmoney) as money
from t_money a inner join t_emp b on a.id=b.id
group by a.id
order by id,m_key
你试试下面的语句select min(m_key) as m_key,a.id,max(b.dept) as name,sum(cmoney) as money
from t_money a inner join t_dept b on a.id=b.id
group by a.id
union
select min(m_key) as m_key,a.id,max(name),sum(cmoney) as money
from t_money a inner join t_emp b on a.id=b.id
group by a.id
order by id,m_key
解决方案 »
- 求存储过程
- 高手们日期时间的加减法怎么做
- 数据库中为什么会有sleeping 的链接!
- ip地址大小比较
- 传输层中遇到错误 SSAS & Excel 2007
- 请问如何进行级联插入?
- sql small question
- 未与信任 SQL Server 连接相关联
- 在配置SQL SERVER 数据源时,当输入登录ID和密码时,出现如下的错误,请帮忙
- 关于判断的问题:假如在form1里有Edit1、Edit2和Edit3,如果是查询,有7种情况如下:
- SQL Server2000中表示整型和日期类型的模糊查询符号是什么?急........
- ★★★★还是关于取得所有记录中的最大和最小的问题!(二问)
Select * from(
Select c.id,a.dept,'' as name,sum(c.cmoney) as cmoney
from t_money c join t_dept a on c.id = a.dept_id
group by c.id,a.dept,''
Union all
Select c.id,a.dept,b.name as name,sum(IsNull(c.cmoney,0)) as cmoney
from t_money c join t_emp b on c.id = b.id
join t_dept a on b.dept_id = a.id
group by c.id,a.dept,b.name
) tmp
order by dept,id
insert into t_money values(1,'d1',1000)
insert into t_money values(2,'d1',1500)
insert into t_money values(3,'d2',2000)
insert into t_money values(4,'d3',3000)
insert into t_money values(5,'e1',100)
insert into t_money values(6,'e1',200)
insert into t_money values(7,'e2',300)
insert into t_money values(8,'e2',400)
insert into t_money values(9,'e3',500)
insert into t_money values(9,'e3',600)create table t_emp(id nvarchar(2),dept_id nvarchar(2),name nvarchar(10))insert into t_emp values('e1','d1','张三')
insert into t_emp values('e2','d1','李四')
insert into t_emp values('e3','d2','王五')
insert into t_emp values('e4','d2','赵六')
insert into t_emp values('e5','d3','李强')
insert into t_emp values('e6','d3','谢安')create table t_dept(id nvarchar(2),dept nvarchar(5))insert into t_dept values('d1','采购')
insert into t_dept values('d2','计划')
insert into t_dept values('d3','财务')select a.id,max(b.dept) as name,
sum(a.cmoney) as money into #temp
from t_money a inner join t_dept b on a.id=b.id
group by a.iddeclare @id nvarchar(2)
create table #temp1(New_ID int IDENTITY(1,1),id nvarchar(2),
name nvarchar(10),money int)
declare temp_cur cursor for select id from #tempOPEN temp_curFETCH NEXT FROM temp_cur
INTO @idWHILE @@FETCH_STATUS = 0
begin
insert into #temp1(id,name,money)
select id,name,money from #temp where id=@id insert into #temp1
select b.id,max(name),sum(isnull(cmoney,0))
from t_money a right join t_emp b on a.id=b.id inner join t_dept c
on c.id=b.dept_id
where c.id=@id
group by b.id FETCH NEXT FROM temp_cur
INTO @id
endclose temp_cur
deallocate temp_curselect * from #temp1 order by New_ID drop table #temp1
drop table #temp
drop table t_emp
drop table t_dept
drop table t_money
select id,dept,(select sum(cmoney) from t_money where id=t_dept.id) [money],0 flag1,id flag2 from t_dept
union all
select distinct '--','-----',null,1,id from t_dept
union all
select id,name,(select sum(cmoney) from t_money where id=t_emp.id),2,dept_id from t_emp
union all
select distinct null,null,null,3,id from t_dept
) tem order by flag2,flag1
1 d1 采购 2500
2 e1 张三 300
3 e2 李四 700
4 d2 计划 2000
5 e3 王五 1100
6 e4 赵六 0
7 d3 财务 3000
8 e5 李强 0
9 e6 谢安 0
select b.sortid,编号=a.id,名称=b.dept,金额=sum(cmoney)
from t_money a inner join (
select *,sortid=id from t_dept
union all
select id,name,dept_id+'_'+id from t_emp
) b on a.id=b.id
group by a.id,b.dept,b.sortid
) a order by sortid
select 编号,名称,金额 from(
select b.id1,编号=a.id,名称=b.dept,金额=sum(cmoney)
from t_money a inner join (
select *,id1=id from t_dept
union all
select id,name,dept_id from t_emp
) b on a.id=b.id
group by a.id,b.dept,b.id1
) a order by id1,编号
insert into @t_dept
select 'd1','采购'
union all select 'd2','计划'
union all select 'd3','财务'declare @t_emp table(id varchar(2),dept_id varchar(2),name varchar(10))
insert into @t_emp
select 'e1','d1','张三'
union all select 'e2','d1','李四'
union all select 'e3','d2','王五'
union all select 'e4','d2','赵六'
union all select 'e5','d3','李强'
union all select 'e6','d3','谢安'declare @t_money table(m_key int,id varchar(2),cmoney int)
insert into @t_money
select 1,'d1',1000
union all select 2,'d1',1500
union all select 3,'d2',2000
union all select 4,'d3',3000
union all select 5,'e1',100
union all select 6,'e1',200
union all select 7,'e2',300
union all select 8,'e2',400
union all select 9,'e3',500
union all select 10,'e3',600select 编号,名称,金额 from(
select b.id1,编号=a.id,名称=b.dept,金额=sum(cmoney)
from @t_money a inner join (
select *,id1=id from @t_dept
union all
select id,name,dept_id from @t_emp
) b on a.id=b.id
group by a.id,b.dept,b.id1
) a order by id1,编号select 编号,名称,金额 from(
select b.sortid,编号=a.id,名称=b.dept,金额=sum(cmoney)
from @t_money a inner join (
select *,sortid=id from @t_dept
union all
select id,name,dept_id+'_'+id from @t_emp
) b on a.id=b.id
group by a.id,b.dept,b.sortid
) a order by sortid
insert into #t_money values(1,'d1',1000)
insert into #t_money values(2,'d1',1500)
insert into #t_money values(3,'d2',2000)
insert into #t_money values(4,'d3',3000)
insert into #t_money values(5,'e1',100)
insert into #t_money values(6,'e1',200)
insert into #t_money values(7,'e2',300)
insert into #t_money values(8,'e2',400)
insert into #t_money values(9,'e3',500)
insert into #t_money values(9,'e3',600)create table #t_emp(id nvarchar(2),dept_id nvarchar(2),name nvarchar(10))insert into #t_emp values('e1','d1','张三')
insert into #t_emp values('e2','d1','李四')
insert into #t_emp values('e3','d2','王五')
insert into #t_emp values('e4','d2','赵六')
insert into #t_emp values('e5','d3','李强')
insert into #t_emp values('e6','d3','谢安')create table #t_dept(id nvarchar(2),dept nvarchar(5))insert into #t_dept values('d1','采购')
insert into #t_dept values('d2','计划')
insert into #t_dept values('d3','财务')select id,dept,[money] from (
select id,dept,(select cast(isnull(sum(cmoney),0) as varchar(10)) from #t_money where id=#t_dept.id) [money],0 flag1,id flag2 from #t_dept
union all
select distinct '--','-----','----',1,id from #t_dept
union all
select id,name,(select cast(isnull(sum(cmoney),0) as varchar(10)) from #t_money where id=#t_emp.id),2,dept_id from #t_emp
union all
select distinct '','','',3,id from #t_dept
) tem order by flag2,flag1
go
drop table #t_emp
drop table #t_dept
drop table #t_money---------------------------结果:
d1 采购 2500
-- ----- ----
e1 张三 300
e2 李四 700
d2 计划 2000
-- ----- ----
e3 王五 1100
e4 赵六 0
d3 财务 3000
-- ----- ----
e5 李强 0
e6 谢安 0
(所影响的行数为 15 行)