sql-server - SQL - 遍历两个表并根据条件求和一个值

Question

我正在为一个投注程序(基于点)的项目工作，如果需要在执行以下操作的 sql server 上创建一个存储过程:

• 如果用户对一个团队下注并且结果正确，则总计 3 分；
• 如果结果与用户猜测的不同但仍然正确判断球队是赢、输还是平，则加 1 分(即用户投注皇家马德里 - 巴塞罗那；用户猜测 2 - 1；结果 1 - 0)；
• 如果用户做错了，他不会得到任何分数。

我一直在寻找一种方法来做到这一点，但我想不出办法。这可能是一个菜鸟问题，因为我们都必须从某个地方开始。我想到了循环，这在理论上是我需要做的，但我需要遍历 bets 表中的每一行并将其值与 games 表中每一行的值进行比较。

如果你能帮助我，那就太好了。谢谢。

编辑:哦，开枪。抱歉，我正要添加表格，但我忘了。好吧，他们在这里:

-投注表:

CREATE TABLE Bets(
   id_bet        NUMERIC(18,0) NOT NULL PRIMARY KEY 
  ,id_user       NUMERIC(18,0)
  ,id_game       NUMERIC(18,0)
  ,date          NUMERIC(8,0)
  ,time          NUMERIC(4,0)
  ,goals_home    NUMERIC(18,0)
  ,goals_visitor NUMERIC(18,0)
);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (1,1,4,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (2,1,3,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (3,7,3,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (4,7,4,20170614,1600,1,1);

---

-游戏表:

CREATE TABLE Games(
   id_game         NUMERIC(18,0) NOT NULL PRIMARY KEY 
  ,num_game        NUMERIC(18,0)
  ,id_club_home    NUMERIC(18,0)
  ,id_club_visitor NUMERIC(18,0)
  ,id_competition  NUMERIC(18,0)
  ,goals_home      NUMERIC(18,0)
  ,goals_visitor   NUMERIC(18,0)
  ,date            NUMERIC(8,0)
  ,time            NUMERIC(4,0)
);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (1,1,3,6,2,2,2,20170614,1700);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (3,2,4,3,2,1,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (4,3,3,4,2,1,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (5,4,6,3,2,2,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (6,5,4,6,2,NULL,NULL,20170614,1600);

---

-我试过这个(和其他方法但没有保存):

CREATE PROCEDURE [dbo].[Count_Points] @valor AS INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @a AS INT
    DECLARE @goals_home_bet AS NUMERIC(18, 0) = (
            SELECT Bets.goals_home
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_visitor_bet AS NUMERIC(18, 0) = (
            SELECT Bets.goals_visitor
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_home_games AS NUMERIC(18, 0) = (
            SELECT Games.goals_home
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_visitor_games AS NUMERIC(18, 0) = (
            SELECT Games.goals_visitor
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_joid_gamego
            )

    SET @valor = 0
    SET @a = 1
    SET @valor = sum(@valor + 3)

    WHILE (
            (
                SELECT max(id_bet)
                FROM Bets
                ) > @a
            )
    BEGIN
        SET @a += 1
    END
END

这只是为了测试它是否有效(因此仅引用值 3)，但显然我只得到值 3。其他方式我也会得到 3 或 null，甚至。

编辑 2:我的解决方案(可能不是最有效的，但仍然如此):

CREATE PROCEDURE [dbo].[Calculate_Points]
        @control as int output,
        @points as int output,
        @nickname varchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    declare @cont numeric(18,0)=1
    declare @cont1 numeric(18,0)
    declare @id_user numeric(18,0)=(select num_user from Users where nickname=@nickname)
    declare @id_game numeric(18,0)
    declare @goals_home_bet numeric(18,0)
    declare @goals_visitor_bet numeric(18,0)
    declare @goals_home_game numeric(18,0)
    declare @goals_visitor_game numeric(18,0)
    set @points=0


    while(@cont<=(select max(num_bet) from Bets))
    begin
        if exists (select id_user from Bets where @id_user=id_user and num_bet=@cont)
        begin
            set @id_game=(select id_game from Bets where num_bet=@cont)
            set @goals_home_bet=(select goals_home from Bets where num_bet=@cont)
            set @goals_visitor_bet=(select goals_visitor from Bets where num_bet=@cont)
            set @cont1=1
            while(@cont1<(select max(id_game) from Games))
            begin
                if(@id_game=(select id_game from Games where id_game =@cont1))
                begin
                    set @goals_home_game=(select goals_home from Games where num_game=@cont1)
                    set @goals_visitor_game=(select goals_visitor from Games where num_game=@cont1)

                    if(@goals_home_bet=@golos_casa_jogo and @golos_fora_aposta=@golos_fora_jogo)
                        set @pontos+=3
                    else if((@goals_home_bet>@goals_visitor_bet and @goals_home_game>@goals_visitor_game) or (@goals_home_bet<@goals_visitor_bet and @goals_home_game<@goals_visitor_bet) or (@goals_home_bet=@goals_visitor_bet and @goals_home_game=@goals_visitor_bet))
                        set @points+=1
                end

                set @cont1+=1
            end
        end
        set @cont+=1
    end

END

Answer 1

根据您的目标从 bit 切换到 int，然后尝试如下操作:

create view vUserPointsPerGame
as
select
    id_user, bets.id_game,
    case 
        -- you could also just compare the goal differences if you want
        when bets.goals_home = games.goals_home and bets.goals_visitor = games.goals_visitor then 3
        when (bets.goals_home >= bets.goals_visitor and games.goals_home >= games.goals_visitor) or (bets.goals_home < bets.goals_visitor and games.goals_home < games.goals_visitor) then 1        
        else 0
    end as Points
from
    bets
    join games on bets.id_game = games.id_game

go

create view vUserTotalPoints
as
    select id_user, sum(points) as TotalPoints 
    from vUserPointsPerGame 
    group by id_user
go

select * from vUserTotalPoints

这将为您返回按用户分割的所有投注的积分总和。

您可以使它更有效率，但这里的主要思想是您不需要循环或存储过程来完成您想在这里完成的事情。

编辑:您提到您想要一个程序，因为它更容易使用。这是一个将获得单个用户的总分的方法:

create procedure spGetUserTotalPoints
(
    @iUserId int,
    @oTotalPoints int output
)
as
begin
    select @oTotalPoints = TotalPoints
    from vUserTotalPoints
    where id_user = @iUserId
end

这是另一个将获得所有已知用户的每个用户的总分:

create procedure spGetUsersTotalPoints
as
begin
    select id_user, TotalPoints
    from vUserTotalPoints
end

同样，如果您遇到有关批处理的错误，只需一次运行每个语句，而不是依赖 go 关键字。一些 SQL 前端不喜欢这样。