将两个表组合成一个新表，以便忽略另一个表 [英] Combine two tables into a new one so that select rows from the other one are ignored

问题描述

我有两个表具有相同的列。我想将这两个表一起加入到第三个表中，第三个表包含第一个表的第一行中的所有行，而第二行中的所有行都具有相同位置的第一个表中不存在的日期。 p>

示例：

交易：

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

transactions2：

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

所需结果：

  date | location_code | product_code |数量
 ------------ + ------------------ + ------------- -  + ---------- 
 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 
  

我该怎么做？我试过这个例子：

  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。

解决方案

根据您的描述，查询可能如下所示：

我使用 LEFT JOIN / IS NULL 以排除第二个表中相同位置和日期的行。 NOT EXISTS 将是另一个好的选择。

UNION 根本不会做你所描述的。

  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; 
  

使用 CREATE TABLE AS 而不是 SELECT INTO 。

我引用手册在 SELECT INTO ：

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

或者，如果目标表已存在：

  INSERT INTO transactions_combined（<目标列的列表在这里！>）
 SELECT ... 
  

我建议不要使用 date 作为列名。这是每个SQL标准中的一个保留字和PostgreSQL中的数据类型名称。

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.

Example:

transactions:

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

transactions2:

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

Desired result:

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;

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.

解决方案

According to your description, the query could look like this:
I use LEFT JOIN / IS NULL to exclude rows from the second table for the same location and date. NOT EXISTS would be the other good option.
UNION simply doesn't do what you describe.

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;

Use CREATE TABLE AS instead of SELECT INTO.
I quote the manual on 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 ...

I would advise not to use date as column name. It's a reserved word in every SQL standard and a function and data type name in PostgreSQL.

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