时间戳

首页 > 解决方案 > 无法在 SQLite 中使用简单的除法选择语句

sqlite - 无法在 SQLite 中使用简单的除法选择语句

问题描述

我有以下代码

SELECT DISTINCT M.First_Name || ' ' || M.Surname AS Manager,
                SUM(CASE WHEN MC.Team = F.Home_Team AND 
                              MC.Start_Date <= F.Date AND 
                              MC.End_Date >= F.Date THEN 1 WHEN MC.Team = F.Away_Team AND 
                                                                MC.Start_Date <= F.Date AND 
                                                                MC.End_Date >= F.Date THEN 1 ELSE 0 END) AS Games,
                SUM(CASE WHEN S.HTFT > S.ATFT AND 
                              F.Home_Team = 48 AND 
                              MC.Start_Date <= F.Date AND 
                              MC.End_Date >= F.Date THEN 3 WHEN S.ATFT > S.HTFT AND 
                                                                F.Away_Team = 48 AND 
                                                                MC.Start_Date <= F.Date AND 
                                                                MC.End_Date >= F.Date THEN 3 WHEN S.HTFT = S.ATFT AND 
                                                                                                  MC.Start_Date <= F.Date AND 
                                                                                                  MC.End_Date >= F.Date THEN 1 ELSE 0 END) AS Points,
                (SUM(1.0 * CASE WHEN S.HTFT > S.ATFT AND 
                                     F.Home_Team = 48 AND 
                                     MC.Start_Date <= F.Date AND 
                                     MC.End_Date >= F.Date THEN 3 WHEN S.ATFT > S.HTFT AND 
                                                                       F.Away_Team = 48 AND 
                                                                       MC.Start_Date <= F.Date AND 
                                                                       MC.End_Date >= F.Date THEN 3 WHEN S.HTFT = S.ATFT AND 
                                                                                                         MC.Start_Date <= F.Date AND 
                                                                                                         MC.End_Date >= F.Date THEN 1 ELSE 0 END) / SUM(CASE WHEN MC.Team = F.Home_Team AND 
                                                                                                                                                                  MC.Start_Date <= F.Date AND 
                                                                                                                                                                  MC.End_Date >= F.Date THEN 1 WHEN MC.Team = F.Away_Team AND 
                                                                                                                                                                                                    MC.Start_Date <= F.Date AND 
                                                                                                                                                                                                    MC.End_Date >= F.Date THEN 1 ELSE 0 END) ) AS PPG
  FROM Manager AS M
       LEFT JOIN
       Manager_Contract AS MC ON M.ID = MC.Manager
       LEFT JOIN
       Fixture AS F ON MC.Team = F.Home_Team OR 
                       MC.Team = F.Away_Team
       LEFT JOIN
       Score AS S ON F.ID = S.Fixture
 GROUP BY M.First_Name || ' ' || M.Surname
HAVING Games > 0
 ORDER BY MC.Start_Date ASC;

哪个返回

经理 游戏 积分 PPG
本加纳 10 12 1.2
保罗·提斯代尔 15 12 0.8
汤米·威德灵顿 3 3 1
乔伊·巴顿 18 11 0.61111

我要返回的最后一列

PPG
1.20
0.80
1.00
0.61

每次我尝试在其中添加 ROUND 时都会收到一条错误消息 - 通常 Row 值被滥用。

任何帮助表示赞赏。

我正在使用 SQLite Studio 和 MacBook Pro。

问题已编辑 - 设法解决最初的问题。

标签： sqliteselectrounding

解决方案

错误信息：

行值被滥用

通常是括号使用不正确的结果，这可能是因为您的代码在CASE表达式的所有分支中都包含许多条件。

我认为您可以简化代码。

HAVING子句中的条件：

HAVING Games > 0

将过滤掉具有此CASE表达式的任何管理器：

CASE 
  WHEN MC.Team = F.Home_Team 
   AND MC.Start_Date <= F.Date 
   AND MC.End_Date >= F.Date THEN 1 
  WHEN MC.Team = F.Away_Team 
   AND MC.Start_Date <= F.Date 
   AND MC.End_Date >= F.Date THEN 1 
  ELSE 0 
END

总是0。

那么，为什么不设置一个WHERE子句，相当于上面的条件呢？此外，这使得前 2 个LEFT连接实际上是INNER连接（我不确定最后一个连接是否应该是一个LEFT连接）。

试试这个简化的代码：

SELECT M.First_Name || ' ' || M.Surname AS Manager,
       COUNT(*) AS Games,
       SUM(
         CASE 
           WHEN (S.HTFT > S.ATFT AND F.Home_Team = 48) OR (S.ATFT > S.HTFT AND F.Away_Team = 48) THEN 3 
           WHEN S.HTFT = S.ATFT THEN 1 
           ELSE 0 
         END
       ) AS Points,
       ROUND(
         1.0 *
         SUM(
           CASE 
             WHEN (S.HTFT > S.ATFT AND F.Home_Team = 48) OR (S.ATFT > S.HTFT AND F.Away_Team = 48) THEN 3 
             WHEN S.HTFT = S.ATFT THEN 1 
             ELSE 0 
           END
         ) / 
         COUNT(*)
         , 2
       ) AS PPG
FROM Manager AS M
INNER JOIN manager_Contract AS MC ON M.ID = MC.Manager
INNER JOIN Fixture AS F ON MC.Team IN (F.Home_Team, F.Away_Team)
LEFT JOIN Score AS S ON F.ID = S.Fixture
WHERE MC.Start_Date <= F.Date AND MC.End_Date >= F.Date
GROUP BY Manager;

推荐阅读