MySQL 单 DISTINCT 列

Question

此表包含主机和接口(interface)列UNIQUE 组合* 编辑:这个表也有一个自动递增的唯一 ID，抱歉我应该在之前提到这个 **

| host....  | interface..... | value      |
+-----------+----------------+------------+
| Host-0    | Interface-15   |    490     |
| Host-2    | Interface-4    |    490     |
| Host-3    | Interface-0    |    495     |
| Host-3    | Interface-7    |    485     |
| Host-5    | Interface-13   |    495     | 
| Host-5    | Interface-17   |    495     |
| Host-10   | Interface-9    |    490     |
| Host-11   | Interface-11   |    495     |
| Host-12   | Interface-9    |    485     |
| Host-12   | Interface-17   |    490     |

我想为 DISTINCT host 按值选择前 10 名

我试过:

SELECT host, interface, value FROM table ORDER BY value DESC LIMIT 10;

| host.... | interface..... | value     |
+----------+----------------+-----------+
| Host-0   | Interface-15   |   490     |
| Host-5   | Interface-17   |   495     |
| Host-5   | Interface-13   |   495     |
| Host-11  | Interface-11   |   495     |
| Host-3   | Interface-0    |   495     |
| Host-0   | Interface-15   |   490     |
| Host-12  | Interface-17   |   490     |
| Host-10  | Interface-9    |   490     |
| Host-2   | Interface-4    |   490     |
| Host-3   | Interface-7    |   485     |
| Host-12  | Interface-9    |   485     |

但我在主机中有重复项。我只需要显示具有最高值的不同主机

例如:Host-5 Interface-17 495主机12接口(interface)-17490

我也试过:

SELECT
  host,
  interface,
  value
FROM table
GROUP BY host
ORDER BY value DESC
LIMIT 10;

但是，我没有得到具有最高值的主机

Answer 1

您可以通过多种方式做到这一点。这是不存在方式:

SELECT host, interface, value 
FROM table t
WHERE NOT EXISTS (select 1
                  from table t2
                  where t2.host = t.host and t2.value > t.value
                 )
ORDER BY value DESC
LIMIT 10;

这表示:“获取表中具有相同主机但没有更高值的所有行。”

您也可以使用 group by 来做到这一点，使用 group_concat()/substring_index() 技巧:

select host, substring_index(group_concat(interface order by value desc), ',', 1) as interface,
       max(value)
from table t
group by host
order by max(value) desc
limit 10;