MySQL统计结果集中列值的变化

Question

我有两个表:

1) task - 代表任务。它只有一个主键，因为所有相关数据都在task_version表中(task HAS_MANY task_version)。

CREATE TABLE task(
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

示例数据:

INSERT INTO task VALUES ('1');
INSERT INTO task VALUES ('2');

2) task_version - 任何任务中的任何更改都会在此表中创建新行。 task_id 应该是外键(为简单起见省略)。这是为了拥有任务中所有变化的完整记录。

CREATE TABLE `task_version` (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
task_id int(11) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
text varchar(255) DEFAULT NULL,
status int(11) DEFAULT NULL,
PRIMARY KEY (id)
);

示例数据:

INSERT INTO `task_version` VALUES ('1', '1', 'Name of task', 'Text of task', '1');
INSERT INTO `task_version` VALUES ('2', '1', 'Name of task', 'Text of task', '1');
INSERT INTO `task_version` VALUES ('3', '1', 'Name of task', 'Text of task', '2');
INSERT INTO `task_version` VALUES ('4', '1', 'Name of task', 'Text of task', '1');
INSERT INTO `task_version` VALUES ('5', '2', 'Name', 'Text', '1');

我需要的是获取每个任务的状态更改数量。

显然我不能只查询这样的不同状态:

SELECT
(
  SELECT
  COUNT(DISTINCT status)
  FROM task_version
  WHERE task_id = t.id
) AS distinct_statuses_per_task,
t.id AS task_id
FROM task t
INNER JOIN task_version tv ON t.id = tv.task_id
GROUP BY t.id

因为distinct_statuses_per_task只是不同的值，不会改变数量。如果有人将状态从 1 更改为 2，从 2 更改为 1，然后再次从 1 更改为 2，我们将得到以下状态序列:

1
2
1
2

所以我们有 2 个不同的状态 (1, 2)，但有 3 个状态变化 (1>2, 2>1, 1>2)，所以它不起作用。

我开发了带有 MySQL 用户变量的解决方案。这是我想嵌入到主查询中的子查询:

SELECT
CASE WHEN (status != @prev_status AND @prev_status IS NOT NULL)
THEN @status_changes_quantity := @status_changes_quantity + 1
END as incrementing_logic,
@status_changes_quantity AS status_changes_quantity,
@prev_status := status AS save_prev
FROM task_version,
(
    SELECT
    @prev_status := NULL,
    @status_changes_quantity := 0
) as task_version_with_additional_vars
WHERE task_id = 1 --Hardcoded task_id
ORDER BY status_changes_quantity DESC
LIMIT 1

这可以作为带有硬编码task_id的独立查询。但我需要将此查询嵌入为子查询，以获取每个任务的状态更改数量。

我无法让它工作。问题是，当我在 SELECT 查询部分设置变量时，它们成为查询结果的一部分。子查询应该返回单个标量，但我的查询返回表(incrementing_logic，status_changes_quantity，save_prev)我不知道sintax如何摆脱这个不需要的列(incrementing_logic，save_prev)。

我尝试过这个:

SELECT
(
    SELECT
    CASE WHEN (status != @prev_status AND @prev_status IS NOT NULL)
    THEN @status_changes_quantity := @status_changes_quantity + 1
    END as incrementing_logic,
    @status_changes_quantity AS status_changes_quantity,
    @prev_status := status AS save_prev
    FROM task_version,
    (
        SELECT
        @prev_status := NULL,
        @status_changes_quantity := 0
    ) as task_version_with_additional_vars
    WHERE task_id = t.id
    ORDER BY status_changes_quantity DESC
    LIMIT 1
) AS status_changes_quantity,
t.id AS task_id,
tv.status AS task_status
FROM task t
INNER JOIN task_version tv ON t.id = tv.task_id

显然得到:

[Err] 1241 - Operand should contain 1 column(s)

然后我尝试将子查询表包装到另一个 tmp 表以摆脱变量字段和 ger 标量值:

SELECT
(
    SELECT
    status_changes_quantity
    FROM
    (
        SELECT

            CASE WHEN (status != @prev_status AND @prev_status IS NOT NULL)
            THEN @status_changes_quantity := @status_changes_quantity + 1
            END as incrementing_logic,

            @status_changes_quantity AS status_changes_quantity,

            @prev_status := status AS save_prev

        FROM task_version,
            (
                SELECT
                    @prev_status := NULL,
                    @status_changes_quantity := 0
            ) as task_version_with_additional_vars
        WHERE task_id = t.id
        ORDER BY status_changes_quantity DESC
        LIMIT 1
    ) AS tmp_table
) AS status_changes_quantity,
t.id AS task_id,
tv.status AS task_status
FROM task t
INNER JOIN task_version tv ON t.id = tv.task_id

我还收到一个错误，t.id 现在在子查询范围内不可见:

[Err] 1054 - Unknown column 't.id' in 'where clause'

也许有人知道如何解决我的问题。纠正我的查询或建议完全不同的算法。

提前致谢。

Answer 1

我稍微修改了您的查询:

SELECT task_id, max( status_changes_quantity )
FROM (
  SELECT 
      task_id, id, 
      CASE WHEN @prev_task_id <> task_id 
             THEN @status_changes_quantity := 0
           WHEN status != @prev_status 
             THEN @status_changes_quantity := @status_changes_quantity + 1
           ELSE @status_changes_quantity
      END status_changes_quantity,
      @prev_task_id := task_id,
      @prev_status := status
  FROM task_version,
  (
      SELECT
      @prev_status := NULL,
      @prev_task_id := null,
      @status_changes_quantity := 0
  ) as task_version_with_additional_vars
  -- WHERE task_id = 1
  ORDER BY task_id, id
) q
GROUP BY task_id
ORDER BY 2 DESC

演示 --> http://www.sqlfiddle.com/#!2/c9ecc/14

此查询计算所有task_id的状态更改数量，
并且仅适用于一项给定任务 - 如果您取消注释 -- WHERE task_id = 1 子句。