You need to enable JavaScript to run this app. 查询满足双条件的设备去重MAC地址及UNION实现疑问
优惠活动
大模型
产品
解决方案
定价
更多

查询满足双条件的设备去重MAC地址及UNION实现疑问

双条件设备MAC查询实现说明

核心误区澄清

这个场景 根本不需要用到UNION:
UNION的作用是合并两个结构一致的独立查询结果集,适配的是「满足条件A 或者 满足条件B」的并集查询场景。而我们的需求是同一设备必须同时满足两个硬约束,属于交集判断,强行用UNION会把逻辑变成「满足任意一个条件就返回」,完全不符合题目的双要求,还会带来额外的去重性能开销。

需求对应约束梳理

最终需要返回的去重MAC地址,必须同时符合两个要求:

  • 该设备在DeviceUser关联表中存在用户名为Hack3r的使用记录
  • 该设备作为连接主机(即对应Connects表的host字段)时,起始时间落在2021年的连接总次数超过100次

可直接落地的SQL实现

写法1:CTE+取交集(逻辑最直观)

-- 先筛选出所有存在Hack3r用户的设备MAC
WITH hack3r_devices AS (
    SELECT DISTINCT mac 
    FROM DeviceUser 
    WHERE user = 'Hack3r'
),
-- 再筛选出2021年作为主机连接次数超100次的设备MAC
freq_over_100_host AS (
    SELECT host AS mac
    FROM Connects
    -- 按使用的数据库替换时间提取函数:MySQL用YEAR(timestamp)=2021,PG用EXTRACT(YEAR FROM timestamp)=2021
    WHERE EXTRACT(YEAR FROM timestamp) = 2021
    GROUP BY host
    HAVING COUNT(*) > 100
)
-- 两个集合取交集,就是同时满足两个条件的设备,结果天然去重
SELECT mac FROM hack3r_devices
INTERSECT
SELECT mac FROM freq_over_100_host;

写法2:INNER JOIN关联(数据库兼容性更强)

如果所用数据库不支持INTERSECT语法,可以改用内连接实现交集判断,无需额外去重:

SELECT DISTINCT du.mac
FROM DeviceUser du
-- 直接关联符合连接次数要求的主机集合
INNER JOIN (
    SELECT host
    FROM Connects
    WHERE YEAR(timestamp) = 2021
    GROUP BY host
    HAVING COUNT(*) > 100
) valid_host ON du.mac = valid_host.host
WHERE du.user = 'Hack3r';

注意:如果错误使用UNION拼接两个筛选结果,最终返回的是「存在Hack3r用户的设备」和「2021年连接超100次的主机」的并集,只要满足任意一个条件就会被返回,完全偏离需求。


内容的提问来源于stack exchange,提问作者Seanio Parselli

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.23 06:24:04