开发中的mysql需求（按时间排序和显示行号）

按时间排序但是会有时间缺失

1. 按小时排序

```sql
SELECT hour24.hour AS TIME, IFNULL(t2.total, 0) AS total FROM 
(SELECT 0 HOUR UNION ALL SELECT 1 HOUR UNION ALL SELECT 2 HOUR UNION ALL SELECT 3 HOUR UNION ALL SELECT 4 HOUR UNION ALL SELECT 5 HOUR UNION ALL SELECT 6 HOUR UNION ALL SELECT 7 HOUR UNION ALL SELECT 8 HOUR UNION ALL SELECT 9 HOUR UNION ALL SELECT 10 HOUR UNION ALL SELECT 11 HOUR UNION ALL SELECT 12 HOUR
UNION ALL SELECT 13 HOUR UNION ALL SELECT 14 HOUR UNION ALL SELECT 15 HOUR UNION ALL SELECT 16 HOUR UNION ALL SELECT 17 HOUR UNION ALL SELECT 18 HOUR UNION ALL SELECT 19 HOUR UNION ALL SELECT 20 HOUR UNION ALL SELECT 21 HOUR UNION ALL SELECT 22 HOUR UNION ALL SELECT 23 HOUR ) hour24
LEFT JOIN 
(SELECT MIN(TIME) AS sttime, HOUR(TIME) AS TIME,COUNT(1) AS total FROM t_ids_alert WHERE attack_type='VIRUS' AND TIME >   STR_TO_DATE(DATE_FORMAT(ADDDATE(SYSDATE(), -1) , '%Y-%m-%d %00:00:00'), '%Y-%m-%d %H:%i:%s') AND TIME < SYSDATE() GROUP BY HOUR(TIME) ) t2
ON hour24.hour =t2.time WHERE hour24.hour <= HOUR(SYSDATE());

2. 按月份排序

SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL -t.help_topic_id DAY),'%Y-%m-%d') AS curr_date
FROM mysql.help_topic t WHERE t.help_topic_id<=30
```sql
在这里插入代码片

3. 查询结果显示行号

SELECT @rowNum:=@rowNum+1 AS rowid,a.kind,a.total
FROM(
SELECT a.lib_date AS kind,COUNT(a.ip) AS total
FROM t_virus_lib_client_list a
LEFT JOIN t_meta_dict_item d
ON a.`location`=d.`item_code`
WHERE d.`item_name`='南京市' AND DATE_SUB(CURDATE(), INTERVAL 6 DAY) < DATE(collect_time) AND d.`dict_code`='map_location'
GROUP BY a.`lib_date`
ORDER BY total DESC) a,(SELECT @rowNum:=0) b