下面的查询提供了预期的结果,但没有使用
LEFT JOIN
. 所以
NULL
排除值。
SELECT
u.id AS user_id,
e1.date AS first_date,
e1.value AS first_value,
e2.date AS last_date,
e2.value AS last_value
FROM
users u,
(SELECT * FROM entries e ORDER BY date ASC) e1,
(SELECT * FROM entries e ORDER BY date DESC) e2
WHERE
e1.user_id = u.id
AND
e2.user_id = u.id
GROUP BY
u.id
这是一把小提琴-
http://sqlfiddle.com/#!9/71599/8
此外,值得注意的是
LIMIT
在您的尝试中,将所有联接结果的结果限制为1,而不是每个联接结果。不管怎样
左连接
不起作用。如果有人知道原因,我很想了解。
编辑:
这是另一次尝试,这次利用
MIN()
和
MAX()
而不是
ORDER BY
. 很遗憾,您需要加入
entries
表多次执行此操作。
SELECT
u.id AS user_id,
e1.date AS first_date,
e1.value AS first_value,
e2.date AS last_date,
e2.value AS last_value
FROM users u
INNER JOIN entries e1 ON (u.id = e1.user_id)
INNER JOIN entries e2 ON (u.id = e2.user_id)
INNER JOIN (
SELECT user_id, MIN(date) AS date
FROM entries
GROUP BY user_id
) e3 ON (e1.user_id = e3.user_id AND e1.date = e3.date)
INNER JOIN (
SELECT user_id, MAX(date) AS date
FROM entries
GROUP BY user_id
) e4 ON (e2.user_id = e4.user_id AND e2.date = e4.date)
GROUP BY u.id
另一把小提琴:
http://sqlfiddle.com/#!9/71599/18