CSDN博客

img zhangzs8896

一个简单的sql存储过程

发表于2004/9/19 15:48:00  545人阅读

分类: 数据库技术

来自:http://community.csdn.net/Expert/topic/3381/3381415.xml?temp=.4722254

--测试:
--方案主、从表
--主表 P-master
create table Pmaster( Pcode VARCHAR(5),Pname varchar(5),Pstatus varchar(1))
insert into Pmaster
select 'P001','PA','1' UNION ALL
select 'P002','PB','0'
--从表 P-detail
create table Pdetail(Pcode  varchar(5), PID varchar(5), Mcode varchar(5),Mtype varchar(1),[Money] int)
insert into Pdetail
select 'P001','01','m1','2',560 UNION ALL
select 'P001','02','m2','3',480 UNION ALL
select 'P001','03','m3','2',360 UNION ALL
select 'P001','04','m4','3',670


--借支主、从表
--主表  Lmaster
create table  Lmaster(Lcode varchar(5),Pcode VARCHAR(4),Ltpye VARCHAR(1))
INSERT INTO Lmaster
select 'L001','P001','2' union all                               
select  'L002','P001','3' union all   
select  'L003','P001','2' union all 
select  'L004','P001','3'                 
--从表  Ldetail
create table Ldetail(Lcode varchar(5),LID varchar(5),Lmoney int )
insert into Ldetail
select 'L001','01', 51 union all
select 'L001','02', 41 union all
select 'L002','03', 31 union all
select 'L002','04', 61
--报销主、从表
--主表  Amaster
create table Amaster(Acode varchar(5),Pcode varchar(5),Atpye varchar(1))
insert into Amaster
select 'A001','P001','2' union all                               
select 'A002','P001','3' union all  
select 'A003','P001','2' union all
select 'A004','P001','3'                 
--从表  Adetail 
create table Adetail(Acode varchar(5),AID varchar(5),Amoney int)
insert into Adetail
select 'A001','01', 52 union all  
select 'A001','02', 42 union all
select 'A002','03', 32 union all
select 'A002','04', 62 

--查询
declare @a varchar(5)
set @a='P001'                  
select distinct a.Pcode,b.mtype,[money]=sum(b.[money])
into #pt
from Pmaster a inner join Pdetail b on a.Pcode=b.Pcode
where a.Pstatus='1'
group by b.mtype,a.Pcode
select distinct a.Ltpye,lmoney=sum(b.lmoney)
into #lt
from lmaster a inner join ldetail b on a.Lcode=b.Lcode
group by a.Ltpye

select a.atpye,Amoney=sum(b.amoney)
into #at
from Amaster a inner join Adetail b on a.Acode=b.Acode
group by a.atpye

select a.Pcode,a.mtype,金额=a.money-b.lmoney+c.amoney
into #t
from #pt a inner join #lt b on a.mtype=b.Ltpye inner join #at c on a.mtype=c.atpye
      
select Pcode=max(Pcode)
       ,可借支的现金的总金额=sum(case mtype when 2 then 金额 else 0 end)
       ,可借支的产品的总金额=sum(case mtype when 3 then 金额 else 0 end)
from #t  
where Pcode=@a

drop table  Pmaster,Pdetail,Lmaster,Ldetail,Amaster,Adetail,#pt,#lt,#at,#t

--运行结果:

Pcode 可借支的现金的总金额  可借支的产品的总金额 
----- ----------- -----------
P001  922         1152

 

0 0

相关博文

我的热门文章

img
取 消
img