mysql - 如何在与另一个表连接之前找到一组数据的模式？

Question

这是我正在查看的两个表:

k3_alert_types

Type       Description

0            No Show
1            Stop Arrival
2            ...
3            ...
4            ...
5            ...

k3_alert

Type  

1
22
33
2
4
5
65
33
1

这些表格只是示例，因为实际的数据集要大得多。我想做的是在 k3_alert 表中找到类型的模式，我已经用以下方法完成了:

SELECT TYPE , number_of_alerts
FROM
(

    SELECT id, TYPE, COUNT(TYPE) AS number_of_alerts FROM k3_alert
    GROUP BY TYPE
)t1
WHERE number_of_alerts IN
(

    SELECT MAX( count_type ) FROM
    (

        SELECT id, TYPE , COUNT(TYPE ) AS count_type FROM k3_alert
        GROUP BY TYPE
    )t
)

我知道如何连接两个表:

SELECT k3_alert_types.description, k3_alert_types.type as type
FROM k3_alert_types
INNER JOIN k3_alert ON k3_alert_types.type = k3_alert.type
ORDER BY type

但我不知道如何同时执行这两项操作。

我想把这看作是整个过程的结果(只是一个例子):

Description  Type  number_of_alerts

No Show      1     350

有什么建议吗？

编辑:服务器类型:MariaDB，PHP扩展:mysql

Answer 1

这应该有效:

SELECT at.description, at.type, COUNT(*) as number_of_alerts
FROM k3_alert_types at
INNER JOIN k3_alert a ON at.type = a.type
GROUP BY at.description, at.type
ORDER BY number_of_alerts DESC 
LIMIT 1