sql - 子查询返回超过 1 个值。只有拆分我的查询才能解决这个问题
问题描述
大家下午好，
在查询快要结束时，我开始为我正在托管的游戏向我的玩家发送 PVP 邮件奖励。问题是所有查询都成功运行，直到最后一个。
我认为这个错误是在我调用的函数返回一个值导致脚本最后变得疯狂之后产生的。我可以通过将我所有的 PVP 邮件奖励分成不同的查询来解决这个问题，但我真的更希望将它们与我发送的所有其他内容放在一个主查询中。
我将如何解决这个问题？感谢您花时间和耐心阅读本文。
这是错误
> Msg 512, Level 16, State 1, Server LAPTOP-K2EKS8H0, Procedure , Line 0
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
> [21000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (512)
> Time: 0.029s
-- Create temporary table to hold data from dbo.Characters and dbo.PVPRanking --
USE DNWorld
IF OBJECT_ID('tempdb..#TempAccountTable') IS NOT NULL
begin
drop table #TempAccountTable
end
Select AccountID, G.AccountName, G.CharacterName, PVPLevel, PVPExp, TotalRank
INTO #TempAccountTable
FROM Characters as G
INNER JOIN (
SELECT *, RANK() OVER (ORDER BY TotalRank) AS rn
FROM PvPRanking
Where totalrank between 1 and 100
) as tmp on tmp.CharacterID = G.CharacterID
;
--------------------------------------------------- Rank 1 PvP QC Distribution ---------------------------------------------------
USE DNMembership
GO
DECLARE @AccountName nvarchar(50), @CompensationAmt INT, @i int = 0, @Rank1Rewards int
SET @Rank1Rewards = 1
-- Set and deliver QC by their total rank.
SET @AccountName = (SELECT AccountName FROM #TempAccountTable where TotalRank = 1)
EXEC dbo.P_AddCashIncome @AccountName, 2, NULL, NULL, @Rank1Rewards
;
--------------------------------------------------- PVP Rank #2 - #10 QC distribution via while loop ---------------------------------------------------
USE DNMembership
GO
DECLARE @AccountName nvarchar(50), @CompensationAmt INT, @i int = 0, @RankRewards int
SET @RankRewards = 2
WHILE (@i <= 10) -- BE AWARE IF EDITING
BEGIN
SET @i = @i + 1
SET @AccountName = (SELECT AccountName FROM #TempAccountTable where TotalRank = 1 + @i) -- BE AWARE IF EDITING
EXEC dbo.P_AddCashIncome @AccountName, 2, NULL, NULL, @RankRewards;
END;
-- PVP Rank #11 - #25 distribution via while loop
USE DNMembership
GO
DECLARE @AccountName nvarchar(50), @CompensationAmt INT, @i int = 0, @RankRewards int
SET @RankRewards = 3
WHILE (@i <= 25)
BEGIN
SET @i = @i + 1
SET @AccountName = (SELECT AccountName FROM #TempAccountTable where TotalRank = 10 + @i)
EXEC dbo.P_AddCashIncome @AccountName, 2, NULL, NULL, @RankRewards;
END;
--------------------------------------------------- PVP Rank #26 - #100 QC distribution via while loop ---------------------------------------------------
USE DNMembership
GO
DECLARE @AccountName nvarchar(50), @CompensationAmt INT, @i int = 0, @RankRewards int
SET @RankRewards = 4
WHILE (@i <= 100)
BEGIN
SET @i = @i + 1
SET @AccountName = (SELECT AccountName FROM #TempAccountTable where TotalRank = 25 + @i)
EXEC dbo.P_AddCashIncome @AccountName, 2, NULL, NULL, @RankRewards;
END;
--------------------------------------------------- PVP Mail Rewards #2 - #10 ---------------------------------------------------
USE DNWorld
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
DECLARE
@nvcSenderName nvarchar(50),
@Subject nvarchar(50),
@Content nvarchar(300),
@CharacterName nvarchar(50),
@CoinAmount bigint,
@ItemID1 bigint ,
@ItemID2 bigint ,
@insItemCount1 smallint,
@insItemCount2 smallint,
@LevelItem1 bigint,
@LevelItem2 bigint,
@MailTabCode tinyint,
@i int = 0
WHILE (@i <= 10) -- BE AWARE IF EDITING
BEGIN
SET @i = @i + 1
-- FROM
SET @nvcSenderName = 'PVP Logistics Bot'
-- SUBJECT
SET @Subject = 'Top 10 Player Killers'
-- MESSAGE
SET @Content = 'Outstanding job on maintaining your status as one of the top 10 players throughout Skitzovania.'
-- TARGET CHAR and Loop to desired rank #
SET @CharacterName = (SELECT CharacterName FROM #TempAccountTable where TotalRank = 1 + @i)
-- COPPER
SET @CoinAmount = '0'
-- ITEM ID
SET @ItemID1 = '374341682' -- Goddess Medal
SET @ItemID2 = '335833344' -- Warrior's Trophy
-- QUANTITY
SET @insItemCount1 = '1'
SET @insItemCount2 = '2'
-- ENHANCEMENT LEVEL
SET @LevelItem1 = '0'
SET @LevelItem2 = '0'
-- MAIL TAB TYPE
-- 0 All, 1 Character, 2 Contents, 3 Event, 4 System
SET @MailTabCode = 4
DECLARE
@ItemSerial1 bigint
DECLARE
@ItemSerialfix1 bigint
DECLARE
@inbReceiverCharacterID int
BEGIN
SET @inbReceiverCharacterID = (Select CharacterID from DNWorld.dbo.Characters where CharacterName = @CharacterName)
SET @ItemSerial1 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix1 = (@ItemSerial1 + 1)
DECLARE
@ItemSerial2 bigint
DECLARE
@ItemSerialfix2 bigint
SET @ItemSerial2 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix2 = (@ItemSerial2 + 1)
EXEC DNWorld.dbo.P_SendSystemMail
@nvcSenderName,
@inbReceiverCharacterID,
3,
4,
@Subject,
@Content,
@CoinAmount,
@ItemSerialfix1,
@ItemID1,
@insItemCount1,
24000,
0,
@LevelItem1,
0,
0,
1,
0,
1,
0,
@ItemSerialfix2,
@itemid2,
@intChannelID = 0,
@intMapID = 0,
@intTotalMailCount = null,
@intNotReadMailCount = 1,
@int7DaysLeftMailCount = null,
@intMailID = null,
@inyMailTabCode = @MailTabCode
END
END
GO
--------------------------------------------------- PVP Mail Rewards #11 - #25 ---------------------------------------------------
USE DNWorld
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
DECLARE
@nvcSenderName nvarchar(50),
@Subject nvarchar(50),
@Content nvarchar(300),
@CharacterName nvarchar(50),
@CoinAmount bigint,
@ItemID1 bigint ,
@ItemID2 bigint ,
@insItemCount1 smallint,
@insItemCount2 smallint,
@LevelItem1 bigint,
@LevelItem2 bigint,
@MailTabCode tinyint,
@i int = 0
WHILE (@i <= 25) -- BE AWARE IF EDITING
BEGIN
SET @i = @i + 1
-- FROM
SET @nvcSenderName = 'PVP Logistics Bot'
-- SUBJECT
SET @Subject = 'Top 25 Player Killers'
-- MESSAGE
SET @Content = 'Outstanding job on maintaining your status as one of the top 25 players throughout Skitzovania.'
-- TARGET CHAR and Loop to desired rank #
SET @CharacterName = (SELECT CharacterName FROM #TempAccountTable where TotalRank = 10 + @i)
-- COPPER
SET @CoinAmount = '0'
-- ITEM ID
SET @ItemID1 = '374341682' -- Goddess Medal
SET @ItemID2 = '335833344' -- Warrior's Trophy
-- QUANTITY
SET @insItemCount1 = '1'
SET @insItemCount2 = '2'
-- ENHANCEMENT LEVEL
SET @LevelItem1 = '0'
SET @LevelItem2 = '0'
-- MAIL TAB TYPE
-- 0 All, 1 Character, 2 Contents, 3 Event, 4 System
SET @MailTabCode = 4
DECLARE
@ItemSerial1 bigint
DECLARE
@ItemSerialfix1 bigint
DECLARE
@inbReceiverCharacterID int
BEGIN
SET @inbReceiverCharacterID = (Select CharacterID from DNWorld.dbo.Characters where CharacterName = @CharacterName)
SET @ItemSerial1 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix1 = (@ItemSerial1 + 1)
DECLARE
@ItemSerial2 bigint
DECLARE
@ItemSerialfix2 bigint
SET @ItemSerial2 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix2 = (@ItemSerial2 + 1)
EXEC DNWorld.dbo.P_SendSystemMail
@nvcSenderName,
@inbReceiverCharacterID,
3,
4,
@Subject,
@Content,
@CoinAmount,
@ItemSerialfix1,
@ItemID1,
@insItemCount1,
24000,
0,
@LevelItem1,
0,
0,
1,
0,
1,
0,
@ItemSerialfix2,
@itemid2,
@intChannelID = 0,
@intMapID = 0,
@intTotalMailCount = null,
@intNotReadMailCount = 1,
@int7DaysLeftMailCount = null,
@intMailID = null,
@inyMailTabCode = @MailTabCode
END
END
GO
--------------------------------------------------- PVP Mail Rewards #26 - #100 ---------------------------------------------------
USE DNWorld
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
DECLARE
@nvcSenderName nvarchar(50),
@Subject nvarchar(50),
@Content nvarchar(300),
@CharacterName nvarchar(50),
@CoinAmount bigint,
@ItemID1 bigint ,
@ItemID2 bigint ,
@insItemCount1 smallint,
@insItemCount2 smallint,
@LevelItem1 bigint,
@LevelItem2 bigint,
@MailTabCode tinyint,
@i int = 0
WHILE (@i <= 100) -- BE AWARE IF EDITING
BEGIN
SET @i = @i + 1
-- FROM
SET @nvcSenderName = 'PVP Logistics Bot'
-- SUBJECT
SET @Subject = 'Top 100 Player Killers'
-- MESSAGE
SET @Content = 'Outstanding job on maintaining your status as one of the top 100 players throughout Skitzovania.'
-- TARGET CHAR and Loop to desired rank #
SET @CharacterName = (SELECT CharacterName FROM #TempAccountTable where TotalRank = 25 + @i)
-- COPPER
SET @CoinAmount = '0'
-- ITEM ID
SET @ItemID1 = '374341682' -- Goddess Medal
SET @ItemID2 = '335833344' -- Warrior's Trophy
-- QUANTITY
SET @insItemCount1 = '1'
SET @insItemCount2 = '2'
-- ENHANCEMENT LEVEL
SET @LevelItem1 = '0'
SET @LevelItem2 = '0'
-- MAIL TAB TYPE
-- 0 All, 1 Character, 2 Contents, 3 Event, 4 System
SET @MailTabCode = 4
DECLARE
@ItemSerial1 bigint
DECLARE
@ItemSerialfix1 bigint
DECLARE
@inbReceiverCharacterID int
BEGIN
SET @inbReceiverCharacterID = (Select CharacterID from DNWorld.dbo.Characters where CharacterName = @CharacterName)
SET @ItemSerial1 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix1 = (@ItemSerial1 + 1)
DECLARE
@ItemSerial2 bigint
DECLARE
@ItemSerialfix2 bigint
SET @ItemSerial2 = (SELECT max(ItemSerial) as ItemSerial
FROM dnworld.dbo.MaterializedItems)
SET @ItemSerialfix2 = (@ItemSerial2 + 1)
EXEC DNWorld.dbo.P_SendSystemMail
@nvcSenderName,
@inbReceiverCharacterID,
3,
4,
@Subject,
@Content,
@CoinAmount,
@ItemSerialfix1,
@ItemID1,
@insItemCount1,
24000,
0,
@LevelItem1,
0,
0,
1,
0,
1,
0,
@ItemSerialfix2,
@itemid2,
@intChannelID = 0,
@intMapID = 0,
@intTotalMailCount = null,
@intNotReadMailCount = 1,
@int7DaysLeftMailCount = null,
@intMailID = null,
@inyMailTabCode = @MailTabCode
END
END
GO
解决方案
推荐阅读
- mongodb - AWS GLUE 错误：调用 o75.pyWriteDynamicFrame 时发生错误。无法将 STRING 转换为 IntegerType（值：BsonString{value=''}）
- visualization - Google 地球引擎：可视化和导出整个集合
- javascript - 我做了 npm run build 并得到了很多错误
- python - PySpark - 创建新密钥
- angular - Angular 11 - 路由无法与参数一起正常工作
- python - 面临的问题是在 python 中使用 GTTS 将多个文本转换为语音
- amazon-web-services - Aws Glue 工作不断将数据推送到红移
- cookies - 我想根据 cookie 的 accessToken 设置状态，但我无法访问 cookie
- python - 处理异常的最pythonic方式
- c# - 列表
.Add() 对象，其中还包括一个 List