为什么五个数之和1 = 4 [英] why is the sum of five 1 = 4

问题描述

基本查询按预期方式工作，但是当我尝试对第一列求和时，其假定为5，但提示我得到4，为什么呢?

the base query works as intenden, but when i try to sum the first columns, its supose to be 5, but insted i get 4, why?

基本查询:

SET @last_task = 0;
SELECT 
  IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
  @last_task := RobotShortestPath
FROM rob_log
ORDER BY rog_log_id;

1   1456
0   1456
0   1456
1   1234
0   1234
1   1456
1   2556
1   1456

总和查询

SET @last_task = 0;
SELECT SUM(new_task) AS tasks_performed
FROM (
  SELECT 
    IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
    @last_task := RobotShortestPath
  FROM rob_log
  ORDER BY rog_log_id
) AS tmp

4

表结构

CREATE TABLE rob_log (
  rog_log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
#  RobotPosX FLOAT NOT NULL,
#  RobotPosY FLOAT NOT NULL,
#  RobotPosDir TINYINT UNSIGNED NOT NULL,
  RobotShortestPath MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY(rog_log_id),
  KEY (rog_log_id, RobotShortestPath)
);

INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1234;
INSERT INTO rob_log(RobotShortestPath) SELECT 1234;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 2556;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;

在sqlfiddle上进行测试: http://sqlfiddle.com/#!2/e80f5/3 作为使用MySQL计算时间轴更改的答案 但是变得迷茫

testing it at sqlfiddle: http://sqlfiddle.com/#!2/e80f5/3 as an answer for Counting changes in timeline with MySQL but got relly confused

推荐答案

这是原因(如 Twitter上所述):

变量@last_task是在单独的查询批"中定义的.我将对SQL Fiddle的查询分解为单独的批处理，分别执行.我这样做是为了您可以在下面看到每个批次的输出作为不同的结果集.在您的小提琴中，您可以看到有两组输出: http://sqlfiddle. com/#！2/e80f5/3/0 和 http://sqlfiddle .com/#！2/e80f5/3/1 .这些映射到您正在运行的两个语句(集合和选择).问题是，您的set语句定义了仅在第一批中存在的变量；当select语句运行时，它是一个单独的批处理，并且您的变量未在该上下文中定义.

The variable @last_task was defined in a separate query "batch". I break up the queries on SQL Fiddle into individual batches, executed separately. I do this so you can see the output from each batch as a distinct result set below. In your Fiddle, you can see that there are two sets of output: http://sqlfiddle.com/#!2/e80f5/3/0 and http://sqlfiddle.com/#!2/e80f5/3/1. These map to the two statements you are running (the set and the select). The problem is, your set statement defines a variable that only exists in the first batch; when the select statement runs, it is a separate batch and your variable isn't defined within that context.

要解决此问题，您所要做的就是定义一个不同的查询终止符.请注意模式和查询面板([;])下的下拉框/按钮-单击它，您可以选择除分号(默认值)以外的其他内容.然后，您的两个语句将作为同一批处理的一部分一起包含在内，您将获得所需的结果.例如: http://sqlfiddle.com/#!2/e80f5/9

To correct this problem, all you have to do is define a different query terminator. Note the dropdown box/button under both the schema and the query panels ( [ ; ] ) - click on that, and you can choose something other than semicolon (the default). Then your two statements will be included together as part of the same batch, and you'll get the result you want. For example: http://sqlfiddle.com/#!2/e80f5/9

这篇关于为什么五个数之和1 = 4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！