我执行了第一个查询，但没有得到预期的结果。然后，我意识到1 slope被解释为整数，因此t.slope中的t.slope*pchp.sign*p.slope slope, t.intercept+t.slope*pchp.sign*p.intercept intercept也充当整数。
然后，我重复查询，但是这次将slope和intercept都转换为小数，并获得了正确的结果。
然后，我第二次重复查询，但只重复强制转换了slope，其值不是零，但不是intercept，并且还获得了正确的结果。
这使我想到了我的问题。什么时候需要类型转换SQL列？
MariaDB [testing]> WITH RECURSIVE t AS (
-> SELECT id, id pointsId, type, 0 value, 0 prevValue, 1 slope, 0 intercept
-> FROM points
-> WHERE id IN (406, 428)
-> UNION ALL
-> SELECT t.id, pchp.pointsId, p.type, p.value, p.prevValue, t.slope*pchp.sign*p.slope slope, t.intercept+t.slope*pchp.sign*p.intercept intercept
-> FROM t
-> INNER JOIN points_custom_has_points pchp ON pchp.pointsCustomId=t.pointsId
-> INNER JOIN points p ON p.id=pchp.pointsId
-> )
-> SELECT id, SUM(slope*value+intercept) value, SUM(slope*prevValue+intercept) prevValue FROM t WHERE type='real' GROUP BY id;
+-----+--------+-----------+
| id | value | prevValue |
+-----+--------+-----------+
| 406 | 0 | 0 |
| 428 | 123702 | 123702 |
+-----+--------+-----------+
2 rows in set (0.00 sec)
MariaDB [testing]> WITH RECURSIVE t AS (
-> SELECT id, id pointsId, type, 0 value, 0 prevValue, CAST(1 AS DECIMAL(12,4)) slope, CAST(0 AS DECIMAL(12,4)) intercept
-> FROM points
-> WHERE id IN (406, 428)
-> UNION ALL
-> SELECT t.id, pchp.pointsId, p.type, p.value, p.prevValue, t.slope*pchp.sign*p.slope slope, t.intercept+t.slope*pchp.sign*p.intercept intercept
-> FROM t
-> INNER JOIN points_custom_has_points pchp ON pchp.pointsCustomId=t.pointsId
-> INNER JOIN points p ON p.id=pchp.pointsId
-> )
-> SELECT id, SUM(slope*value+intercept) value, SUM(slope*prevValue+intercept) prevValue FROM t WHERE type='real' GROUP BY id;
+-----+-------------+-------------+
| id | value | prevValue |
+-----+-------------+-------------+
| 406 | 49480.8000 | 49480.8000 |
| 428 | 123702.0000 | 123702.0000 |
+-----+-------------+-------------+
2 rows in set (0.00 sec)
MariaDB [testing]> WITH RECURSIVE t AS (
-> SELECT id, id pointsId, type, 0 value, 0 prevValue, CAST(1 AS DECIMAL(12,4)) slope, 0 intercept
-> FROM points
-> WHERE id IN (406, 428)
-> UNION ALL
-> SELECT t.id, pchp.pointsId, p.type, p.value, p.prevValue, t.slope*pchp.sign*p.slope slope, t.intercept+t.slope*pchp.sign*p.intercept intercept
-> FROM t
-> INNER JOIN points_custom_has_points pchp ON pchp.pointsCustomId=t.pointsId
-> INNER JOIN points p ON p.id=pchp.pointsId
-> )
-> SELECT id, SUM(slope*value+intercept) value, SUM(slope*prevValue+intercept) prevValue FROM t WHERE type='real' GROUP BY id;
+-----+-------------+-------------+
| id | value | prevValue |
+-----+-------------+-------------+
| 406 | 49480.8000 | 49480.8000 |
| 428 | 123702.0000 | 123702.0000 |
+-----+-------------+-------------+
2 rows in set (0.00 sec)
MariaDB [testing]>
表定义如下：
MariaDB [testing]> explain points;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| idPublic | int(11) | NO | MUL | 0 | |
| accountsId | int(11) | NO | MUL | NULL | |
| name | varchar(45) | NO | MUL | NULL | |
| value | float | YES | | NULL | |
| prevValue | float | YES | | NULL | |
| units | varchar(45) | YES | | NULL | |
| type | char(8) | NO | MUL | NULL | |
| slope | float | NO | | 1 | |
| intercept | float | NO | | 0 | |
| tsValueUpdated | datetime | YES | | NULL | |
| sourceTypeId | tinyint(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
MariaDB [testing]> explain points_custom_has_points;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| pointsCustomId | int(11) | NO | PRI | NULL | |
| pointsId | int(11) | NO | PRI | NULL | |
| sign | tinyint(4) | NO | MUL | 1 | |
+----------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [testing]>
最佳答案
将CAST(1 AS DECIMAL(12,4)) slope更改为1.0 slope。如果可行，那么这里是解释：SELECT中的第一个UNION确定每一列的数据类型。因此，简单地说1会将斜率设置为某种整数形式（我认为BIGINT）。
您可能对其他专栏也有类似的问题。
我怀疑由于SELECTs您无法在该UNION中交换WITH。但这可能是另一回尝试。 （如果尝试过，请报告。）
关于mysql - 什么时候需要类型转换SQL列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52498138/