我有一个 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
您需要有一个子查询来获取每个 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
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))
)
这些解决方案比 accepted answer 更好,因为它们在使用相同用户和日期进行多次付款时可以正常工作。您可以try on SQL Fiddle。
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
limit 1
子句只会返回 1 个用户,这不是 OP 想要的。
date
列与 max(p.date)
不同。如果您在 payments
表中添加更多列(例如 cost
),则所有这些列都不会来自需要的行
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
你可以试试这个:
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
您的查询有两个问题:
每个表和子查询都需要一个名称,因此您必须将子查询命名为 INNER JOIN (SELECT ...) AS p ON ...。您拥有的子查询仅返回一个行期间,但实际上您希望每个行都有一个用户。为此,您需要一个查询来获取最大日期,然后自行加入以获取整行。
假设 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
From payments as p Where p.user_id =@user_id
,因为查询正在对整个表进行分组。
@John Woo 的回答帮助我解决了类似的问题。我还通过设置正确的顺序改进了他的答案。这对我有用:
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
不过,我不确定这有多有效。
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
这将使它工作
Matei Mihai 给出了一个简单有效的解决方案,但只有在 SELECT 部分放置一个 MAX(date)
才会起作用,因此该查询将变为:
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
并且 order by 不会对分组产生任何影响,但它可以对 group by 提供的最终结果进行排序。我试过了,它对我有用。
如果您在 ORDER BY 子句中需要几个列,我的回答直接来自 @valex 非常有用。
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
这是非常简单的内部联接,然后按 user_id 分组,并在 payment_id 中使用 max 聚合函数,假设您的表是用户并且付款查询可以是
SELECT user.id, max(payment.id)
FROM user INNER JOIN payment ON (user.id = payment.user_id)
GROUP BY user.id
不定期副业成功案例分享
Part 1 - Joins and Unions
。 :) 收藏!