mysql - 查找其他两列中每个唯一值出现的最小值

Question

给出以下(简化的)表格:

人p

id  name    registered
-----------------------------------
1   Geoff   2011-03-29 12:09:08
2   Phil    2011-04-29 09:03:54
3   Tony    2011-05-29 21:22:23
4   Gary    2011-06-21 22:56:08
...

项目i

date                        p1id    p2id    
----------------------------------------
2011-06-29 20:09:44         1       2
2011-06-26 10:45:00         1       3
2011-06-23 12:22:43         2       3
2011-06-22 13:07:12         2       4
...

我想要: 每个p.id出现在p1id或p2id<任一列中的最早的单个i.date/;或p.registered(如果两者都不包含)。

到目前为止，我已经尝试过:

CREATE TEMPORARY TABLE temp (id INT);
INSERT INTO temp (id) 
    SELECT DISTINCT u FROM (
        SELECT p1id AS u FROM Items UNION ALL 
        SELECT p2id AS u FROM Items
    )tt;
SELECT registered,id FROM People 
    WHERE id NOT IN (SELECT id FROM temp);

这让我到达了第二部分，尽管方式相当笨拙；除了通过 p.id 的所有值进行某种外部脚本化迭代之外，我还停留在第一部分(呃)。

有人可以帮忙吗？

我使用的是 MySQL 5.1，有大约 20k 人和大约 100k 项。

Answer 1

另一种解决方案:

SELECT id, name, IF(min_date1 IS NULL AND min_date2 IS NULL, registered, LEAST(COALESCE(min_date1, min_date2), COALESCE(min_date2, min_date1))) date FROM (
  SELECT p.id, p.name, p.registered, MIN(i1.date) min_date1, MIN(i2.date) min_date2 FROM people p
    LEFT JOIN items i1
      ON p.id = i1.p1id
    LEFT JOIN items i2
      ON p.id = i2.p2id
  GROUP BY id
) t;

或者这个:

SELECT p.id, p.name, COALESCE(MIN(i.date), p.registered) FROM people p
  LEFT JOIN (
    SELECT p1id id, date FROM items
      UNION ALL
    SELECT p2id id, date FROM items
  ) i
  ON p.id = i.id
GROUP BY id;

结果:

+------+-------+---------------------+
| id   | name  | date                |
+------+-------+---------------------+
|    1 | Geoff | 2011-06-26 10:45:00 |
|    2 | Phil  | 2011-06-22 13:07:12 |
|    3 | Tony  | 2011-06-23 12:22:43 |
|    4 | Gary  | 2011-06-22 13:07:12 |
+------+-------+---------------------+