我有两个具有相同列的表.我想将这两个表一起连接到第三个表中,第三个表包含第一个表中的所有行,而第二个表中的所有行都具有相同位置的第一个表中不存在的日期.
例:
交易方式:
- 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.
解决方法
根据您的描述,查询可能如下所示:
我使用LEFT JOIN / IS NULL来排除第二个表中相同位置和日期的行. @L_301_0@将是另一个不错的选择.
UNION根本不做你所描述的.
我使用LEFT JOIN / IS NULL来排除第二个表中相同位置和日期的行. @L_301_0@将是另一个不错的选择.
UNION根本不做你所描述的.
- 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 toSELECT INTO
.CREATE TABLE AS
is the recommended Syntax,since this form ofSELECT INTO
is not
available in ECPG or PL/pgsql,because they interpret theINTO
clause
differently. Furthermore,CREATE TABLE AS
offers a superset of the
functionality provided bySELECT INTO
.
或者,如果目标表已存在:
- INSERT INTO transactions_combined (<list names of target column here!>)
- SELECT ...
我建议不要使用日期作为列名.它是每个sql标准中的reserved word以及Postgresql中的函数和数据类型名称.