V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
yuann72
V2EX  ›  问与答

能不能用 SQL 查出没有在数据表里出现的值

  •  
  •   yuann72 · 354 天前 · 2664 次点击
    这是一个创建于 354 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一个数据表只有一个 name 列, 这个数据表只有两行, 分别是 A 和 B 。

    name
    A
    B

    现在有几个数据,分别是 A,B,C,D ,有没有办法使用 SQL 语句查出 A,B,C,D 这 4 个中哪些没有出现在数据表里?
    按我举的这个例子,最后应该输出 C 和 D

    25 条回复    2023-04-10 10:48:39 +08:00
    yuann72
        1
    yuann72  
    OP
       354 天前
    我问了 chatGPT ,它完全没理解我的意思😅


    可以使用 SQL 中的 NOT IN 子句来解决这个问题。具体地,可以写出如下的 SQL 语句:
    SELECT name FROM table_name WHERE name NOT IN ('A', 'B', 'C', 'D');
    其中,table_name 是要查询的数据表名,name 是列名,'A', 'B', 'C', 'D' 是要查询的字符串值。这个语句会返回那些在数据表中没有出现的 name 值。

    在你举的例子中,执行这个语句会得到以下结果:
    C
    D
    因为 C 和 D 没有出现在这个数据表中。
    Ambition95
        2
    Ambition95  
       354 天前
    把 abcd 搞成表 t1 ,假设原表是 t2
    select
    *
    from t1
    left anti join t2
    on t1.name = t2.name
    hahastudio
        3
    hahastudio  
       354 天前
    https://stackoverflow.com/questions/8007821/sql-single-query-to-return-values-that-are-not-present
    但是一般会问为什么会要有这样的需求,不能从 SQL 外解决吗
    tomczhen
        4
    tomczhen  
       354 天前 via Android
    按集合的概念来描述就应该能理解了。
    Biggoldfish
        5
    Biggoldfish  
       354 天前 via Android
    WHERE NOT IN 就行吧
    yjim
        6
    yjim  
       354 天前
    你这个需求脱离 SQL 实现方便点,如果一定要用 SQL 来执行的话,了解下存储过程
    Hurriance
        7
    Hurriance  
       354 天前
    基于你的「几个数据」建立临时表 t1 ,用 not in 对比 原表 t2 ,不过这种实现似乎并不太「优雅」?

    <script src="https://gist.github.com/KailokFung/fb75a7a6c1bd8aa75c40ac3e5f58971f.js"></script>
    xiangyuecn
        8
    xiangyuecn  
       354 天前   ❤️ 2
    牛逼到不行的 union all ,轻松构造多行数据

    select name from

    (
    select 'A' as name
    union all select 'B' as name
    union all select 'C' as name
    union all select 'D' as name
    ) as List

    where name not in(
    -- select name from 你的表
    (
    select 'A' as name
    union all select 'B' as name
    )
    )
    akira
        9
    akira  
       354 天前
    SQL 查有不查无。
    构建一个 临时表 放好 ABCD ,然后再来做这个事情 就简单了。
    yuann72
        10
    yuann72  
    OP
       354 天前
    @Ambition95 #2
    @hahastudio #3
    @yjim #6
    @Hurriance #7
    @xiangyuecn #8
    有时会需要临时查下一批数据中哪些没有出现在表里。
    目前的做法确实是用 SQL 以外的方式实现,但不够方便,就是把表的数据查出来复制到 EXCEL 里进行对比,有些麻烦,数据表里只有几千行还行,要是大几万行就更麻烦。
    通过创建临时表的方式也行但也不够方便,建表>插入数据>查询完>删表,而且如果是在生产环境里查,生产环境里也不给建临时表啊
    yuann72
        11
    yuann72  
    OP
       354 天前
    SQL 里有没有这种函数:可以传入任意个参数,自动转成一张临时表。
    这样就能用这张临时表来 JOIN 或者 NOT IN 查询了
    yjim
        12
    yjim  
       354 天前
    @yuann72 你这几万行数据指的是有几万个不同的 name ?还是指有几万条 name 数据,但是来来去去就那么几个值?

    如果是后者可以用 select distinct(name) from table 去重。
    lower
        13
    lower  
       354 天前
    布隆过滤?
    yjim
        14
    yjim  
       354 天前
    @yuann72 建表>插入数据>查询完>删表 你这一套方法用存储过程,不需要建表也可以完成,而且写一次存储过程下次直接在数据库里调用就可以获得检查结果了。 缺点是存储过程的写法和坑实在让人蛋疼。好处嘛,一次写完下次直接跑就行了。
    akira
        15
    akira  
       354 天前
    生产环境 单独加一个库 专门用来放这种临时需求的表数据。
    而且这种查询的表 你可以保留下来,不做删除,方便后续回查。

    如果你们有数仓的话,这类查询放数仓就更方便了。
    awen233333
        16
    awen233333  
       354 天前 via iPhone
    @yuann72 表值函数返回 ABCD ,然后用 minus 运算符减去 AB
    fackVL
        17
    fackVL  
       354 天前 via iPhone
    感觉你走两个极端了,人家说另外的方式不是说导出来用 Excel 。可以试试用代码实现啊
    takato
        18
    takato  
       354 天前
    @lower 布隆过滤器是概率情况,如果一定要确定值的时候不要使用。
    documentzhangx66
        19
    documentzhangx66  
       354 天前
    临时表 + 游标 轻松解决。

    给 A 、B 、C 、D 构建临时表:
    {
    ID int 主键,自增。
    ValueToSearch int ,用来存放 A 、B 、C 、D
    IsValueInDataTable bool ,该值是否存在于数据表中。
    }

    对其用游标遍历,然后每个值去数据表中查,查询的结果 Update 到临时表中的 IsValueInDataTable 字段。

    很多人应该是一行 SQL 写多了,要写多行 SQL 就不会了。
    c6h6benzene
        20
    c6h6benzene  
       354 天前 via iPhone
    @yuann72 not in 里面的是用逗号分割的项,一般多个值拼一下就好了。

    至于有没有这种函数,要看你用的数据库版本,oracle 和 sql server 都有 listagg/string_agg ,没有的话大概也可以拼起来之后再拆开。SQL SERVER 常见招式是 xml path 和 stuff ,然后 split_string
    c6h6benzene
        21
    c6h6benzene  
       354 天前 via iPhone
    或者 select except
    CHUB
        22
    CHUB  
       354 天前
    SELECT unnest(ARRAY['A', 'B', 'C', 'D']) EXCEPT SELECT column_name FROM table_name;

    或者更简单的:

    SELECT column_name FROM table_name GROUP BY column_name; 再用目测法看看缺了哪个
    jhdxr
        23
    jhdxr  
       354 天前
    pgsql 和 mssql 上面都有答案了,我来贴个 mysql 的: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

    ( mysql 8 之前或者 mariadb 用变量+字符串函数也行,就是写起来太太太太麻烦了。。。
    hero0earth
        24
    hero0earth  
       354 天前
    可以使用公共表达式( https://dev.mysql.com/doc/refman/8.0/en/with.html )构建临时表,再做外连接
    arvinsilm
        25
    arvinsilm  
       353 天前
    可以干,但属实没必要,数据库不是用来干这事的。不如写个脚本,用时跑一把完事。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4653 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 01:07 · PVG 09:07 · LAX 18:07 · JFK 21:07
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.