代码之家 › 专栏 › 技术社区 › user1032531

何时需要类型转换SQL列?

mariadb casting mysql

user1032531 · 技术社区 · 6 年前

我执行了第一个查询,但没有得到预期的结果。然后我意识到 1 slope 被解释为整数,因此 t.slope 在里面 t.slope*pchp.sign*p.slope slope, t.intercept+t.slope*pchp.sign*p.intercept intercept

然后我重复了这个问题,但这次两个 slope 和 intercept 作为小数,得到了正确的结果。

斜坡 拦截 ,也得到了正确的结果。

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]>

1 回复 | 直到 6 年前

Rick James diyism 6 年前

改变 CAST(1 AS DECIMAL(12,4)) slope 到 1.0 slope . 如果这样做行得通,那么这里有一个解释:

这个第一 SELECT UNION 确定每列的数据类型。所以,简单地说 1 会将斜率设置为某种形式的整数(我想 BIGINT ).

您可能对其他列有类似的问题。

SELECTs 在那里面 工会 因为 WITH