MySQL单列

Question

这个表有主机列和接口列UNIQUE combination *编辑:这个表还有一个自动递增的唯一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     |

但我在主机上有副本。我只需要显示一个具有最高价值的独立主机。

例如:主机-5接口-17 495主机12接口-17 490

我也尝试过：

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

但是，我不会得到价值最高的主机

Answer 1

你可以用不同的方式做这件事。下面是not exists的方法：

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;

Answer 2

试试这个：

SELECT host, interface, value
FROM table t1
WHERE value = (SELECT MAX(value) 
    FROM table t2 
    WHERE t2.host = t1.host)
AND interface = (SELECT 
    t3.interface
    FROM table t3 
    WHERE t3.value = t1.value
    AND t3.host = t1.host limit 1)
ORDER BY value DESC LIMIT 10;