跪求更新语句（SQL）

Question

世界杯竞猜
一个list表：存比赛对阵名单，如：荷兰vs巴西
ID,OneSide,OtheSide,Time,Result(比赛结果),State(0:可以竞猜，1:比赛开始前半小时停止竞猜，2：比赛结果公布)一个参与Guess表:存每个人每场比赛的竞猜信息
ID,Username,S_time,Guess(与list表中的result对应，2为oneside赢，1为otheride赢)一个分数score表：存每个人的得分
ID,Username,score得分规则1：猜对第一场得一分，连续猜对两场1+2得3分，如此类推，如果中间错了，重新开始累积得分规则2：如果猜oneside赢的是1个人，猜otherside赢的是9个人，比赛结果（result）是oneside赢，则附加得分为（9+1）/1每个人每场竞猜的得分有基本得分和附加得分组成
现在要用语句更新score中的每个人的得分，请问用语句怎么实现啊？

Answer 1

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GuessScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GuessScore]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Guess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Guess]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[List]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[List]
GOCREATE TABLE [dbo].[GuessScore] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Score] [float] NULL ,
[State] [int] NULL 
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Guess] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SubmitTime] [datetime] NULL ,
[Guess] [int] NULL ,
[State] [int] NULL 
) ON [PRIMARY]
GOCREATE TABLE [dbo].[List] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HomeSide] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[VisitingSide] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StartTime] [datetime] NULL ,
[Result] [int] NULL ,
[demo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[State] [int] NULL 
) ON [PRIMARY]
GO
想要的结果就是：只要参与竞猜的人，在GuessScore中都有有个分数

Answer 2

在list表建触发器，当有新比赛结果时，查询竞猜人，更新得分表
建议score表加一列存连续猜对场次， 猜赢时加1，输时归0，然后根据该值更新得分情况

Answer 3

--插入竞猜结果触发器
--只在state为0,并且CID有效情况才可竞猜有效
alter trigger OnGuessAdd
on dbo.Guess
instead of insert
as
begin 
declare @state int
SET @state = 1  --默认不让竞猜
select @state = a.[State] from dbo.List a where exists(select 1 from inserted where inserted.CID = a.ID)
if @state = 0
begin
--插入竞猜结果
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select [CID], [username] ,[SubmitTime],[Guess],[State] from inserted)
--同时更新表Guessscore
insert into dbo.GuessScore([username],[LastGuessScore],[Score],[State])
(select [username],0,0,[State] from inserted)
end
end--list表触发器()
alter trigger OnListUpdate
on dbo.List
For Update
as
begin 
IF (COLUMNS_UPDATED() & 16) > 0 --Result列(第5列，值为2的(5-1)次幂)被更新时触发guessscore表变更
begin
-- 更新所有竞猜者结果
-- DECLARE @result int
-- DECLARE @ID int
Update dbo.GuessScore set [LastGuessScore] = (case  when a.Result = b.Guess then [LastGuessScore]+1 else 0 end),
[Score] = [Score] + [LastGuessScore] + 1
FROM deleted a
join dbo.Guess b on b.CID = a.ID
end
end出一部分了，剩下的吃饭完再来

Answer 4

