sql – 将两个表合并为一个新表,以便忽略另一个表中的选择行

我有两个具有沟通列的表.我想将这两个表一路毗连到第三个表中,第三个表包括第一个表中的全部行,而第二个表中的全部行都具有沟通位置的第一个表中不存在的日期.

例：

买卖营业方法：

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

祈望的功效：

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

我怎么会这样呢？我试过这个例子：

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.

办理要领

CREATE TABLE AS 
SELECT date,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.
我引用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.

可能,假如方针表已存在：

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

我提议不要行使日期作为列名.它是每个SQL尺度中的reserved word以及PostgreSQL中的函数和数据范例名称.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!