先说一下表的关系 每一个方案有N个费用 费用有两种支付方式 现金(Mtype=3) 产品(Mtype=2) 这些费用可借支 也可报销 在方案中 每个费用是用PID表示 它就是借支从表中的LID和报销从表中的AID 方案中的支付方式也就是借支和报销中的借支类型Ltpye和报销类型Atpye
第一个存储过程的要求是:
查询出满足条件的方案的支付方式为现金(Mtype=3)的可借支现金的总金额 和 支付方式为产品(Mtype=2)可借支的产品的总金额 查询关键字是TextBox里的字符串 是用户输入的方案编号 当然也可以为空(即查询出全满足条件的全部方案)满足条件的方案的意思就是Pstatus=1
可借支的现金的总金额=方案的费用支付方式为“现金”的总费用-(借支方式为“现金”的总借支金额+报销方式为“现金”的总报销金额)可借支的产品的总金额=方案的费用支付方式为“产品”的总费用-(借支方式为“产品”的总借支金额+报销方式为“产品”的总报销金额)
这是第一个需要的存储过程 我估计大家都懵了吧 呵呵
第一个存储过程的要求是:
查询出满足条件的方案的支付方式为现金(Mtype=3)的可借支现金的总金额 和 支付方式为产品(Mtype=2)可借支的产品的总金额 查询关键字是TextBox里的字符串 是用户输入的方案编号 当然也可以为空(即查询出全满足条件的全部方案)满足条件的方案的意思就是Pstatus=1
可借支的现金的总金额=方案的费用支付方式为“现金”的总费用-(借支方式为“现金”的总借支金额+报销方式为“现金”的总报销金额)可借支的产品的总金额=方案的费用支付方式为“产品”的总费用-(借支方式为“产品”的总借支金额+报销方式为“产品”的总报销金额)
这是第一个需要的存储过程 我估计大家都懵了吧 呵呵
create proc 存储过程名
@a varchar(10) --这里是些参数
as
begin
.........
end--如果你能在查询分析器中查询到你要的结果,按格式放在存储过程里就可以了。
调用时:
exec 存储过程名 参数
在查询分析器中
create proc aaa
@str varchar(5)
as
begin
select * from 表 where 字段=@str
end---
这样的话,如果你调用存储过程
exec aaa 'abc'的执行结果,就等价与
select * from 表 where 字段='abc'不知道你明白了吗?
--方案主、从表
--主表 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.Ltpyeselect a.atpye,Amoney=sum(b.amoney)
into #at
from Amaster a inner join Adetail b on a.Acode=b.Acode
group by a.atpyeselect 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=@adrop table Pmaster,Pdetail,Lmaster,Ldetail,Amaster,Adetail,#pt,#lt,#at,#t--运行结果:
Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152是要这样的结果吗?
create proc Pname
@Pcode varchar(5)
as
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.Ltpyeselect a.atpye,Amoney=sum(b.amoney)
into #at
from Amaster a inner join Adetail b on a.Acode=b.Acode
group by a.atpyeselect 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=@Pcodedrop table #pt,#lt,#at,#t--调用
exec Pname 'P001'--运行结果:
Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152
按你的数据的话,Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152
对还是不对?如果结果正确的话,那你再改一下存储过程,刚没注意你的
查询关键字是TextBox里的字符串 是用户输入的方案编号 当然也可以为空(即查询出全满足条件的全部方案)
--修改如下:
create proc Pname
@Pcode varchar(5)
as
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
if @Pcode<>''
begin
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=@Pcode
end
if @Pcode=''
begin
select Pcode
,可借支的现金的总金额=sum(case mtype when 2 then 金额 else 0 end)
,可借支的产品的总金额=sum(case mtype when 3 then 金额 else 0 end)
from #t
group by Pcode
end
drop table #pt,#lt,#at,#t这样的话,你可以这么调用:
exec pname 'p001' --查询p001的结果
exec pname ''--查询所有满足条件的记录。--当然这个''或者'P001'应该是来自你的TextBox--如果运行结果根本不符合你的要得到的结果,那就.........一切是扯淡了,呵呵
Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152其中的第2列就是现金的,第2列是产品的啊。这个就是通过Mtype来区分的
不知道你要什么的格式啊
难道是:P001 2 922
P001 3 1152这样的格式?
Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152
create proc Pname
@Pcode varchar(5)
as
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
if @Pcode<>''
begin
select Pcode=max(Pcode),mtype,
金额=sum(金额)
from #t
where Pcode=@Pcode
group by mtype
end
if @Pcode=''
begin
select Pcode,mtype,
金额=sum(金额)
from #t
group by Pcode,mtype
order by Pcode,mtype
end
drop table #pt,#lt,#at,#t--调用
exec pname 'P001'--结果:Pcode mtype 金额
----- ----- -----------
P001 2 922
P001 3 1152
--那你注意一下这里:
select Pcode=max(Pcode)
,可借支的现金的总金额=sum(case mtype when 2 then 金额 else 0 end)
--上面的mtype为2的汇总,也就是“现金”的
,可借支的产品的总金额=sum(case mtype when 3 then 金额 else 0 end)
--同理,上面的 mtype为3的汇总,也就是“产品”的
from #t
declare @a varchar(20)
set @a='P20040804003'
select distinct a.projectCode,a.projectName,b.payModeCode,总共批准的金额=sum(b.ratifyMoney)
into ppt
from TP_ProjectMaster a inner join TP_ProjectDetail b
on a.projectCode = b.projectCode
where a.projectCode=@a
and a.ratifyStatusCode='3'
and (a.executeStatusCode=0 or a.executeStatusCode=1)
group by b.payModeCode,a.projectCode,a.projectName
select distinct a.loanType,总共借支的金额=sum(b.loanMoney)
into llt
from TL_LoanMaster a inner join TL_LoanMoneyDetail b on a.loanCode=b.loanCode
group by a.loanTypeselect a.accountType,总共报销的金额=sum(b.accountMoney)
into aat
from TA_AccountMaster a inner join TA_AccountMoneyDetail b on a.accountCode=b.accountCode
group by a.accountTypeselect a.projectCode,a.payModeCode,金额=a.总共批准的金额-b.总共借支的金额-c.总共报销的金额
into ttt
from ppt a inner join llt b on a.payModeCode=b.loanType inner join aat c on a.payModeCode=c.accountType
select projectCode=max(projectCode)
,可借支的现金的总金额=sum(case payModeCode when 2 then 金额 else 0 end)
,可借支的产品的总金额=sum(case payModeCode when 3 then 金额 else 0 end)
from ttt
where Pcode=@adrop table ppt,llt,aat,ttt在结果表ppt中 只有这么一行 projectCode projectName payModeCode 总共批准的金额
P20040804003 商超费用 3 14900
好象没有出现您说的这个结果啊--运行结果:
Pcode 可借支的现金的总金额 可借支的产品的总金额
----- ----------- -----------
P001 922 1152
我是按你的type连接的。
--你先把
select a.projectCode,a.payModeCode,金额=a.总共批准的金额-b.总共借支的金额-c.总共报销的金额
into ttt
from ppt a inner join llt b on a.payModeCode=b.loanType inner join aat c on a.payModeCode=c.accountType更改成:
select a.projectCode,a.payModeCode,金额=a.总共批准的金额-b.总共借支的金额-c.总共报销的金额
into ttt
from ppt a left join llt b on a.payModeCode=b.loanType inner join aat c on a.payModeCode=c.accountType也就是把 inner join-->left join
看看有什么结果,你发过来看看。估计有的列会是 空。
@projectcode varchar(20)
as
select distinct a.projectCode,b.payModeCode,[ratifyMoney]=sum(b.[ratifyMoney])
into #pt
from TP_ProjectMaster a inner join TP_ProjectDetail b on a.projectCode=b.projectCode
where a.ratifyStatusCode='3' and (a.executeStatusCode=0 or a.executeStatusCode=1)
group by b.payModeCode,a.projectCode
select distinct a.loanType,loanMoney=sum(b.loanMoney)
into #lt
from TL_LoanMaster a inner join TL_LoanMoneyDetail b on a.loanCode=b.loanCode
group by a.loanCode select a.accountType,accountMoney=sum(b.accountMoney)
into #at
from TA_AccountMaster a inner join TA_AccountMoneyDetail b on a.accountCode=b.accountCode
group by a.accountType select a.projectCode,a.payModeCode,金额=a.ratifyMoney-b.loanMoney-c.accountMoney
into #t
from #pt a inner join #lt b on a.payModeCode=b.loanType inner join #at c on a.payModeCode=c.accountType
if @projectcode<>''
begin
select projectCode=max(projectCode),payModeCode,
金额=sum(金额)
from #t
where projectCode=@projectcode
group by payModeCode
end
if @projectcode<>''
begin
select projectCode,payModeCode,
金额=sum(金额)
from #t
group by payModeCode,payModeCode
order by payModeCode,payModeCode
end drop table #pt,#lt,#at,#t
exec Test_GetAllCanLoanProject 'P20040804003'
出错信息为:服务器: 消息 169,级别 15,状态 2,过程 Test_GetAllCanLoanProject,行 41
在排序依据列表中多次指定了某一列。排序依据列表中的列必须是唯一的。
order by payModeCode,payModeCode
---》
group by payModeCode
order by payModeCode--你到底是要哪个格式?看准数据
projectCode projectName canloanmoney canloanproduct
P20040802001 广西3季度方案 3469 4523
P20040804002 2 2391
请问要如何才能得到这样的格式: 方案编号 方案名称 可借支的现金 可借支的产品
projectCode projectName canloanmoney canloanproduct
P20040802001 广西3季度方案 3469 4523
@projectcode varchar(20)
as
select distinct a.projectCode,b.payModeCode,ratifyMoney=sum(b.ratifyMoney)
into #pt
from TP_ProjectMaster a inner join TP_ProjectDetail b on a.projectCode=b.projectCode
where a.ratifyStatusCode='3' and (a.executeStatusCode=0 or a.executeStatusCode=1)
group by b.payModeCode,a.projectCode
select distinct a.loanType,loanMoney=sum(b.loanMoney)
into #lt
from TL_LoanMaster a inner join TL_LoanMoneyDetail b on a.loanCode=b.loanCode
group by a.loanType select a.accountType,accountMoney=sum(b.accountMoney)
into #at
from TA_AccountMaster a inner join TA_AccountMoneyDetail b on a.accountCode=b.accountCode
group by a.accountType select a.projectCode,a.payModeCode,金额=a.ratifyMoney-b.loanMoney-c.accountMoney
into #t
from #pt a inner join #lt b on a.payModeCode=b.loanType inner join #at c on a.payModeCode=c.accountType
if @projectcode<>''
begin
select projectCode=max(projectCode),payModeCode,
金额=sum(金额)
from #t
where projectCode=@projectcode
group by payModeCode
end
if @projectcode=''
begin
select projectCode,payModeCode,
金额=sum(金额)
from #t
group by projectCode,payModeCode
order by projectCode,payModeCode
end drop table #pt,#lt,#at,#t
drop proc Test_GetAllCanLoanProject
exec Test_GetAllCanLoanProject 'P20040804002'
我怀疑是数据的问题,也就是inner join时,连接时,有的表没数据,就查不出来的。
类似下面的这个
存储过程:
create proc Pname
@Pcode varchar(5)
as
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.Ltpyeselect a.atpye,Amoney=sum(b.amoney)
into #at
from Amaster a inner join Adetail b on a.Acode=b.Acode
group by a.atpyeselect 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=@Pcodedrop table #pt,#lt,#at,#t
[email protected]
谢谢,很想多一些朋友,在技术方面能多多指导我!
P20040804002 2 2391真的 现在我就想把它变成下面这样就OK了 projectCode 可借现金金额 可借产品金额
P20040804002 5679 2391请问小二老大 如何在这个的基础上改进一下:
create proc Test_GetAllCanLoanProject
@projectcode varchar(20)
as
select distinct a.projectCode,b.payModeCode,ratifyMoney=sum(b.ratifyMoney)
into #pt
from TP_ProjectMaster a inner join TP_ProjectDetail b on a.projectCode=b.projectCode
where a.ratifyStatusCode='3' and (a.executeStatusCode=0 or a.executeStatusCode=1)
group by b.payModeCode,a.projectCode
select distinct a.loanType,loanMoney=sum(b.loanMoney)
into #lt
from TL_LoanMaster a inner join TL_LoanMoneyDetail b on a.loanCode=b.loanCode
group by a.loanType select a.accountType,accountMoney=sum(b.accountMoney)
into #at
from TA_AccountMaster a inner join TA_AccountMoneyDetail b on a.accountCode=b.accountCode
group by a.accountType select a.projectCode,a.payModeCode,金额=a.ratifyMoney-b.loanMoney-c.accountMoney
into #t
from #pt a inner join #lt b on a.payModeCode=b.loanType inner join #at c on a.payModeCode=c.accountType
if @projectcode<>''
begin
select projectCode=max(projectCode),payModeCode,
金额=sum(金额)
from #t
where projectCode=@projectcode
group by payModeCode
end
if @projectcode=''
begin
select projectCode,payModeCode,
金额=sum(金额)
from #t
group by projectCode,payModeCode
order by projectCode,payModeCode
end drop table #pt,#lt,#at,#t
drop proc Test_GetAllCanLoanProject
exec Test_GetAllCanLoanProject 'P20040802001'
@projectcode varchar(20)
as
select distinct a.projectCode,b.payModeCode,ratifyMoney=sum(b.ratifyMoney)
into #pt
from TP_ProjectMaster a inner join TP_ProjectDetail b on a.projectCode=b.projectCode
where a.ratifyStatusCode='3' and (a.executeStatusCode=0 or a.executeStatusCode=1)
group by b.payModeCode,a.projectCode
select distinct a.loanType,loanMoney=sum(b.loanMoney)
into #lt
from TL_LoanMaster a inner join TL_LoanMoneyDetail b on a.loanCode=b.loanCode
group by a.loanType select a.accountType,accountMoney=sum(b.accountMoney)
into #at
from TA_AccountMaster a inner join TA_AccountMoneyDetail b on a.accountCode=b.accountCode
group by a.accountType select a.projectCode,a.payModeCode,金额=a.ratifyMoney-b.loanMoney-c.accountMoney
into #t
from #pt a inner join #lt b on a.payModeCode=b.loanType inner join #at c on a.payModeCode=c.accountType
if @projectcode<>''
begin
select projectCode=max(projectCode),payModeCode
,可借现金金额 =sum(case mtype when 2 then 金额 else 0 end)
,可借产品金额=sum(case mtype when 3 then 金额 else 0 end)
from #t
where projectCode=@projectcode
group by payModeCode
end
if @projectcode=''
begin
select projectCode,payModeCode
,可借现金金额 =sum(case mtype when 2 then 金额 else 0 end)
,可借产品金额=sum(case mtype when 3 then 金额 else 0 end)
from #t
group by projectCode,payModeCode
order by projectCode,payModeCode
end drop table #pt,#lt,#at,#t --试试吧,其实,你应该在最开始我写的那个存储过程修改的,呵呵。
projectCode payModeCode 金额P20040804002 3 5679
P20040804002 2 2391真的 现在我就想把它变成下面这样就OK了 projectCode 可借现金金额 可借产品金额
P20040804002 5679 2391--测试:create table tb(projectCode varchar(20),payModeCode varchar(1), 金额 int)
insert into tb
select 'P20040804002','3',5679 union all
select 'P20040804002' ,'2',2391
select * from tb --原始数据
--所要的结果:
select projectCode,
可借现金金额=sum(case payModeCode when 3 then 金额 else 0 end )
,可借产品金额=sum(case payModeCode when 2 then 金额 else 0 end )
from tb
group by projectCodedrop table tb
--运行结果:
projectCode payModeCode 金额
-------------------- ----------- -----------
P20040804002 3 5679
P20040804002 2 2391(所影响的行数为 2 行)projectCode 可借现金金额 可借产品金额
-------------------- ----------- -----------
P20040804002 5679 2391(所影响的行数为 1 行)