Oracle SQL查询：查找IpAddress=1.2.3.4且Userid=User1的Object条目

针对你的需求，以下是几种可行的Oracle SQL查询方法：

方法一：自连接查询 #

通过将表与自身连接，确保同一个Object同时匹配两个键值条件：

SELECT DISTINCT t1.Object
FROM your_table t1
JOIN your_table t2 ON t1.Object = t2.Object
WHERE t1.Extension_key = 'IpAddress' 
  AND t1.Extension_value = '1.2.3.4'
  AND t2.Extension_key = 'Userid' 
  AND t2.Extension_value = 'User1';

方法二：分组过滤 #

先筛选出符合任一条件的记录，再通过分组统计确保Object同时满足两个条件：

SELECT Object
FROM your_table
WHERE (Extension_key = 'IpAddress' AND Extension_value = '1.2.3.4')
   OR (Extension_key = 'Userid' AND Extension_value = 'User1')
GROUP BY Object
HAVING COUNT(DISTINCT Extension_key) = 2;

方法三：PIVOT行转列查询 #

将键值对结构转为列结构，直接通过WHERE条件筛选，逻辑更直观：

SELECT Object
FROM your_table
PIVOT (
    MAX(Extension_value)
    FOR Extension_key IN ('IpAddress' AS IpAddress, 'Userid' AS Userid)
)
WHERE IpAddress = '1.2.3.4' 
  AND Userid = 'User1';

注意：将上述代码中的your_table替换为你实际的表名。

内容的提问来源于stack exchange，提问作者user3478415