gpt4 book ai didi

SQL - 左连接后丢失列

转载 作者:行者123 更新时间:2023-12-02 03:01:25 28 4
gpt4 key购买 nike

这是我在 DB Fiddle 中的内容(使用 PostgreSQL v9.6):

示例表:

CREATE TABLE sample_table (
seller_id varchar(255),
week varchar(255),
week_end timestamp,
year integer,
product_id varchar(255),
num_sold integer,
dollars_sold integer
);

填充了虚假数据:

INSERT INTO sample_table (seller_id, week, week_end, year, product_id, num_sold, dollars_sold)
VALUES ('12345A', '01/01/2020 - 01/07/2020', '01/07/2020', 2020, '1A', 1, 5),
('12345A', '01/08/2020 - 01/14/2020', '01/14/2020', 2020, '1A', 2, 10),
('12345A', '01/15/2020 - 01/21/2020', '01/21/2020', 2020, '1A', 3, 15),
('12345B', '01/01/2020 - 01/07/2020', '01/07/2020', 2020, '1A', 2, 10),
('12345B', '01/08/2020 - 01/14/2020', '01/14/2020', 2020, '1A', 4, 20),
('12345B', '01/15/2020 - 01/21/2020', '01/21/2020', 2020, '1A', 6, 30),
('12345C', '01/01/2020 - 01/07/2020', '01/07/2020', 2020, '1A', 1, 5),
('12345C', '01/08/2020 - 01/14/2020', '01/14/2020', 2020, '1A', 2, 10),
('12345C', '01/15/2020 - 01/21/2020', '01/21/2020', 2020, '1A', 3, 15),
('12345D', '01/01/2020 - 01/07/2020', '01/07/2020', 2020, '1A', 5, 25),
('12345D', '01/08/2020 - 01/14/2020', '01/14/2020', 2020, '1A', 10, 50),
('12345D', '01/15/2020 - 01/21/2020', '01/21/2020', 2020, '1A', 15, 75),
('12345E', '01/01/2020 - 01/07/2020', '01/07/2020', 2020, '1A', 3, 15),
('12345E', '01/08/2020 - 01/14/2020', '01/14/2020', 2020, '1A', 6, 30),
('12345E', '01/15/2020 - 01/21/2020', '01/21/2020', 2020, '1A', 9, 45);

我的查询:

SELECT a.* FROM (SELECT     x.week_end, 
x.week,
x.year,
y.product_id,
z.seller_id
FROM (
SELECT DISTINCT week_end,
year,
week
FROM sample_table) x
CROSS JOIN
(
SELECT DISTINCT product_id
FROM sample_table) y
CROSS JOIN
(
SELECT DISTINCT seller_id
FROM sample_table) z) AS a
LEFT JOIN sample_table b
ON
a.seller_id = b.seller_id
AND
a.week_end = b.week_end
AND
a.product_id = b.product_id;

这是我期望发生的情况:查询从表中获取 week + week_end + year 的每个现有组合,并交叉连接它与每个现有的 product_id,然后将结果与每个现有的 seller_id 交叉连接。虽然我的示例表没有反射(reflect)这一点,但我正在使用的实际表缺少行,目标是通过创建已存在的行的每个组合来生成这些缺少的行。例如,如果卖家 12345A 缺少 01/01/2020 - 01/07/2020 某一产品这一周的数据,则在此操作之后将创建缺失的行。

关于这个问题:交叉连接后,我想将表左连接回其自身,以将 num_solddollars_sold 数据带回到以下行:已经存在(并且生成的任何缺失行都将显示空值)。

在我的左连接之后,num_solddollars_sold 列丢失了,我很困惑。我从另一个与此表非常相似的表中复制了查询,并且只是更改了一些列名称。我复制的查询完全按照说明工作,但是当我在这个新表上运行此修改后的查询时,这两列不会通过左连接完成。

最佳答案

您已创建a来获取周/产品/卖家的所有组合。只需选择a.*不会显示除周/产品/卖家之外的任何其他数据。您错过了使用别名 b 包含左连接中的这些字段(num_sold 和dollar_sold),因此基本上您需要使用 b 别名将它们添加到主选择中:

SELECT a.*, b.sum_sold, b.dollars_sold FROM 
(SELECT x.week_end,
x.week,
x.year,
y.product_id,
z.seller_id
FROM (
SELECT DISTINCT week_end,
year,
week
FROM sample_table) x
CROSS JOIN
(
SELECT DISTINCT product_id
FROM sample_table) y
CROSS JOIN
(
SELECT DISTINCT seller_id
FROM sample_table) z) AS a
LEFT JOIN sample_table b ON
a.seller_id = b.seller_id
AND
a.week_end = b.week_end
AND
a.product_id = b.product_id;

关于SQL - 左连接后丢失列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59885092/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com