现一共有六个表,字段类型设置如下:
tadmin:(管理员表)
admin_id varchar 3
admin_account varchar 15
admin_psd varchar 30
admin_auth varchar 30
true_name varchar 30
tclient:(客户表)
Client_ID varchar 6
Client_Name varchar 15
Client_Psw varchar 50
Client_Contact varchar 50
Client_Account money 8
True_Name varchar 15tpay:(交费表)
Pay_ID varchar 50
Client_ID varchar 6
Admin_ID varchar 3
Pay_Money money 8
paytime datetime 8
tsource:(资源表)
Source_ID varchar 50
Source_Name varchar 50
Source_URL varchar 50
Source_Memo varchar 50
Source_Price money 8
Type_ID varchar 50
Admin_ID varchar 3
Up_Date datetime 8
ttype:(资源类型表)
Type_ID varchar 50
Type_name varchar 15tvod:(点播表)
VOD_ID varchar 50
Client_ID varchar 6
Source_ID varchar 50
vodtime datetime 8编写存储过程 要实现统计客户帐户余额一.客户交费总额存储过程如下:CREATE PROCEDURE usp_6paynum @moneynum varchar(15)='%',@paytime1 datetime=null,@paytime2 datetime=null
AS
Select a.Client_ID as 客户编号,a.Client_Name as 客户姓名,sum(b.pay_money) as 交费总额,b.paytime as 交费时间
from TClient as a join TPay as b on a.Client_ID=b.Client_ID
where a.Client_ID=@moneynum or a.Client_Name=@moneynum or b.paytime between @paytime1 and @paytime2
group by a.Client_ID,a.Client_Name,b.PayTime
go二.客户消费总额存储过程如下:
CREATE PROCEDURE usp_6vodnum @vodmoney varchar(15)='%',@vodtime1 datetime=null,@vodtime2 datetime=null
AS
select b.Client_ID as 客户编号,c.Client_Name as 客户姓名,sum(a.Source_price) as 消费金额 ,b.vodtime as 点播日期
from TSource as a join TVod as b on a.Source_ID=b.Source_ID
INNER join TClient as c on b.Client_ID=c.Client_ID
where b.Client_ID=@vodmoney or c.Client_Name=@vodmoney or b.vodtime between @vodtime1 and @vodtime2
group by b.Client_ID,c.Client_Name,b.vodtime
GO实现统计客户帐户余额怎么写呢??急,明天就得要交作业了,老师说要用输出参数做,可是我不会,写了老是有错误,各路大侠会的帮帮忙,在线待~~~~~~~~~~~
:-)
tadmin:(管理员表)
admin_id varchar 3
admin_account varchar 15
admin_psd varchar 30
admin_auth varchar 30
true_name varchar 30
tclient:(客户表)
Client_ID varchar 6
Client_Name varchar 15
Client_Psw varchar 50
Client_Contact varchar 50
Client_Account money 8
True_Name varchar 15tpay:(交费表)
Pay_ID varchar 50
Client_ID varchar 6
Admin_ID varchar 3
Pay_Money money 8
paytime datetime 8
tsource:(资源表)
Source_ID varchar 50
Source_Name varchar 50
Source_URL varchar 50
Source_Memo varchar 50
Source_Price money 8
Type_ID varchar 50
Admin_ID varchar 3
Up_Date datetime 8
ttype:(资源类型表)
Type_ID varchar 50
Type_name varchar 15tvod:(点播表)
VOD_ID varchar 50
Client_ID varchar 6
Source_ID varchar 50
vodtime datetime 8编写存储过程 要实现统计客户帐户余额一.客户交费总额存储过程如下:CREATE PROCEDURE usp_6paynum @moneynum varchar(15)='%',@paytime1 datetime=null,@paytime2 datetime=null
AS
Select a.Client_ID as 客户编号,a.Client_Name as 客户姓名,sum(b.pay_money) as 交费总额,b.paytime as 交费时间
from TClient as a join TPay as b on a.Client_ID=b.Client_ID
where a.Client_ID=@moneynum or a.Client_Name=@moneynum or b.paytime between @paytime1 and @paytime2
group by a.Client_ID,a.Client_Name,b.PayTime
go二.客户消费总额存储过程如下:
CREATE PROCEDURE usp_6vodnum @vodmoney varchar(15)='%',@vodtime1 datetime=null,@vodtime2 datetime=null
AS
select b.Client_ID as 客户编号,c.Client_Name as 客户姓名,sum(a.Source_price) as 消费金额 ,b.vodtime as 点播日期
from TSource as a join TVod as b on a.Source_ID=b.Source_ID
INNER join TClient as c on b.Client_ID=c.Client_ID
where b.Client_ID=@vodmoney or c.Client_Name=@vodmoney or b.vodtime between @vodtime1 and @vodtime2
group by b.Client_ID,c.Client_Name,b.vodtime
GO实现统计客户帐户余额怎么写呢??急,明天就得要交作业了,老师说要用输出参数做,可是我不会,写了老是有错误,各路大侠会的帮帮忙,在线待~~~~~~~~~~~
:-)
如果是输出参数:参数 output
调用:存储过程名 参数 output