mysql组合行_如何在MYSQL中将行组合在一起？

我能想到三种基本方法.

一种方法是使用MySQL用户变量,一种方法使用theta JOIN,另一种方法使用SELECT列表中的子查询.

θ-JOIN

一种方法是使用theta-JOIN.这种方法是一种通用的SQL方法(没有MySQL特定的语法),可以与多个RDBMS一起使用.

注：对于大量行,此方法可以创建非常大的中间结果集,这可能导致有问题的性能.

SELECT o.e_id, MAX(i.date_time) AS in_time, o.date_time AS out_time

FROM e `o`

LEFT

JOIN e `i` ON i.e_id = o.e_id AND i.date_time < o.date_time AND i.in_out = 'I'

WHERE o.in_out = 'O'

GROUP BY o.e_id, o.date_time

ORDER BY o.date_time

这样做的是匹配每个’O’行的每个’O’行与之前的每个’I’行,然后我们使用MAX聚合来选择具有最接近日期时间的’I’记录.

这适用于完美配对的数据;可能会为不完美的对产生奇怪的结果……(两个连续的’O’记录没有中间的’I’行,都会匹配到同一个’I’行,等等)

SELECT列表中的相关子查询

另一种方法是在SELECT列表中使用相关子查询.这可能具有次优性能,但有时可行(并且有时是返回指定结果集的最快方法…当我们在外部查询中返回有限数量的行时,此方法效果最佳.)

SELECT o.e_id

, (SELECT MAX(i.date_time)

FROM e `i`

WHERE i.in_out = 'I'

AND i.e_id = o.e_id

AND i.date_time < o.date_time

) AS in_time

, o.date_time AS out_time

FROM e `o`

WHERE o.in_out = 'O'

ORDER BY o.date_time

用户变量

另一种方法是利用MySQL用户变量. (这是一种特定于MySQL的方法,是“缺失”分析函数的一种解决方法.)

这个查询的作用是按e_id排序所有行,然后按date_time排序,这样我们就可以按顺序处理它们.每当我们遇到’O'(out)行时,我们使用前一行’I’行中date_time的值作为’in_time’)

N.B.：MySQL用户变量的使用取决于MySQL以特定顺序执行操作,这是一个可预测的计划.内联视图(或MySQL派生词中的“派生表”)的使用为我们提供了可预测的执行计划.但是这种行为在未来的MySQL版本中可能会发生变化.

SELECT c.e_id

, CAST(c.in_time AS DATETIME) AS in_time

, c.out_time

FROM (

SELECT IF(@prev_e_id = d.e_id,@in_time,@in_time:=NULL) AS reset_in_time

, @in_time := IF(d.in_out = 'I',d.date_time,@in_time) AS in_time

, IF(d.in_out = 'O',d.date_time,NULL) AS out_time

, @prev_e_id := d.e_id AS e_id

FROM (

SELECT e_id, date_time, in_out

FROM e

JOIN (SELECT @prev_e_id := NULL, @in_time := NULL) f

ORDER BY e_id, date_time, in_out

) d

) c

WHERE c.out_time IS NOT NULL

ORDER BY c.out_time

这适用于您拥有的数据集,它需要更彻底的测试和调整,以确保您在行未完美配对时获得您想要的结果集(例如,两行’O’行没有’I’行他们之间,’I’行没有后续’O’行等等)