mysql命令分为两个不同的部分

I've a situation where i need a double order by. Let me try to explain it (for simplicity i've removed unnecessary columns).

我有一个情况，我需要一个双重订单。让我试着解释一下（为简单起见，我删除了不必要的列）。

(currentDate = 2017-07-31)

（currentDate = 2017-07-31）

I've a table called user like so

我有一个像用户这样的用户表

 user_id | username | user_changed
 ---------------------------------
 1       | John Doe | 2017-07-31 12:22:58 (less then 48 hours)
 2       | Test name| 2017-07-30 09:17:18 (less the 48 hours)
 4       | itRocks2 | 2017-07-28 07:45:52 (greater then 48 hours)
 3       | itRocks  | 2017-07-29 07:45:52 (greater then 48 hours)

Now comes the tricky part. I need to order into two different parts

现在是棘手的部分。我需要订购两个不同的部分

Part one: The first part needs to be a random order by user_changed where user_changed is 48 hours ago or less.

第一部分：第一部分需要是user_changed的随机顺序，其中user_changed是48小时前或更短时间。

Part two: Needs to order the remaining users where the user_changed is greater then 48 hours with order by user_changed ASC

第二部分：需要通过user_changed ASC订购其中user_changed大于48小时的剩余用户

So with the example data from our database this should be the result

因此，使用我们数据库中的示例数据，这应该是结果

1 | John Doe | 2017-07-31 12:22:58

1 | John Doe 2017-07-31 12:22:58

2 | Test name| 2017-07-30 09:17:18

2 |测试名称| 2017-07-30 09:17:18

These two needs a random order because they are less then 48 hours All the other users (greater the 48 hours) needs an order by user_changed ASC, like so

这两个需要一个随机顺序，因为它们不到48小时所有其他用户（大于48小时）需要user_changed ASC的订单，就像这样

3 | itRocks | 2017-07-29 07:45:52 (example 52 hours)

3 | itRocks | 2017-07-29 07:45:52（例子52小时）

4 | itRocks2 | 2017-07-28 07:45:52 (example 60 hours)

4 | itRocks2 | 2017-07-28 07:45:52（例如60小时）

Also i need a default order by user_id for grouping the users later on in my view. So some queries i've tried:

此外，我还需要user_id的默认订单，以便稍后在我的视图中对用户进行分组。所以我试过一些问题：

query 1

查询1

SELECT      *, if(user_changed >= SUBDATE(NOW(), INTERVAL 48 HOUR), RAND(), 0) AS rndOrder
FROM        users 
ORDER BY    user_id, rndOrder ASC

query 2

查询2

SELECT      *, if(user_changed >= SUBDATE(NOW(), INTERVAL 48 HOUR), "true", "false") AS rndOrder
FROM        users 
ORDER BY    user_id ASC,    
            CASE 
                WHEN rndOrder = 1 THEN user_changed RAND()
                WHEN frndOrder = 0 THEN user_changed ASC
            END;

1 个解决方案

#1

I would try something like this:

我会尝试这样的事情：

SELECT      *
FROM        users 
ORDER BY    user_id ASC,    
        CASE 
            WHEN user_changed <= SUBDATE(NOW(), INTERVAL 48 HOUR) THEN
                 ADDDATE( CURDATE(), 1 + (FLOOR( 1 + RAND( ) * 10000 )))
            ELSE user_changed
        END ASC;

Basically, keep the order consistent, but set the things from the last 48 hours to be some value in the future with a random date added.

基本上，保持订单一致，但是将过去48小时内的事物设置为将来添加随机日期的某些值。

#1