使用mysql left join desc order不起作用

rt4zxlrg 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(324)

这里我有两个表，我必须加入这两个表，我必须得到计划的细节，我试过，但没有发生，这是我的代码
用户信息

id                 fullName

1                    Arun
2                    Sarvan

用户\活动\计划

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

联系酒店

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

在这里user\u info（tablename）id（列名）我正在使用user\u active\u plan（tablename）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
        )
    )
)

更新预期答案

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
        )

      )

sql mysql

来源：https://stackoverflow.com/questions/48191626/using-mysql-left-join-desc-order-is-not-working

1条答案

按热度按时间

xxslljrj1#

您可以通过一个简单的子查询获得最新的计划，无需分组。联系人的计数可以通过简单的分组完成：

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;

赞(0）回复(0）举报 2021-06-25

我来回答

使用mysql left join desc order不起作用

1条答案

相关问题

热门标签

最新问答