MySQL按类别和子类别排序 [英] MySQL order by category and subcategory
本文介绍了MySQL按类别和子类别排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试对 MySQL 中的一些 Nagios 警报进行排序.
I am trying to sort some Nagios alerts in MySQL.
我希望将第 2 列按现在的方式分组在一起.所有警报均已分组.问题是我希望这些组按降序排列.例如，所有主持人 | 所有活动 | 65 |"应该在顶部，接下来是All Hosts | memory-usage-UNX | 25"，然后是 25 memory-usage-UNX 警报.接下来是所有主机 | CPU-Load-UNX | 9"，然后是其余的 CPU-Load-UNX 警报，按计数降序排列.
I would like column 2 to be grouped together as it is now. All alerts are grouped. The problem is that i would like those groups to be in descending order. For example, "All Hosts | All Events | 65 |" should be at the top, next would be "All Hosts | memory-usage-UNX | 25", this would be followed by the 25 memory-usage-UNX alerts. Next would be "All Hosts | CPU-Load-UNX | 9" and then the rest of the CPU-Load-UNX alerts in descending order based on count.
这是我的查询.
SELECT ifnull(Host,'All Hosts') as Host
,ifnull(Event,'All Events') as Event
,Event_Count
FROM
(
SELECT Host
,name2 as Event
-- ,Occurrences
,count(name2) as Event_Count
FROM
(
SELECT ifnull(nbo.name1,'All Hosts') as Host
,nbo.name2
,count(1) as Occurrences
FROM
nagios_notifications no
,nagios_objects nbo
WHERE
no.object_id = nbo.object_id
AND nbo.name1 in (select nh.display_name
from nagios_hostgroup_members nhm
,nagios_hosts nh
where nhm.host_object_id = nh.host_object_id
and nhm.hostgroup_id in (select hostgroup_id
from nagios_hostgroups
where alias in ('Production-and-Critical-IT')
)
)
AND no.start_time >= now() - interval 1 week
AND no.state = 2 -- change to != 0 for everything that's not OK
GROUP BY nbo.name1,nbo.name2 ASC WITH ROLLUP
) a
-- where Host = 'All Hosts' or name2 is not null
WHERE name2 is not null
GROUP BY Event,Host with rollup
) b
order by Event, Host, Event_Count;
这是输出.
+--------------------+------------------------------------------+-------------+
| Host | Event | Event_Count |
+--------------------+------------------------------------------+-------------+
| All Hosts | All Events | 65 |
| All Hosts | check_activesync-WIN | 1 |
| pexc001 | check_activesync-WIN | 1 |
| All Hosts | check_asterisk_extension_error-TEL | 1 |
| pteln004 | check_asterisk_extension_error-TEL | 1 |
| All Hosts | check_dag-WIN | 1 |
| pexc001 | check_dag-WIN | 1 |
| All Hosts | check_exchange_mailqueue-WIN | 1 |
| pexc001 | check_exchange_mailqueue-WIN | 1 |
| All Hosts | check_health_measure-JVA | 2 |
| pwspr031 | check_health_measure-JVA | 1 |
| pwspr049 | check_health_measure-JVA | 1 |
| All Hosts | check_http_prod-JVA | 4 |
| putpr002 | check_http_prod-JVA | 1 |
| putpr004 | check_http_prod-JVA | 1 |
| pwspr027 | check_http_prod-JVA | 1 |
| pwspr031 | check_http_prod-JVA | 1 |
| All Hosts | check_jmx_HeapMemoryUsage_background-JVA | 1 |
| putpr001 | check_jmx_HeapMemoryUsage_background-JVA | 1 |
| All Hosts | check_tomcat-UNX | 1 |
| putpr002 | check_tomcat-UNX | 1 |
| All Hosts | check_total_procs-UNX | 1 |
| naggy2 | check_total_procs-UNX | 1 |
| All Hosts | check_w305_KC_standby-DBA | 1 |
| pdbdrkc01 | check_w305_KC_standby-DBA | 1 |
| All Hosts | cisco-cpu-load-UNX | 1 |
| crrt01.pr1 | cisco-cpu-load-UNX | 1 |
| All Hosts | CPU-Load-UNX | 9 |
| pfspr02 | CPU-Load-UNX | 1 |
| psearchpr005 | CPU-Load-UNX | 1 |
| psearchpr006 | CPU-Load-UNX | 1 |
| psearchpr007 | CPU-Load-UNX | 1 |
| psearchpr008 | CPU-Load-UNX | 1 |
| putpr001 | CPU-Load-UNX | 1 |
| putpr004 | CPU-Load-UNX | 1 |
| pwspr031 | CPU-Load-UNX | 1 |
| pwspr049 | CPU-Load-UNX | 1 |
| All Hosts | disk-usage-UNX | 1 |
| pmsvpr007 | disk-usage-UNX | 1 |
| All Hosts | memory-usage-UNX | 26 |
| p0crmpr001 | memory-usage-UNX | 1 |
| p0crmpr002 | memory-usage-UNX | 1 |
| pmsvpr002 | memory-usage-UNX | 1 |
| pmsvpr008 | memory-usage-UNX | 1 |
| putpr001 | memory-usage-UNX | 1 |
| pwspr013 | memory-usage-UNX | 1 |
| pwspr014 | memory-usage-UNX | 1 |
| pwspr019 | memory-usage-UNX | 1 |
| pwspr022 | memory-usage-UNX | 1 |
| pwspr024 | memory-usage-UNX | 1 |
| pwspr025 | memory-usage-UNX | 1 |
| pwspr026 | memory-usage-UNX | 1 |
| pwspr027 | memory-usage-UNX | 1 |
| pwspr030 | memory-usage-UNX | 1 |
| pwspr031 | memory-usage-UNX | 1 |
| pwspr032 | memory-usage-UNX | 1 |
| pwspr033 | memory-usage-UNX | 1 |
| pwspr036 | memory-usage-UNX | 1 |
| pwspr041 | memory-usage-UNX | 1 |
| pwspr042 | memory-usage-UNX | 1 |
| pwspr043 | memory-usage-UNX | 1 |
| pwspr044 | memory-usage-UNX | 1 |
| pwspr045 | memory-usage-UNX | 1 |
| pwspr046 | memory-usage-UNX | 1 |
| pwspr049 | memory-usage-UNX | 1 |
| pwspr050 | memory-usage-UNX | 1 |
| All Hosts | new_pending_phone-TEL | 1 |
| pdbpr01 | new_pending_phone-TEL | 1 |
| All Hosts | prod-best-match-JVA | 1 |
| Prod-MicroServices | prod-best-match-JVA | 1 |
| All Hosts | prod-consumer-rating-rewards-JVA | 1 |
| Prod-MicroServices | prod-consumer-rating-rewards-JVA | 1 |
| All Hosts | prod-credit-request-JVA | 1 |
| Prod-MicroServices | prod-credit-request-JVA | 1 |
| All Hosts | prod-favorite-pros-JVA | 1 |
| Prod-MicroServices | prod-favorite-pros-JVA | 1 |
| All Hosts | prod-ha-spd-update-JVA | 1 |
| Prod-MicroServices | prod-ha-spd-update-JVA | 1 |
| All Hosts | prod-payment-batch-JVA | 1 |
| Prod-MicroServices | prod-payment-batch-JVA | 1 |
| All Hosts | prod-session-log-loader-JVA | 1 |
| Prod-MicroServices | prod-session-log-loader-JVA | 1 |
| All Hosts | prod-sm-caldav-event-JVA | 1 |
| Prod-MicroServices | prod-sm-caldav-event-JVA | 1 |
| All Hosts | prod-sp-task-coverage-indexer-JVA | 1 |
| Prod-MicroServices | prod-sp-task-coverage-indexer-JVA | 1 |
| All Hosts | prod-watcher-JVA | 1 |
| Prod-MicroServices | prod-watcher-JVA | 1 |
| All Hosts | prod-ws-api-security-JVA | 1 |
| Prod-MicroServices | prod-ws-api-security-JVA | 1 |
| All Hosts | prod-ws-communication-JVA | 1 |
| Prod-MicroServices | prod-ws-communication-JVA | 1 |
| All Hosts | prod-ws-entity-JVA | 1 |
| Prod-MicroServices | prod-ws-entity-JVA | 1 |
+--------------------+------------------------------------------+-------------+
推荐答案
在查询结束时，请确保在 order by 子句中指定顺序
At the end of the query just make sure you specify the order in the order by clause
Order by host asc, event asc, event_count desc
这篇关于MySQL按类别和子类别排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
