返回内连接上最大值的行

Question

我不是SQL中最强的，但我一直在管理.在这种情况下，我的一个内部联接正在返回多个行，我正试图找到一种方法，使用名为“确定性”的列来获取具有最高值的行。

该语句涉及两个表，dim_asset和dim_asset_operating_system。这些表由一个名为asset_id的公共字段和一个名为os_description on dim_asset和description on dim_asset_operating_system的字段链接。

代码：

SELECT
    A.ip_address,
    A.os_type,
    A.os_vendor,
    A.os_family,
    A.os_name,
    A.os_version,
    A.os_description,
    A.os_system,
    B.certainty
FROM 
    dim_asset A
INNER JOIN 
    dim_asset_operating_system B ON A.asset_id = B.asset_id 
                                 AND A.os_description = B.description
WHERE 
    ip_address = '192.168.65.100';

输出：

   ip_address   | os_type | os_vendor | os_family | os_name | os_version |   os_description   |  os_system   | certainty
----------------+---------+-----------+-----------+---------+------------+--------------------+--------------+-----------
 192.168.65.100 |         | Ubuntu    | Linux     | Linux   | 16.04      | Ubuntu Linux 16.04 | Ubuntu Linux |         1
 192.168.65.100 |         | Ubuntu    | Linux     | Linux   | 16.04      | Ubuntu Linux 16.04 | Ubuntu Linux |         1
 192.168.65.100 |         | Ubuntu    | Linux     | Linux   | 16.04      | Ubuntu Linux 16.04 | Ubuntu Linux |      0.85
(3 rows)

理想情况下..。我想退货如下：

   ip_address   | os_type | os_vendor | os_family | os_name | os_version |   os_description   |  os_system   | certainty
----------------+---------+-----------+-----------+---------+------------+--------------------+--------------+-----------
 192.168.65.100 |         | Ubuntu    | Linux     | Linux   | 16.04      | Ubuntu Linux 16.04 | Ubuntu Linux |         1

在我上面的样本里，这只是一张唱片.理想情况下，我需要将其缩放到数以百万计的返回行中的10行。

谢谢你能提供的任何帮助。

Answer 1

如果您希望获得最高的确定性，只需将其ORDER BY并将结果集约束为FETCH FIST ROW ONLY即可。

SELECT * FROM dim_asset A
INNER JOIN dim_asset_operating_system B ON A.asset_id = B.asset_id AND
                                           A.os_description = B.description
WHERE ip_address = '192.168.65.100'
ORDER BY certainty DESC
FETCH FIRST ROW ONLY;

但是，如果多个记录共享相同的最高确定性(一个平分)，则此查询将只返回其中一个，在许多用例中，这不是一种理想的行为。如果您对共享最高值的所有记录感兴趣，请使用FETCH FIRST ROW WITH TIES (在PostgreSQL 13中引入)：

SELECT * FROM dim_asset A
INNER JOIN dim_asset_operating_system B ON A.asset_id = B.asset_id AND
                                           A.os_description = B.description
WHERE ip_address = '192.168.65.100'
ORDER BY certainty DESC
FETCH FIRST ROW WITH TIES;

演示：db<>fiddle