MySQL 查询数学问题未产生预期输出

Question

概述:

我构建了一个在本地运行的应用程序，它允许我跟踪 children 每天的家务和行为。该系统具有消极和积极的行为，我可以分配给它们，这些行为对应于 100 分值。

逻辑:

• 查询仅查看当天来计算分数。如果前一天收到评分，这些评分将不会计入每日总评分。
• 100 分是 child 当天可以获得的最高分，即使他们的评分导致他们超过此分数，也始终会返回 100。
• 如果他们当天没有任何评分(无论是正面还是负面)，系统会将他们的分数默认为起始点 100。
• 当他们获得积分时，其总数将根据为行为设置的值进行相应调整，上升或下降。

场景:

1. 没有任何评分的新一天意味着 child 从 100 分开始。他们收到值为 -3 的负面行为。这会将他们的 totalPoints 返回为 97。
2. 然后，上述 child 将获得 2 分的积极评价，使他们的 totalPoints 达到 99。
3. 他们又获得了值(value) 5 分的正面评价。由于我们的上限为 100，因此我们会将其 totalPoints 返回为 100，无论它超出 100 多少。

问题:

我构建了查询，并认为一切正常，但似乎存在轻微的数学问题。当 child 获得 -3 分评分时，他们的评分达到了预期的 97。然后我给了他们一个积极的4，这使得他们的分数达到了99，而不是像我预期的那样100。

