对日期为mysql.php的行中的列进行排序 - Sort column from within the rows on date mysql php

Sort column from within the rows on date mysql php

本文关键字：排序日期 mysql php | 更新日期: 2023-09-27

我有一个带有activated_at和is_paying列的表，我在其中存储日期时间，如2015-03-17 16:06:12，is_payining列是一个布尔列，默认情况下为1或0，现在我想在activated_date范围内按is_payin对项目进行排序。例如，我的表中有类似的记录

item_id     is_paying   activated_at
14186       1           2015-03-17 23:40:45 
13647       1           2015-03-17 12:43:52     
4404        0           2015-03-16 11:58:15     
14313       1           2015-03-16 10:42:24     
3806        0           2015-03-15 12:37:36     
10716       1           2015-03-15 11:01:32

现在我想要像一样的输出

14186       1           2015-03-17 23:40:45 
13647       1           2015-03-17 12:43:52     
14313       1           2015-03-16 10:42:24     
4404        0           2015-03-16 11:58:15
10716       1           2015-03-15 11:01:32     
3806        0           2015-03-15 12:37:36

它应该优先激活日期，然后在该日期内，如果设置为1，则应该将is_paying排序为top，有人能帮我吗

到目前为止，我有这个

select items.id, CASE WHEN (DATE(items.activated_at) = DATE(now())) THEN items.is_paying END as sort_order from group by items.id order by sort_order desc, items.activated_at desc

根据给定的预期结果，您似乎希望先按日期，然后按is_playing降序对数据进行排序。你可以做

select * from table_name order by date(activated_at) desc,is_playing desc;

以下是测试用例

mysql> create table test (item_id int,is_playing int , activated_at datetime);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values (14186,1,'2015-03-17 23:40:45'),(13647,1,'2015-03-17 12:43:52'),(4404,0,'2015-03-16 11:58:15'),(14313,1,'2015-03-16 10:42:24'),(3806,0,'2015-03-15 12:37:36'),(10716,1,'2015-03-15 11:01:32');
mysql> select * from test order by date(activated_at) desc,is_playing desc;
+---------+------------+---------------------+
| item_id | is_playing | activated_at        |
+---------+------------+---------------------+
|   14186 |          1 | 2015-03-17 23:40:45 |
|   13647 |          1 | 2015-03-17 12:43:52 |
|   14313 |          1 | 2015-03-16 10:42:24 |
|    4404 |          0 | 2015-03-16 11:58:15 |
|   10716 |          1 | 2015-03-15 11:01:32 |
|    3806 |          0 | 2015-03-15 12:37:36 |
+---------+------------+---------------------+
6 rows in set (0.00 sec)

试试这个

SELECT*FROM表格ORDER BY activated_at DESC，is _paying DESC

按以下顺序选择query

select * from mytable order by date(activated_at) desc, is_paying desc