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
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
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小时内的事物设置为将来添加随机日期的某些值。