左连接、排序依据、MySQL 优化

发布于 2024-10-18 08:58:47 字数 596 浏览 5 评论 0原文

我有一个这样的查询：

   SELECT m...., a...., r....
     FROM 0_member AS m                 
LEFT JOIN 0_area AS a ON a.user_id = (SELECT user_id 
                                        FROM `0_area` 
                                       WHERE user_id = m.id 
                                    ORDER BY sec_id ASC LIMIT 1)
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

想法是从 0_area 表获取第一行，从而尝试内部联接。但是，它并没有按预期工作。

在0_area和0_member之间，0_member.id = 0_area.user_id。但是，有多行 0_area.user_id，我希望该行具有最低的 sec_id 值。

有什么帮助吗？

原文

I have a query like this:

   SELECT m...., a...., r....
     FROM 0_member AS m                 
LEFT JOIN 0_area AS a ON a.user_id = (SELECT user_id 
                                        FROM `0_area` 
                                       WHERE user_id = m.id 
                                    ORDER BY sec_id ASC LIMIT 1)
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

The idea is to get the first row from 0_area table and hence the attempted inner join. However, it is not working as expected.

Between 0_area and 0_member, 0_member.id = 0_area.user_id. However, there are multiple rows of 0_area.user_id and I want the row having the lowest value of sec_id.

Any help please?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栖迟 2024-10-25 08:58:47

   SELECT m...., a...., r....
     FROM 0_member AS m               
LEFT JOIN (SELECT user_id, min(sec_id) minsec
           FROM `0_area`
           GROUP BY user_id) g1 on g1.user_id=m.id
LEFT JOIN 0_area AS a ON a.user_id = g1.user_id and a.sec_id=minsec
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

   SELECT m...., a...., r....
     FROM 0_member AS m               
LEFT JOIN (SELECT user_id, min(sec_id) minsec
           FROM `0_area`
           GROUP BY user_id) g1 on g1.user_id=m.id
LEFT JOIN 0_area AS a ON a.user_id = g1.user_id and a.sec_id=minsec
LEFT JOIN 0_rank as r ON a.rank_id = r.id 
    WHERE m.login_userid = '$username'

回复收藏 0 原文

~没有更多了~