将两个表合并为一个新表，以便忽略从另一个表中选择的行 [英] Combine two tables into a new one so that select rows from the other one are ignored

问题描述

我有两个具有相同列的表.我想将这两个表连接到第三个表中，其中包含第一个表中的所有行和第二个表中具有相同位置的第一个表中不存在的日期的所有行.

I have two tables that have identical columns. I would like to join these two tables together into a third one that contains all the rows from the first one and from the second one all the rows that have a date that doesn't exist in the first table for the same location.

例子:

交易:

date    |location_code| product_code | quantity 
------------+------------------+--------------+----------
2013-01-20 | ABC         | 123          |  -20         
2013-01-23 | ABC         | 123          |  -13.158
2013-02-04 | BCD         | 234          |  -4.063

事务2:

date    |location_code| product_code | quantity 
------------+------------------+--------------+----------
 2013-01-20 | BDE         | 123          |  -30         
 2013-01-23 | DCF         | 123          |  -2
 2013-02-05 | UXJ         | 234          |  -6

想要的结果:

date    |location_code| product_code | quantity 
------------+------------------+--------------+----------
 2013-01-20 | ABC         | 123          |  -20         
 2013-01-23 | ABC         | 123          |  -13.158
 2013-01-23 | DCF         | 123          |  -2
 2013-02-04 | BCD         | 234          |  -4.063
 2013-02-05 | UXJ         | 234          |  -6

我该怎么做呢?例如，我试过这样:

How would I go about this? I tried for example this:

SELECT date, location_code, product_code, type, quantity, location_type, updated_at
      ,period_start_date, period_end_date
   INTO transactions_combined
   FROM ( SELECT * FROM transactions_kitchen k
          UNION ALL
          SELECT *
            FROM transactions_admin h
            WHERE h.date NOT IN (SELECT k.date FROM k)
        ) AS t;

但这并没有考虑到我想包含日期相同但位置不同的行.我正在使用 Postgresql 9.2.

but that doesn't take into account that I'd like to include the rows that have the same date, but different location. I have Postgresql 9.2 in use.

推荐答案

UNION 根本不符合您的描述.这个查询应该:

UNION simply doesn't do what you describe. This query should:

CREATE TABLE AS 
SELECT date, location_code, product_code, quantity
FROM   transactions_kitchen k

UNION  ALL
SELECT h.date, h.location_code, h.product_code, h.quantity
FROM   transactions_admin h
LEFT   JOIN transactions_kitchen k USING (location_code, date)
WHERE  k.location_code IS NULL;

LEFT JOIN/IS NULL 从第二个表中排除相同位置和日期的行.见:

LEFT JOIN / IS NULL to exclude rows from the second table for the same location and date. See:

• 选择不存在的行其他表

使用 CREATE TABLE AS 代替 SELECT INTO.手册:

CREATE TABLE AS 在功能上类似于 SELECT INTO.CREATE TABLE AS 是推荐的语法，因为这种形式的 SELECT INTO在 ECPG 或 PL/pgSQL 中不可用，因为它们解释INTO 子句不同.此外，CREATE TABLE AS 提供了一个SELECT INTO 提供的功能的超集.

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

或者，如果目标表已经存在:

Or, if the target table already exists:

INSERT INTO transactions_combined (<list names of target column here!>)
SELECT ...

旁白:我不会使用 date 作为列名.它是每个 SQL 标准中的 保留字 和一个函数Postgres 中的数据类型名称.

Aside: I would not use date as column name. It's a reserved word in every SQL standard and a function and data type name in Postgres.

这篇关于将两个表合并为一个新表，以便忽略从另一个表中选择的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！