Zend DB 连接同一个表中的 2 行

Question

我有下表。

---------------------------------------------
check_id | action_id | user_id |         dt |
---------------------------------------------
       1 |         1 |       6 | 2011-09-17 |
       2 |         1 |       6 | 2011-09-18 |
       3 |         3 |       6 | 2011-09-19 |
       4 |         3 |       6 | 2011-09-20 |
---------------------------------------------

我想查询这个表并得到以下结果。

-----------------------------------------------
action_id | user_id |   dt_start |     dt_end |
-----------------------------------------------
       1 |        6 | 2011-09-17 | 2011-09-18 |
       3 |        6 | 2011-09-19 | 2011-09-20 |
-----------------------------------------------

所以我使用以下查询。

$checks->select()
->from(array('c1' => 'checks'), array('dt as dt_start')
->joinLeft(array('c2' => 'checks'), 'c1.action_id = c2.action_id', array('dt as dt_end')
->where('c1.user_id = ?', $userId)
->group('c1.action_id')

但这给了我以下结果。

-----------------------------------------------
action_id | user_id |   dt_start |     dt_end |
-----------------------------------------------
       1 |        1 | 2011-09-17 | 2011-09-17 |
       1 |        3 | 2011-09-19 | 2011-09-19 |
-----------------------------------------------

有人可以告诉我我做错了什么吗？

Answer 1

尝试这个查询，不使用分组依据：

SELECT *
FROM checks c1 LEFT OUTER JOIN checks c2 ON c1.action_id = c2.action_id 
WHERE c1.user_d = 6

您会看到您得到了 c1.dt < 的匹配项。 c2.dt，这就是你想要的。
但您也会得到 c1.dt > 的匹配项。 c2.dt，其中 c1.dt = c2.dt。
也就是说，自连接包括 c1 和 c2 指向同一行的结果。

然后，您使用 GROUP BY 将多行合并为一行，但 MySQL 会从组中选择任意行。 （这是一个不明确的查询，如果您SET SQL_MODE='ONLY_FULL_GROUP_BY'，您会收到错误。）

因此，您的自联接和 GROUP BY 仅返回 c1 和c2 实际上是同一行。

要解决此问题，您应该添加一个条件：c1.dt < c2.dt。

$checks->select()
->from(array('c1' => 'checks'), array('dt as dt_start')
->joinLeft(array('c2' => 'checks'), 
           'c1.action_id = c2.action_id AND c1.dt < c2.dt', 
           array('dt as dt_end')
->where('c1.user_id = ?', $userId)

在这种情况下，假设每个操作没有多个开始和结束，您可能根本不需要 GROUP BY。

顺便说一句，这种类型的复杂性是通常建议将事件开始/结束数据存储在单行中的原因之一，其中开始在一列中，结束在第二列中。