在INNER JOIN上返回值最大的行

Question

我不是最强的SQL，但我一直在管理.....我有一种情况，其中一个我的内连接返回多行，我试图找到一种方法来抓取行与使用名为"确定性"列的最高值。

语句涉及两个表dim_asset和dim_asset_operating_system。这些表由一个名为asset_id的公共字段和一个名为os_description的字段在dim_asset和一个名为description的字段在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;

Demo:db<>fiddle