如何在子查询中使用自连接？

Question

数据：

create table `values` (
     id integer unsigned primary key auto_increment,
     value double,
     `time` time
);

insert into `values` (time, value) values
  ('00:00:01', 1),
  ('00:00:02', 2),
  ('00:00:03', 3),
  ('00:01:01', 4),
  ('00:01:02', 5),
  ('00:01:03', 6);

同时使用相同的 http://sqlfiddle.com/#!9/ec3e8/1 进行测试和修改。

SELECT
    vs1.time as t1,
    vs1.value as v1,
    vs2.time as t2,
    vs2.value as v2
FROM `values` vs1, `values` vs2
JOIN (
    SELECT MIN(time) as mint, MAX(time) as maxt FROM `values`
    GROUP BY hour(time), minute(time)
) as te on vs1.time = te.mint AND vs2.time = te.maxt
WHERE vs1.id != vs2.id AND hour(vs1.time) = hour(vs2.time) AND minute(vs1.time) = minute(vs2.time)

我在mysql中有一个时间序列数据。我想用一条SQL查询聚合一些统计信息。我希望按时间分组值，然后从组中获取最小值、最大值、第一个值和最后一个值。

我知道mysql没有聚合函数、ctes、first和last函数，否则这将是微不足道的。

因此，我的计划是创建所有行的乘积（将表与自身连接），然后将其与每个间隔的最小和最大时间连接起来，以从间隔中获取第一个和最后一个值，但是这会导致错误。

“on clause”中的“Unknown column 'vs1.time'”是什么问题？我需要使用相同的行乘积来获取最小值和最大值吗，还是需要使用“group by”连接另一个查询？

另一个选择是制作一个过程或在某种编程语言中执行它。过程不是一个选项。我想比较原始SQL和在编程语言中执行的性能。

这应该适用于mysql 5.6。

- user1685095

---

MySQL 8.0拥有CTE和窗口函数。但是在我写这篇文章时，MySQL 8.0仍然只处于发布候选状态。 - Bill Karwin

@BillKarwin 我知道，谢谢。 - user1685095

期望的结果会是什么样子？ - Strawberry

Answer 1

您正在混合使用 pre-1992 的逗号分隔连接语法和正确的 ANSI 连接，这是不应该的。文档 (https://dev.mysql.com/doc/refman/5.7/en/join.html) 明确指出：

然而，逗号运算符的优先级小于 INNER JOIN、CROSS JOIN、LEFT JOIN 等。如果在存在连接条件的情况下混合使用逗号连接和其他连接类型，则可能会出现“on clause”中未知列 'col_name' 的错误。

用 CROSS JOIN 替换逗号可以解决问题： FROM `values` vs1 CROSS JOIN `values` vs2。

但我发现大多数您的条件都是多余的。同时更改连接顺序以提高可读性 (尤其是为了避免假装进行交叉连接)：

SELECT
    vs1.time as t1,
    vs1.value as v1,
    vs2.time as t2,
    vs2.value as v2
FROM 
(
  SELECT MIN(time) as mint, MAX(time) as maxt 
  FROM `values`
  GROUP BY hour(time), minute(time)
) as te
JOIN `values` vs1 ON vs1.time = te.mint
JOIN `values` vs2 ON vs2.time = te.maxt
ORDER BY vs1.time;

- Thorsten Kettner

---

这不是一个错误，而是“连接优先级”不同。 - Strawberry

@Strawberry：啊，我不知道那个。你是对的，甚至在文档中都有。谢谢，我会更新我的答案。 - Thorsten Kettner