代码之家 › 专栏 › 技术社区 › David542

除了显示其他字段

google-bigquery postgresql sql

-1

David542 · 技术社区 · 11 月前

假设我有两个表(来自上传的csv文件),我想根据新文件中不在旧文件中的id+区域进行差异分析。最简单的方法是:

SELECT id, territory FROM this_week EXCEPT SELECT id, territory FROM last_week

然而,我想做的是全部由该差异生成的字段(在两个表中——每个键一行)。这怎么可能做到呢?

postgres或bigquery都可以。两者都有 EXCEPT 设置op。

Erwin回答中的数据示例:

WITH this_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(22,'us','spider','hd'),(3,'fr','new','hd')),
     last_week (id,territory,name,other) AS (VALUES(1,'us','titanic','uhd'),(2,'us','spider','hd'))
SELECT *  -- all columns of "this_week"
FROM   this_week t
WHERE  NOT EXISTS (
   SELECT * FROM last_week l
   WHERE  t.id = l.id
   AND    t.territory = l.territory
   );

2 回复 | 直到 11 月前

Erwin Brandstetter 11 月前

使用 NOT EXISTS :

SELECT *  -- all columns of "this_week"
FROM   this_week t
WHERE  NOT EXISTS (
   SELECT FROM last_week l
   WHERE  t.id = l.id
   AND    t.territory = l.territory
   );

并索引 last_week (id, territory) 通常会对性能有很大帮助。

这显示 全部的 列 this_week .
我看不出添加以下列有什么意义 last_week ,这将是空的( null )根据查询的定义,如果你离开了join。

基础知识:

Select rows which are not present in other table

注意一个细微的区别:

EXCEPT (当不使用时 ALL )折叠重复。此查询没有。你可能想要一个或另一个。通常,你想要这个。

DanielOnMSE 11 月前

比较两个结构相同的表(相同序号位置的相同列名),主键(唯一且不可为空)pk:

SELECT t1.*
    , t2.*
FROM Table1 t1
FULL JOIN Table2 t2
    ON t1.pk = t2.pk
WHERE NOT EXISTS(
    SELECT t1.*
    INTERSECT
    SELECT t2.*
    )

这将给你两张表之间的所有差异。如果两个表中都有匹配的pk,您将看到其余列在某处不同的记录。如果一行在t1而不是t2(按pk),它将出现,如果一行位于t2而不是t1(按pk计算),它也将出现。

如果表的结构不同,则需要展开子查询中的*,并明确列出要比较的列。

举个例子

--Create two identically strctured tables
CREATE TABLE Table1(PK BIGINT NOT NULL
                    , Col1 BIGINT
                    , Col2 BIGINT
                    )
;

CREATE TABLE Table2(PK BIGINT NOT NULL
                    , Col1 BIGINT
                    , Col2 BIGINT
                    )
;

--insert some data into them
INSERT INTO Table1(PK
                , Col1
                , Col2 
                )
VALUES
    (1, 100, 1000) --Pk In both Table1 and Table2, and contents match
    ,(2, 101, 1001) --Pk In both Table1 and Table2, and contents do not match
    ,(3, 102, 1002) --Pk only in Table1
;

INSERT INTO Table2(PK
                , Col1
                , Col2 
                )
VALUES
    (1, 100, 1000) --Pk In both Table1 and Table2, and contents match
    ,(2, 0, 0) --Pk In both Table1 and Table2, and contents do not match
    ,(4, 103, 1003) --Pk only in Table2
;

--Compare them
SELECT CASE 
        WHEN t1.PK IS NULL --This assumes that PK being NULL is due to the JOIN only!
            THEN 'In Table2, and not in Table1'
        WHEN t2.pk IS NULL --This assumes that PK being NULL is due to the JOIN only!
            THEN 'In Table1, and not in Table2'
        ELSE 'In both tables, but contents differ'
    END AS ComparisonDescription
    , t1.*
    , t2.*
FROM Table1 t1
FULL JOIN Table2 t2
    ON t1.pk = t2.pk
WHERE NOT EXISTS(
    SELECT t1.* --IF not identically structured, select the common columns here
    INTERSECT
    SELECT t2.* --IF not identically structured, select the common columns here
    )
;

Db<>Fiddle 举个例子