MySQLINNERJOIN 仅从第二个表中选择一行

我有一个 users表和一个 payments表，为每个用户，其中有付款，可能有多个相关的付款在 payments表。我想选择所有用户谁有付款，但只选择他们的最新付款。我正在尝试这个 SQL 语句，但是我从来没有尝试过嵌套的 SQL 语句，所以我想知道我做错了什么。谢谢你的帮助

SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*
FROM payments AS p
ORDER BY date DESC
LIMIT 1
)
ON p.user_id = u.id
WHERE u.package = 1
288714 次浏览
小开 
SELECT u.*, p.*, max(p.date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id
ORDER BY p.date DESC

看看这个 Sqlfiddle

小开
最佳答案

您需要有一个子查询来获取每个 user ID的最新日期。

SELECT  a.*, c.*
FROM users a
INNER JOIN payments c
ON a.id = c.user_ID
INNER JOIN
(
SELECT user_ID, MAX(date) maxDate
FROM payments
GROUP BY user_ID
) b ON c.user_ID = b.user_ID AND
c.date = b.maxDate
WHERE a.package = 1
小开

您的查询有两个问题:

  1. 每个表和子查询都需要一个名称，因此必须将子查询命名为 INNER JOIN (SELECT ...) AS p ON ...。
  2. 您所拥有的子查询只返回一行周期，但您实际上需要一行 每个用户。为此，您需要一个查询来获得最大日期，然后自我连接回来获得整个行。

假设 payments.date没有平局，试试看:

    SELECT u.*, p.*
FROM (
SELECT MAX(p.date) AS date, p.user_id
FROM payments AS p
GROUP BY p.user_id
) AS latestP
INNER JOIN users AS u ON latestP.user_id = u.id
INNER JOIN payments AS p ON p.user_id = u.id AND p.date = latestP.date
WHERE u.package = 1
小开 
   SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*,
@num := if(@id = user_id, @num + 1, 1) as row_number,
@id := user_id as tmp
FROM payments AS p,
(SELECT @num := 0) x,
(SELECT @id := 0) y
ORDER BY p.user_id ASC, date DESC)
ON (p.user_id = u.id) and (p.row_number=1)
WHERE u.package = 1
小开

Matei Mihai 给出了一个简单而有效的解决方案，但它不会工作，直到把一个 MAX(date)在 SELECT 部分，所以这个查询将成为:

SELECT u.*, p.*, max(date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id

并且按顺序排列不会对分组产生任何影响，但是它可以按顺序排列组提供的最终结果。我试过了，很管用。

小开

@ 吴宇森的回答帮助我解决了一个类似的问题。通过设置正确的顺序，我改进了他的答案。这对我很有效:

SELECT  a.*, c.*
FROM users a
INNER JOIN payments c
ON a.id = c.user_ID
INNER JOIN (
SELECT user_ID, MAX(date) as maxDate FROM
(
SELECT user_ID, date
FROM payments
ORDER BY date DESC
) d
GROUP BY user_ID
) b ON c.user_ID = b.user_ID AND
c.date = b.maxDate
WHERE a.package = 1

不过我不确定这有多高效。

小开

我的回答直接受@valex 的启发，非常有用，如果您需要在 ORDERBY 子句中使用多个联号。

    SELECT u.*
FROM users AS u
INNER JOIN (
SELECT p.*,
@num := if(@id = user_id, @num + 1, 1) as row_number,
@id := user_id as tmp
FROM (SELECT * FROM payments ORDER BY p.user_id ASC, date DESC) AS p,
(SELECT @num := 0) x,
(SELECT @id := 0) y
)
ON (p.user_id = u.id) and (p.row_number=1)
WHERE u.package = 1
小开 
SELECT u.*, p.*
FROM users AS u
INNER JOIN payments AS p ON p.id = (
SELECT id
FROM payments AS p2
WHERE p2.user_id = u.id
ORDER BY date DESC
LIMIT 1
)

或者

SELECT u.*, p.*
FROM users AS u
INNER JOIN payments AS p ON p.user_id = u.id
WHERE NOT EXISTS (
SELECT 1
FROM payments AS p2
WHERE
p2.user_id = p.user_id AND
(p2.date > p.date OR (p2.date = p.date AND p2.id > p.id))
)

这些解决方案比 接受的答案更好，因为它们在同一用户和日期进行多次支付时能正常工作。你可以 试试 SQL Fiddle。

小开

你可以试试这个:

SELECT u.*, p.*
FROM users AS u LEFT JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY [Date] DESC) AS RowNo
FROM payments
) AS p ON u.userid = p.userid AND p.RowNo=1
小开 
SELECT U.*, V.* FROM users AS U
INNER JOIN (SELECT *
FROM payments
WHERE id IN (
SELECT MAX(id)
FROM payments
GROUP BY user_id
)) AS V ON U.id = V.user_id

这会让它起作用的

小开

这是非常简单的做内部连接，然后分组的用户 _ id 和使用最大聚合函数的 pay _ id 假设您的表是用户和支付查询可以

SELECT user.id, max(payment.id)
FROM user INNER JOIN payment ON (user.id = payment.user_id)
GROUP BY user.id

提交答案