复杂顺序与条款

发布于 2025-01-23 13:39:26 字数 696 浏览 0 评论 0原文

我在尝试从MySQL查询订购结果时遇到了问题。

该表包含用户信息,尤其是:

  • user_status(integer)
  • user_verified(boolean)
  • last_login(date)

该订单按子句应将用户分组为6个主要组,并且在每个组中以last_login desc订购它们。

因此,我试图获得(在伪代码中):

first show users: user_status = 1 AND user_verified = true, last_login DESC
then: user_status = 1 AND user_verified = false, last_login DESC
then: user_status = 2 AND user_verified = true, last_login DESC
then: user_status = 2 AND user_verified = false, last_login DESC
then: user_status = 0 AND user_verified = true, last_login DESC
then: user_status = 0 AND user_verified = false, last_login DESC

我遇到问题,将其放在一条连贯的顺序中,以便对任何帮助都表示感谢!

I'm having problems trying to order the results from a MySQL query.

The table contains user information, notably:

  • user_status (an integer)
  • user_verified (boolean)
  • last_login (date)

The ORDER BY clause should group users into 6 main groups and within each group order them by the last_login DESC.

Thus I'm trying to get (in pseudo code):

first show users: user_status = 1 AND user_verified = true, last_login DESC
then: user_status = 1 AND user_verified = false, last_login DESC
then: user_status = 2 AND user_verified = true, last_login DESC
then: user_status = 2 AND user_verified = false, last_login DESC
then: user_status = 0 AND user_verified = true, last_login DESC
then: user_status = 0 AND user_verified = false, last_login DESC

I'm having problems putting this together into a coherent ORDER BY clause so any help would be appreciated!

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

七颜 2025-01-30 13:39:26

您可以在子句中使用顺序中的布尔表达式,因为它们被评估为1 true 0 0 for

ORDER BY user_status = 1 DESC,
         user_status = 2 DESC,
         user_status = 0 DESC,
         user_verified DESC,
         last_login DESC;

。。

ORDER BY CASE user_status 
           WHEN 1 THEN 1
           WHEN 2 THEN 2
           WHEN 0 THEN 3
         END,
         user_verified DESC,
         last_login DESC;

false -functions.html#function_field“ rel =“ nofollow noreferrer”> field() 函数:

ORDER BY FIELD(user_status, 1, 2, 0),
         user_verified DESC,
         last_login DESC;

You can use boolean expressions in the ORDER BY clause, because they are evaluated as 1 for true or 0 for false.

In your case you can simplify your requirement to:

ORDER BY user_status = 1 DESC,
         user_status = 2 DESC,
         user_status = 0 DESC,
         user_verified DESC,
         last_login DESC;

or:

ORDER BY CASE user_status 
           WHEN 1 THEN 1
           WHEN 2 THEN 2
           WHEN 0 THEN 3
         END,
         user_verified DESC,
         last_login DESC;

or with FIELD() function:

ORDER BY FIELD(user_status, 1, 2, 0),
         user_verified DESC,
         last_login DESC;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文