子查询返回了多个值只有拆分我的查询才能解决这个问题

lnlaulya  于 2021-08-09  发布在  Java
关注(0)|答案(0)|浏览(182)

大家下午好，
在我的查询快结束的时候，我开始给我的玩家发送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
sqlsql-server

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com