搜索发送失败，短信发重记录

最新推荐文章于 2025-06-11 16:00:00 发布

xianyiqi

最新推荐文章于 2025-06-11 16:00:00 发布

阅读量535

点赞数

CC 4.0 BY-SA版权

文章标签： 2010 手机

本文链接：https://blog.youkuaiyun.com/xianyiqi/article/details/5966529

本文提供了一段SQL脚本，用于查询指定日期范围内充值失败的记录及短信重发情况。通过游标和子查询的方式实现了对充值失败记录的详细检索，并统计了各卡充值金额。

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

--搜索发送失败，短信发重记录 declare @startDate datetime declare @endDate datetime set @startDate = '2010-10-26' set @endDate = '2010-10-27' declare @PhoneNumber varchar(100) declare @RechargeMoney varchar(100) declare @AddTime datetime declare Cur cursor for select PhoneNumber,RechargeMoney from dbo.view_FailureRecharge where addtime between @startDate and @endDate group by PhoneNumber,RechargeMoney open Cur Fetch next From Cur Into @PhoneNumber,@RechargeMoney While @@fetch_status=0 Begin declare @countmsg int select @countmsg = count(1) from ( select Count(1) as countMsg,MsgContent from inbox as a where msgcontent like '%' + @PhoneNumber + '%' and ReceiveTime between @startDate and @endDate Group by MsgContent ) as b if(@countmsg > 1) begin select * from inbox as a where msgcontent like '%' + @PhoneNumber + '%' and ReceiveTime between @startDate and @endDate end Fetch Next From Cur Into @PhoneNumber,@RechargeMoney End Close Cur Deallocate Cur --统计各卡冲值金额 declare @startDate datetime declare @endDate datetime set @startDate = '2010-10-26' set @endDate = '2010-10-27' select MOdemID as 卡编号, (select top 1 [Name] from smsmodem where smsmodem.ModemID = RechargeMsg.MOdemID) as 卡名, (select top 1 AccountNo from smsmodem where smsmodem.ModemID = RechargeMsg.MOdemID) as 代理商帐号, (select top 1 CardNo from smsmodem where smsmodem.ModemID = RechargeMsg.MOdemID) as 手机卡号, sum(RechargeMoney) as 缴费金额 from dbo.RechargeMsg where addtime between @startDate and @endDate group by MOdemID order by 卡名 --重发记录 declare @startDate datetime declare @endDate datetime set @startDate = '2010-10-26' set @endDate = '2010-10-27' --select * from HasBeenSentMsg where [Message] in( select [Message] from HasBeenSentMsg where msgid in( select msgid from ( select count(1) as countmsg, min(MsgID) as msgid ,[Message] from HasBeenSentMsg where sendtime between @startDate and @endDate group by [Message]) as a where countmsg > 1 ) --) --select * from --dbo.view_FailureRecharge --where PhoneNumber = '13880964752' --select * from dbo.InBox where msgcontent like '%13880964752%' -- -- --select * from dbo.RechargeMsg where PhoneNumber = '18782935186' -- --select * from outbox where msgcontent like '%18782935186%'