mysql - 使用 mysql LEFT Join DESC 命令不起作用

Question

这里我有两个表，我必须加入这两个表，我必须获取计划详细信息，我尝试过但没有发生，这是我的代码

用户信息

id                 fullName

1                    Arun
2                    Sarvan

user_active_plan

id      userId    planName
1          1      Free Plan
2          1      Cool Plan
3          2      Free Plan

contact_property

id      userId    contactProperty
1          1      A
2          1      B
3          2      C

这里 user_info(表名) id (列名) 我使用 user_active_plan(表名) userId(列名) 的外键

<小时/>

我想根据 userId 获取最新的计划，所以我使用 desc order ，但它没有达到预期的结果:

$sql = "SELECT a.fullName,b.*FROM user_info a LEFT JOIN user_active_plan b ON a.id = b.userId GROUP BY b.userId ORDER BY id DESC";
        $result = $this->GetJoinRecord($sql);
        print_r($result);

我得到以下错误结果:

Array
(
    [0] => Array
        (
            [fullName] => Sarvan
            [id] => 3
            [userId] => 2
            [planName] => Free Plan
        )
    [1] => Array
        (
            [fullName] => Arun
            [id] => 1
            [userId] => 1
            [planName] => Free Plan
        )
    )
)

我期待以下结果:

Array
(
    [0] => Array
        (
            [fullName] => Sarvan
            [id] => 3
            [userId] => 2
            [planName] => Free Plan
        )
    [1] => Array
        (
            [fullName] => Arun
            [id] => 2
            [userId] => 1
            [planName] => Coll Plan
        )
    )
)

Updated Expected Answer

    Array
(
    [0] => Array
        (
            [userId] => 1
            [fullName] => Arun
            [planId] => 2
            [planName] => Cool Plan
            [contactCount] => 2
        )

    [1] => Array
        (
            [userId] => 2
            [fullName] => Sarvan
            [planId] => 3
            [planName] => Free Pla1
            [contactCount] => 1
        )

      )

Answer 1

通过简单的子查询即可获取最新计划，无需分组。联系人的计数可以通过简单的分组来完成:

SELECT u.id AS userId, u.fullName, p.id AS planId, p.planName, COUNT(c.userId) AS contactCount
FROM user_info u
LEFT JOIN user_active_plan p ON u.id = p.userId
LEFT JOIN contact_property c ON u.id = c.userId
WHERE p.id = (SELECT id
              FROM user_active_plan
              WHERE userId = u.id
              ORDER BY id DESC
              LIMIT 1)
GROUP BY c.userId;

您还可以将条件从 WHERE 子句移至联接:

SELECT u.id AS userId, u.fullName, p.id AS planId, p.planName, COUNT(c.userId) AS contactCount
FROM user_info u
LEFT JOIN user_active_plan p ON u.id = p.userId
                             AND p.id = (SELECT id
                                         FROM user_active_plan
                                         WHERE userId = u.id
                                         ORDER BY id DESC
                                         LIMIT 1)
LEFT JOIN contact_property c ON u.id = c.userId
GROUP BY c.userId;