>我有一个活动策划者mysql
数据库,我正在尝试获取城市表的报告,以显示按每个事件的天数排序的所有城市。
例如,一些记录是:
event_id - city_id - start - end
1 - 88 - 2019-01-01 - 2019-01-05
2 - 89 - 2019-02-01 2019-02-03
3 - 90 - 2019-03-01 2019-03-04
4 - 88 - 2019-01-06 - 2019-01-07
该报告可以是:
city_id - days
88 - 7
90 - 4
89 - 3
你可以用这样的查询来做到这一点:
SELECT event_id , city_id, SUM( DATEDIFF( `end`,`start`)+1 ) as days
FROM sample
GROUP BY city_id
order by SUM( DATEDIFF( `end`,`start`) +1 ) DESC;
样本
MariaDB [test]> select * from sample;
+----------+---------+------------+------------+
| event_id | city_id | start | end |
+----------+---------+------------+------------+
| 1 | 88 | 2019-01-01 | 2019-01-05 |
| 2 | 89 | 2019-02-01 | 2019-02-03 |
| 3 | 90 | 2019-03-01 | 2019-03-04 |
| 4 | 88 | 2019-01-06 | 2019-01-07 |
+----------+---------+------------+------------+
4 rows in set (0.229 sec)
MariaDB [test]>
MariaDB [test]> SELECT event_id , city_id, SUM( DATEDIFF( `end`,`start`)+1 ) as days
-> FROM sample
-> GROUP BY city_id
-> order by SUM( DATEDIFF( `end`,`start`) +1 ) DESC;
+----------+---------+------+
| event_id | city_id | days |
+----------+---------+------+
| 1 | 88 | 7 |
| 3 | 90 | 4 |
| 2 | 89 | 3 |
+----------+---------+------+
3 rows in set (0.001 sec)
MariaDB [test]>
使用以下查询 -
select
event_id, city_id, DATEDIFF(end, start) as days
from
table_name
order by DATEDIFF(end, start) desc