use ymw_test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GuessScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GuessScore]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Guess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Guess]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[List]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[List]
GOCREATE TABLE [dbo].[GuessScore] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [username] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[LastGuessScore] [int] NULL,
    [Score] [float] NULL ,    [State] [int] NULL 
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Guess] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CID] [int] NULL ,
    [username] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [SubmitTime] [datetime] NULL ,
    [Guess] [int] NULL ,
    [State] [int] NULL 
) ON [PRIMARY]
GOCREATE TABLE [dbo].[List] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [HomeSide] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [VisitingSide] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [StartTime] [datetime] NULL ,
    [Result] [int] default 0 ,
    [demo] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [State] [int] NULL 
) ON [PRIMARY]
GO--插入竞猜结果触发器
--只在state为0,并且CID有效情况才可竞猜有效
create trigger OnGuessAdd
on dbo.Guess
instead of insert
as
begin 
declare @state int
SET @state = 1  --默认不让竞猜
select @state = a.[State] from dbo.List a where exists(select 1 from inserted where inserted.CID = a.ID)
if @state = 0
begin
--插入竞猜结果
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select [CID], [username] ,[SubmitTime],[Guess],[State] from inserted)
--同时更新表Guessscore
if (select count(*) from dbo.GuessScore a where exists(select 1 from inserted b where a.[username] = b.[username])) = 0
begin
print '1'
insert into dbo.GuessScore([username],[LastGuessScore],[Score],[State])
(select [username],0,0,[State] from inserted)
end
end
endgo
--list表触发器()
create trigger OnListUpdate
on dbo.List
For Update
as
begin IF (COLUMNS_UPDATED() & 16) > 0 --Result列(第5列，值为2的(5-1)次幂)被更新时触发guessscore表变更
begin
-- 更新所有竞猜者结果
--规则一：猜对第一场得一分，连续猜对两场1+2得3分，如此类推，如果中间错了，重新开始累积
--规则二：如果猜oneside赢的是1个人，猜otherside赢的是9个人，比赛结果（result）是oneside赢，则附加得分为（9+1）/1
DECLARE @winCount int
DECLARE @AppendScore int --先算出附加分
select @winCount = count(*) FROM dbo.Guess a join inserted b on a.CID = b.ID where a.[Guess] = 2
print @winCount
select @AppendScore = (case  when @winCount > 0 then count(*)/@winCount else 0 end)
FROM dbo.Guess a join inserted b on a.CID = b.ID --更新表
Update dbo.GuessScore set [LastGuessScore] = (case  when b.Result = b.Guess then a.[LastGuessScore]+1 else 0 end),
[Score] = (case  when b.Result = b.Guess then a.[Score] + @AppendScore + a.[LastGuessScore] + 1  else a.[Score] end)
FROM GuessScore a
inner join (select [username],[Guess],[Result] from inserted c  left join dbo.Guess d  on d.CID = c.ID) b on b.[username] = a.[username]
end
end
go
--插入比赛
insert into dbo.List (HomeSide,VisitingSide,StartTime,Result,[State])
values(N'巴西',N'意大利','2010-06-30 10:00:00.000',0,0)
go
--插入竞猜数据(路人甲猜巴西胜)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id, N'路人甲',getdate(),2,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--插入竞猜数据(路人乙猜巴西输)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人乙',getdate(),1,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--插入竞猜数据(路人丙猜荷兰赢)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人丙',getdate(),2,0 from dbo.List where HomeSide = N'巴西' and VisitingSide = N'意大利')
go
--更新比赛竞猜状态(停止竞猜)
update dbo.List set [State] = 0 where id = (select top 1 id from dbo.List where HomeSide = '巴西' and VisitingSide = '意大利')
go
--巴西胜
update dbo.List set [Result] = 2 where id = (select top 1 id from dbo.List where HomeSide = '巴西' and VisitingSide = '意大利')
go
select * from dbo.GuessScore
/*
ID          username   LastGuessScore Score                  State
----------- ---------- -------------- ---------------------- -----------
178         路人甲        1              2                      0
179         路人乙        0              0                      0
180         路人丙        1              2                      0(3 行受影响)
*/--插入比赛2
insert into dbo.List (HomeSide,VisitingSide,StartTime,Result,[State])
values(N'荷兰',N'美国','2010-06-30 10:00:00.000',0,0)
go
--插入竞猜数据(路人甲猜荷兰胜)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id, N'路人甲',getdate(),2,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide =N'美国')
--插入竞猜数据(路人乙猜荷兰输)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人乙',getdate(),2,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
--插入竞猜数据(路人丙猜荷兰赢)
insert into dbo.Guess ([CID], [username] ,[SubmitTime],[Guess],[State])
(select id,N'路人丙',getdate(),1,0 from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
go--更新比赛竞猜状态(停止竞猜)
update dbo.List set [State] = 0 where id = (select top 1 id from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
--荷兰胜
update dbo.List set [Result] = 2,[State] = 2 where id = (select top 1 id from dbo.List where HomeSide = N'荷兰' and VisitingSide = N'美国')
go
select * from dbo.GuessScore
/*
ID          username   LastGuessScore Score                  State
----------- ---------- -------------- ---------------------- -----------
178         路人甲        2              5                      0
179         路人乙        1              2                      0
180         路人丙        0              2                      0(3 行受影响)
*/

Answer 5

分析下结果：第一场比赛，甲，丙都压中，因此基数分为1，附加分为3/2 = 1，总分为2
/*
ID          username   LastGuessScore Score                  State
----------- ---------- -------------- ---------------------- -----------
178         路人甲        1              2                      0
179         路人乙        0              0                      0
180         路人丙        1              2                      0(3 行受影响)
*/
第二场比赛，甲中，由于是连续的，因此得分为2分，附加分为1，总分为3分，加上上次2分，所以为5分
乙中，基数分为1，附加分为3/2 = 1，总分为2
丙未中，分值不变
/*
ID          username   LastGuessScore Score                  State
----------- ---------- -------------- ---------------------- -----------
178         路人甲        2              5                      0
179         路人乙        1              2                      0
180         路人丙        0              2                      0(3 行受影响)
*/