mysql - 在mysql中连接两个表时对一个表使用限制

Question

在这种情况下，如何使用限制和连接正确编写 sql 语句？

SELECT  u.userName, u.userFriends, u.userJoined, f.freindName (only 3) 
FROM    user u inner join friends f ON u.userId = f.addedUserId 
WHERE   u.userId=1 
AND     f.userId=1

我试过了

SELECT  u.userName, u.userFriends, u.userJoined, group_concat(f.freindName) 
FROM    user u inner join friends f ON u.userId = f.userId 
WHERE   u.userId = 1 and f.addedUserId = 1 
GROUP   by u.userName limit 5 /* but this does not work */

你能帮忙把 f.freindName 限制为 3吗

'gid'  | 'userName'  | 'userId' |   'userFriends'   |'userJoined'
-------|-------------|----------|-------------------|--------------
'1'    | 'Jason'     | '1'      |   '5'             |'14-Aug-2014'


'gid'  | 'friendName'|  'thisUserId'    |'addedUserId'
-------|-------------|------------------|-----------------
'1'    | 'James'     |  '2'             |'1'
'2'    | 'Lars'      |  '3'             |'1'
'3'    | 'Kirk'      |  '4'             |'1'
'4'    | 'Rob'       |  '5'             |'1'
'5'    | 'Dave'      |  '5'             |'1'

Answer 1

我假设表 users 和 friends 由列 userId 和 addedUserId 连接。我进一步假设您想要一个包含 userId = 1 的用户的三个 friend 的列表。

您可以通过使用子选择来使用派生表来获得所需的结果:

SELECT  u.userName, u.userFriends, u.userJoined, group_concat(f.friendName) 
FROM    user u 
INNER JOIN (
    SELECT
        userId,
        friendName
    FROM
        friends
    LIMIT 3
) f
ON u.userId = f.addedUserId 
WHERE   u.userId = 1
GROUP   by u.userName

应该可以。