查询:

 SELECT c.id,
       c.NAME,
       Date_format(From_days(Datediff(CURRENT_DATE, c.age)),
       '%y Years %m Months %d Days')                                 AS age,
       c.photoname,
       c.photonamesmall,
       (SELECT CASE
                 WHEN ( Ifnull(Sum(t.points), (SELECT settingvalue
                                               FROM   settings
                                               WHERE  settingname = 'MaxPoints')
                        ) >= (
                        SELECT
                               settingvalue
                        FROM
                        settings
                        WHERE
                                 settingname = 'MaxPoints') ) THEN 100
                 WHEN ( Sum(t.points) <= 0 ) THEN ( (SELECT settingvalue
                                                     FROM   settings
                                                     WHERE  settingname =
                                                            'MaxPoints')
                                                    + Sum(t.points) )
                 ELSE ( (SELECT settingvalue
                         FROM   settings
                         WHERE  settingname = 'MaxPoints') -
                        Ifnull(Sum(t.points), (SELECT
                        settingvalue
                                               FROM   settings
                                               WHERE
                        settingname = 'MaxPoints')) )
               END
        FROM   behaviorratings AS r
               JOIN behaviortypes AS t
                 ON r.behaviorid = t.behaviortypeid
        WHERE  r.childid = c.id
               AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
       totalPoints,
       (SELECT definitionname
        FROM   behaviordefinitions AS d
        WHERE  totalpoints BETWEEN d.min AND d.max)                  AS
       behaviorRating
FROM   children AS c  

fiddle :

这里是 SQL fiddle 的链接:http://sqlfiddle.com/#!9/fa06c/1/0

我期望看到的 Child 2 (Brynlee) 的结果是 100 而不是 99。

她从 100 开始，收到了 -3，然后收到了 +4。虽然我知道这个操作顺序的数学是正确的，但我需要对其进行调整以反射(reflect)我期望它如何反射(reflect)。 100 - 3 = 97 然后 97 + 4 = 101 (我们的最大值为 100，所以 100 将是totalPoints。

Answer 1

试试这个

SELECT c.id,
   c.name,
   DATE_FORMAT(
    FROM_DAYS(
        DATEDIFF(CURRENT_DATE, c.age)
    ),
    '%y Years %m Months %d Days'
  ) AS age,
   c.photoName,
   c.photoNameSmall,
   (SELECT CASE
             WHEN ( Ifnull(Sum(t.points), 0
                    ) + (SELECT settingValue
                                           FROM   settings
                                           WHERE  settingName = 'MaxPoints') >= (
                    SELECT
                           settingValue
                    FROM
                    settings
                    WHERE
                             settingName = 'MaxPoints') ) THEN 100
             WHEN ( Sum(t.points) <= 0 ) THEN ( (SELECT settingValue
                                                FROM   settings
                                                WHERE  settingName =
                                                       'MaxPoints')
                                               + Sum(t.points) )
             ELSE ( (SELECT settingValue
                     FROM   settings
                     WHERE  settingName = 'MaxPoints') -
                    Ifnull(Sum(t.points), (SELECT
                    settingvalue
                                           FROM   settings
                                           WHERE
                    settingName = 'MaxPoints')) )
           END
    FROM   behaviorRatings AS r
           JOIN behaviorTypes AS t
             ON r.behaviorID = t.behaviorTypeID
    WHERE  r.childid = c.id
           AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
   totalPoints,
   (SELECT definitionName
    FROM   behaviorDefinitions AS d
    WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
   behaviorRating
   FROM   children AS c

基本上，使用

WHEN ( Ifnull(Sum(t.points), (SELECT settingvalue
                              FROM   settings
                              WHERE  settingname = 'MaxPoints')
)

当 sum(t.points) 为 null 时，只会给你 100。要获得总分，您需要做

Ifnull(Sum(t.points), 0) + (SELECT settingvalue
                              FROM   settings
                              WHERE  settingname = 'MaxPoints')

这个sql可能更容易看

SET @maxPoints := (SELECT settingValue
              FROM   settings
              WHERE  settingName = 'MaxPoints');

SELECT c.id,
   c.name,
   DATE_FORMAT(
    FROM_DAYS(
        DATEDIFF(CURRENT_DATE, c.age)
    ),
    '%y Years %m Months %d Days'
  ) AS age,
   c.photoName,
   c.photoNameSmall,
   (SELECT CASE
             WHEN ( Ifnull(Sum(t.points), 0) + @maxPoints > @maxPoints ) THEN 100
             ELSE ( Ifnull(Sum(t.points), 0) + @maxPoints )
           END
    FROM   behaviorRatings AS r
           JOIN behaviorTypes AS t
             ON r.behaviorID = t.behaviorTypeID
    WHERE  r.childid = c.id
           AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
   totalPoints,
   (SELECT definitionName
    FROM   behaviorDefinitions AS d
    WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
   behaviorRating
   FROM   children AS c

使用 50 作为起点:

SET @maxPoints := (SELECT settingValue
          FROM   settings
          WHERE  settingName = 'MaxPoints');

SET @startingPoint := 50;

SELECT c.id,
c.name,
DATE_FORMAT(
FROM_DAYS(
    DATEDIFF(CURRENT_DATE, c.age)
),
'%y Years %m Months %d Days'
) AS age,
c.photoName,
c.photoNameSmall,
(SELECT CASE
         WHEN ( Ifnull(Sum(t.points), 0) + @startingPoint > @maxPoints ) THEN 100
         ELSE ( Ifnull(Sum(t.points), 0) + @startingPoint )
       END
FROM   behaviorRatings AS r
       JOIN behaviorTypes AS t
         ON r.behaviorID = t.behaviorTypeID
WHERE  r.childid = c.id
       AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
totalPoints,
(SELECT definitionName
FROM   behaviorDefinitions AS d
WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
behaviorRating
FROM   children AS c

总分超过限制后应用上限的 SQL

SET @maxPoints := (SELECT settingValue
            FROM   settings
            WHERE  settingName = 'MaxPoints');

SET @startingPoint := 50;

SELECT 
    c.id,
    c.name,
    DATE_FORMAT(
    FROM_DAYS(DATEDIFF(CURRENT_DATE, c.age)), '%y Years %m Months %d Days') AS age,
    c.photoName,
    c.photoNameSmall,
    (
        select x.tp 
        from 
        (
            SELECT t.childid,
                @rn:=CASE WHEN @cid <> t.childid THEN 0 ELSE @rn+1 END AS rn,
                @startingPoint + @tp:= CASE 
                    WHEN @cid <> t.childid 
                    THEN ifnull(t.points, 0)
                    ELSE (
                        case when @startingPoint + t.points + @tp > @maxPoints 
                        then @maxPoints - @startingPoint
                        else t.points + @tp end)
                    END AS tp,
                @cid:=t.childid AS clset,
                t.timestamp
            FROM
                (SELECT @tp:= -1) p,
                (SELECT @rn:= -1) n,
                (SELECT @cid:= -1) cd,
                (
                    SELECT r.childid, t.points, r.timestamp
                    FROM behaviorRatings AS r
                    JOIN behaviorTypes AS t ON r.behaviorID = t.behaviorTypeID
                    ORDER BY r.childid, r.timestamp
                ) t
        ) x
        where x.childid = c.id AND Date_format(x.timestamp, '%Y-%m-%d') = Curdate()
        order by x.childid, x.rn desc
        limit 1
    ) AS totalPoints,
    (
        SELECT definitionName
        FROM   behaviorDefinitions AS d
        WHERE  totalPoints BETWEEN d.min AND d.max
    ) AS behaviorRating
FROM   children AS c