存储过程+联合查询返回字符串

最新推荐文章于 2024-03-22 16:12:46 发布

banxun5906

最新推荐文章于 2024-03-22 16:12:46 发布

阅读量167

点赞数

原文链接：http://www.cnblogs.com/ZxtIsCnblogs/p/8004336.html

版权

本文介绍了一个SQL存储过程的创建实例，该过程名为GetTable，用于从数据库中检索特定时间段内的用户数据，包括用户ID、用户名、代理数量、充值金额及佣金金额等信息。存储过程接受三个参数：用户名、开始时间和结束时间，其中结束时间默认为当前时间。通过条件判断和字符串拼接，实现了灵活的查询功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if Exists(select name from sysobjects where NAME = 'GetTable' and type='P')
drop procedure GetTable
go
CREATE PROCEDURE GetTable
@name nvarchar(50)=null,
@Time1 datetime='2017-03-07 00:00:00',
@Time2 datetime=null
AS
BEGIN
DECLARE @Str nvarchar(max)=''
if @Time2 is null
BEGIN
set @Time2=GETDATE()
END
set @Str='SELECT w.ID,w.UserAccount,(SELECT COUNT(1) AS Expr1 FROM dbo.BI6WebSiteUser AS c WHERE (ParentAgencyID = w.ID) AND (UserType = 2)) AS Agentcount
isnull(SUM(p.Paymoney),0) as RechargeAmount,isnull(Sum(isnull(p.AgentDividedMoney,0)),0) as CommissionAmount
from dbo.WebSiteUser as w , dbo.Boing_PayHheOrderForm as p
where w.ID=p.AgentDividedID and p.IsDelete=1 and p.CreateTime>='+@Time1+' and p.CreateTime<='+@Time2
if @name is not null
BEGIN
set @Str+=' and UserAccount='+@name
END