blocks|key|473126|text|您需要使用子查询来获取每个user+ID的最新日期。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|473127|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|code-block|syntax|javascript|473128|entityMap^0|D|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You need to have a subquery to get their latest date per <code>user ID</code>.

<pre><code>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
</code></pre>

blocks|key|473582|text|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
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|473583|或|unstyled|473584|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))
)|473585|这些解决方案比accepted+answer更好，因为当存在具有相同用户和日期的多个支付时，它们可以正常工作。你可以使用try+on+SQL+Fiddle。|offset|length|473586|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12526264/1118709|1|http://sqlfiddle.com/#!9/956d3c/4^0|0|0|0|7|F|0|1O|H|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|X|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|Z|8|@]|9|@$K|10|L|11|1|12]|$K|13|L|14|1|15]]|A|$]]|$1|M|3|-4|5|F|7|16|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]]]

<pre class="lang-sql prettyprint-override"><code>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
)
</code></pre>
Or
<pre class="lang-sql prettyprint-override"><code>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 &gt; p.date OR (p2.date = p.date AND p2.id &gt; p.id))
)
</code></pre>
These solutions are better than the <a href="https://stackoverflow.com/a/12526264/1118709">accepted answer</a> because they work correctly when there are multiple payments with same user and date. You can <a href="http://sqlfiddle.com/#!9/956d3c/4" rel="nofollow noreferrer">try on SQL Fiddle</a>.

blocks|key|256507|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|256508|查看此sqlfiddle|unstyled|offset|length|256509|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/9fd4c/1/0^0|0|3|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>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
</code></pre>

Check out this <a href="http://sqlfiddle.com/#!2/9fd4c/1/0" rel="nofollow noreferrer">sqlfiddle</a>

blocks|key|473146|text|+++SELECT+u.*+
++++++++FROM+users+AS+u
++++++++INNER+JOIN+(
++++++++++++SELECT+p.*,
+++++++++++++@num+:=+if(@id+=+user_id,+@num+%2B+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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|473147|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> 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
</code></pre>

blocks|key|257003|text|@John+Woo的回答帮助我解决了一个类似的问题。我也通过设置正确的顺序改进了他的答案。这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|257004|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|code-block|syntax|javascript|257005|不过，我不确定这有多有效。|257006|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

@John Woo's answer helped me solve a similar problem. I've improved upon his answer by setting the correct ordering as well. This has worked for me:

<pre><code>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
</code></pre>

I'm not sure how efficient this is, though.

blocks|key|257178|text|您可以尝试这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|257179|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|code-block|syntax|javascript|257180|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this:

<pre><code>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
</code></pre>

blocks|key|473680|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|473681|这会让它工作起来|unstyled|473682|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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
</code></pre>

This will get it working

blocks|key|256754|text|Matei+Mihai给出了一个简单而有效的解决方案，但它只有在SELECT+part中放入一个MAX(date)才能工作，所以这个查询将变成：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|256755|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|code-block|syntax|javascript|256756|和order+by不会对分组产生任何影响，但它可以对group+by提供的最终结果进行排序。我试过了，它对我很有效。|256757|entityMap^0|1C|9|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Matei Mihai given a simple and efficient solution but it will not work until put a <code>MAX(date)</code> in SELECT part so this query will become:

<pre class="lang-sql prettyprint-override"><code>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
</code></pre>

And order by will not make any difference in grouping but it can order the final result provided by group by. I tried it and it worked for me.

blocks|key|473548|text|我的回答直接来自@valex，非常有用，如果您在ORDER+BY子句中需要几个参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|473549|++++SELECT+u.*+
++++FROM+users+AS+u
++++INNER+JOIN+(
++++++++SELECT+p.*,
+++++++++@num+:=+if(@id+=+user_id,+@num+%2B+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|code-block|syntax|javascript|473550|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

My answer directly inspired from @valex very usefull, if you need several cols in the ORDER BY clause. 

<pre><code> 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
</code></pre>

blocks|key|257243|text|这非常简单，执行内连接，然后按user_id分组，并在payment_id中使用max聚合函数，假设您的表是用户和支付查询，可以是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|257244|SELECT+user.id,+max(payment.id)
FROM+user+INNER+JOIN+payment+ON+(user.id+=+payment.user_id)
GROUP+BY+user.id|code-block|syntax|javascript|257245|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is quite simple do The inner join and then group by user_id and use max aggregate function in payment_id assuming your table being user and payment query can be
<pre><code>SELECT user.id, max(payment.id)
FROM user INNER JOIN payment ON (user.id = payment.user_id)
GROUP BY user.id
</code></pre>

I have a <code>users</code> table and a <code>payments</code> table, for each user, those of which have payments, may have multiple associated payments in the <code>payments</code> table. I would like to select all users who have payments, but only select their latest payment. I'm trying this SQL but i've never tried nested SQL statements before so I want to know what i'm doing wrong. Appreciate the help

<pre><code>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
</code></pre>

MySQL INNER JOIN select only one row from second table

我有一个users表和一个payments表，对于每个用户，其中的那些有付款，在payments表中可能有多个关联的付款。我想选择所有的用户谁有付款，但只选择他们的最新付款。我正在尝试这个SQL，但我以前从未尝试过嵌套SQL语句，所以我想知道我做错了什么。感谢你的帮助SELECT u.* FROM users AS u    INNER JOIN (        SELECT p.*      

问MySQL内连接仅从第二个表中选择一行
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL内连接仅从第二个表中选择一行EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL内连接仅从第二个表中选择一行
EN