时间戳

首页 > 解决方案 > T-SQL存储过程执行select两次？

sql-server - T-SQL存储过程执行select两次？

问题描述

DROP PROCEDURE showMatchOnDate
go

CREATE PROCEDURE showMatchOnDate 
     @MatchDate DATE
AS
    DECLARE @tempTeams TABLE
                       (
                           Id CHAR(3) PRIMARY KEY,
                           name VARCHAR(40),
                           nomatches INT,
                           owngoals INT,
                           othergoals INT,
                           points INT
                       )

    INSERT INTO @tempTeams(Id, name, nomatches, owngoals, othergoals, points)
        SELECT * FROM teams

    DECLARE @homeGoals INT
    DECLARE @outGoals INT
    DECLARE @homeID CHAR(3)
    DECLARE @outID CHAR(3)

    DECLARE @count INT
    DECLARE @totalAmount INT

    SET @totalAmount = (SELECT COUNT(*) FROM matches WHERE matchdate <= @MatchDate)
    SET @count = 1

    SELECT @totalAmount

    WHILE (@count <= @totalAmount)
    BEGIN
        SELECT @homeGoals = (SELECT homegoal FROM matches WHERE Id = @count)
        SELECT @outGoals = (SELECT outgoal FROM matches WHERE Id = @count)
        SELECT @homeID = (SELECT homeid FROM matches WHERE Id = @count)
        SELECT @outID = (SELECT outid FROM matches WHERE Id = @count)

        UPDATE @tempTeams
        SET nomatches = nomatches + 1
        WHERE Id = @homeID OR Id = @outID

        UPDATE @tempTeams
        SET owngoals = owngoals + @homeGoals
        WHERE Id = @homeID

        UPDATE @tempTeams
        SET owngoals = owngoals + @outGoals
        WHERE Id = @outID

        UPDATE @tempTeams
        SET othergoals = othergoals + @outGoals
        WHERE Id = @homeID

        UPDATE @tempTeams
        SET othergoals = othergoals + @homeGoals
        WHERE Id = @outID

        UPDATE @tempTeams
        SET points = points + 3
        WHERE Id = @homeID AND @homeGoals > @outGoals

        UPDATE @tempTeams
        SET points = points + 3
        WHERE Id = @outID AND @outGoals > @homeGoals

        IF @outGoals = @homeGoals
            UPDATE @tempTeams
            SET points = points + 1
            WHERE Id = @homeID OR (Id = @outID)

        SET @count = @count + 1
    END

    SELECT * FROM @tempTeams
go

出于某种原因，当我运行它时，我得到了每个项目的副本。我不想要。显然我想要正确的值，应该是 154 个不同的项目。但相反，我得到 308，并且每个项目都是重复的。有谁知道为什么？

标签： sql-serverdatabasetsql

解决方案

首先检查你的“团队”表，你可以在这里有额外的数据，如果有，删除重复的。

最佳实践还需要编写显式字段以避免将来出现错误，如下所示：

insert into @tempTeams(Id,name,nomatches,owngoals,othergoals,points)
   select Id,name,nomatches,owngoals,othergoals,points
   from teams

推荐阅读