根据另一列的最大值选择多列
根据另一列的最大值选择多列
提问于 2016-04-19 09:48:21
我有和Oracle 11g数据库，有以下三个表(简化)：
IP表，包含IP标识符、IP和IP状态以及FQDN。IPs可能会被重复。
+-------+-------------+-----------+-----------+
| ID_IP | IP | IP_STATUS | FQDN |
+-------+-------------+-----------+-----------+
| 1 | 192.168.1.1 | 1 | test.com |
| 2 | 192.168.1.1 | 2 | test.com |
| 3 | 192.168.1.1 | 3 | test.com |
| 4 | 10.10.45.12 | 2 | test2.com |
+-------+-------------+-----------+-----------+VLAN表、包含和VLAN标识符以及VLAN编号
+---------+-------------+
| VLAN_ID | VLAN_NUMBER |
+---------+-------------+
| 1 | 3 |
| 2 | 5 |
| 3 | 7 |
+---------+-------------+与虚拟局域网和综合方案相关的表格：
+-------+---------+
| IP_ID | VLAN_ID |
+-------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 2 |
+-------+---------+在实际的IP表中，主键是元组(IP，IP_STATUS)。我的目标是创建一个消除IP_STATUS的新表，为此，我希望聚合IP并获得VLAN_NUMBER较高的IP的ID_IP和FQDN。SELECT查询的答案如下所示：
+-------+-------------+-----------+
| ID_IP | IP | FQDN |
+-------+-------------+-----------+
| 3 | 192.168.1.1 | test.com |
| 4 | 10.10.45.12 | test2.com |
+-------+-------------+-----------+我可以使用以下查询获得IP：
SELECT i.IP, max(v.VLAN_ID)
FROM IPS i
LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP
LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA
GROUP BY i.IP;我不知道的是如何得到其他列。我尝试使用一个子查询，如下所示：
SELECT i.ID_IP, i.IP, i.FQDN
FROM IPS i
WHERE i.IP IN (
SELECT i.IP, max(v.VLAN_ID)
FROM IPS i
LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP
LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA
GROUP BY i.IP;
)但是它不起作用，因为子查询返回两个值，我需要max(vlan.VLAN_ID)来完成聚合。
我怎么才能得到正确的IP_ID？
谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-04-19 10:36:32
您可以使用一个分析子句按照IP进行拆分，然后按VLAN_NUMBER进行排序，然后筛选以保留每个组中的第一行：
SELECT ID_IP, IP, FQDN
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY i.IP ORDER BY v.VLAN_NUMBER DESC) AS NB,
i.ID_IP, i.IP, i.FQDN
FROM IPS i
LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP = v_i.ID_IP
LEFT JOIN VLANS v ON v_i.VLAN_ID = v.VLAN_ID
) t_a
WHERE NB = 1Stack Overflow用户
发布于 2016-04-19 10:35:34
您可能需要尝试使用WITH子句。粗略地..。
WITH IPWITHMAXVLANID(IP, MAXVLAN) AS (
SELECT i.IP, max(v.VLAN_ID)
FROM IPS i
LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP
LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA
GROUP BY i.IP
)
SELECT i.ID_IP, i.IP, i.FQDN, iml.MAXVLAN
FROM IPS i
INNER JOIN IPWITHMAXVLANID iml on i.IP = imp.IP希望这能有所帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36714742
复制相关文章
相似问题
腾讯云开发者
