SQL Server存储过程日期时间参数

Question

我有一个足球联赛的数据库，我试图让球队的当前得分上的具体日期。如果我在过去输入一个日期，我希望在该特定日期得分 - 该日期的每个球队的得分。

在我的数据库中，我有一个包括所有比赛的表格和我与团队和他们的点（该表实际上是一样的当前得分）的表。

两个表是：

create table teams 
(
id char(3) primary key, 
name varchar(40), 
nomatches int, 
owngoals int, 
othergoals int, 
points int 
) 

create table matches 
(
id int identity(1,1), 
homeid char(3) foreign key references teams(id), 
outid char(3) foreign key references teams(id), 
homegoal int, 
outgoal int, 
matchdate datetime 
) 

我试图用一个存储过程，我有一个datetime作为参数，以显示在由参数定义的日期目前的得分（球队表）。

现在，我选择的所有比赛是大于我想直到评分表，从日期和减去该比赛从队点的结果（更新）。

但在我看来，这是一个大量的工作来的东西，简单的事情。

有没有人有更好的主意？

Answer 1

为什么要减去？在我看来，正确的做法是采取联赛开始日期并计算从当天到选定日期的分数。

Answer 2

我不知道你为什么会想要把这样一个简单的查询到的操作，但是基本上就是你问的是如下：

CREATE PROCEDURE sp_goals_to_date(@todate DATETIME) AS  
    SELECT id, SUM(homegoal) AS homegoal, SUM(outgoal) AS outgoal FROM matches WHERE matchdate <= @mydate 

什么费多尔说的是正确的 - 这是更从一开始就有效地进行单个计算，而不是必须从两个不同的表中进行计算。

Answer 3

我会先转换matches表是这样的：

SELECT 
    teamid = CASE t.calchometeam WHEN 1 THEN m.homeid ELSE m.outid END, 
    owngoal = CASE t.calchometeam WHEN 1 THEN m.homegoal ELSE m.outgoal END, 
    othergoal = CASE t.calchometeam WHEN 0 THEN m.homegoal ELSE m.outgoal END, 
    points = CASE m.homegoal 
    WHEN m.outgoal THEN @drawpoints 
    ELSE (SIGN(m.homegoal - m.outgoal) + 1)/2^~m.playedhome) * @winpoints 
     + (SIGN(m.homegoal - m.outgoal) + 1)/2^m.playedhome) * @losspoints 
    END 
FROM matches m 
CROSS JOIN (
    SELECT CAST(0 AS bit) UNION ALL 
    SELECT CAST(1 AS bit) 
) AS t (calchometeam) 
WHERE m.matchdate <= @givendate 

现在，它更容易计算所需的总计：

SELECT 
    teamid, 
    nomatches = COUNT(*), 
    owngoals = SUM(owngoal), 
    othergoals = SUM(othergoal), 
    points  = SUM(points) 
FROM transformed_matches 
GROUP BY 
    teamid 

下一步将会参加最后的结果设置为teams表来获得团队的名字。如果你真的需要最后一步，那么你当然可以按照你想要的方式从头开始计算，即只计算你需要从当前值减去的统计量，而不是实际的排名。因此，使用这种倒置的逻辑，整个查询可能是这样的：

WITH 
transformed_matches AS (
    SELECT 
    matchid = m.id, 
    teamid = CASE t.calchometeam WHEN 1 THEN m.homeid ELSE m.outid END, 
    owngoal = CASE t.calchometeam WHEN 1 THEN m.homegoal ELSE m.outgoal END, 
    othergoal = CASE t.calchometeam WHEN 0 THEN m.homegoal ELSE m.outgoal END, 
    points = CASE m.homegoal 
     WHEN m.outgoal THEN @drawpoints 
     ELSE (SIGN(m.homegoal - m.outgoal) + 1)/2^~m.playedhome) * @winpoints 
     + (SIGN(m.homegoal - m.outgoal) + 1)/2^m.playedhome) * @losspoints 
    END 
    FROM matches m 
    CROSS JOIN (
    SELECT CAST(0 AS bit) UNION ALL 
    SELECT CAST(1 AS bit) 
) AS t (calchometeam) 
    WHERE m.matchdate > @givendate 
), 
aggregated AS (
    SELECT 
    teamid, 
    nomatches = COUNT(*), 
    owngoals = SUM(owngoal), 
    othergoals = SUM(othergoal), 
    points  = SUM(points) 
    FROM transformed_matches 
    GROUP BY 
    teamid 
) 
SELECT 
    t.id, 
    t.name, 
    nomatches = t.nomatches - ISNULL(a.nomatches , 0), 
    owngoals = t.owngoals - ISNULL(a.orngoals , 0), 
    othergoals = t.nomatches - ISNULL(a.othergoals, 0), 
    points  = t.points  - ISNULL(a.points , 0) 
FROM teams t 
    LEFT JOIN aggregated a ON t.id = a.teamid 

注意：您没有说明你的意思是哪一种足球，但生活在欧洲，它更容易为我承担足球协会而不是其他类型。然而，因为我不确定，我决定参数化我的查询。这就是为什么你可以看到所有这些@winpoints,@drawpoints和@losspoints占位符。如果你愿意的话，你可以用实际的常数替换变量，或者如果你想满足好奇心，如果不同的评分系统有效，你可以让查询参数化。