代码之家 › 专栏 › 技术社区 › ptownbro

SQL在查询中为每条记录插入重复行

amazon-redshift postgresql sql

ptownbro · 技术社区 · 5 年前

我需要帮助在查询结果中添加空行,以便最终结果具有一致的重复值,以便在模板中使用。例如,我有一个按客户和季度返回销售额的查询。如果客户在某个季度没有销售,那么该季度自然不会显示。但是我需要它。

下面是我的意思的一个例子(编辑:第二个表格在原文中是错误的):

编辑: 我已经添加了示例CREATETABLE和insert到与下面的建议相关的脚本中,以用于测试。

create table #customers (id int, customer varchar(30));
create table #quarters (id int, quarter varchar(30));
create table #salesAggs (id int, customer varchar(30), quarter varchar(30), amount int);

insert into #customers (id, customer) values (1, 'John'), (2, 'Mary'), (3, 'Kathy');
insert into #quarters (id, quarter) values (1, 'Q1'), (2, 'Q2'), (3, 'Q3'), (4, 'Q4');
insert into #salesAggs (id, customer, quarter, amount)
values
    (1, 'John', 'Q1', 10),
    (2, 'John', 'Q3', 10),
    (3, 'Mary', 'Q2', 10),
    (4, 'Kathy', 'Q1', 10)
;

1 回复 | 直到 5 年前

pwilcox 5 年前

在将季度数据加入到您的销售数据之前,在季度数据和客户数据之间进行交叉联接:

select     c.id, c.customer, q.quarter, sa.amount
from       quarters q
cross join customers c
left join  salesAggs sa on q.quarter = sa.quarter and c.id = sa.id

select     c.id, c.customer, q.quarter, sa.amount
from       (select distinct quarter from salesAggs) q
cross join (select distinct id, customer from salesAggs) c
left join  salesAggs sa on q.quarter = sa.quarter and c.id = sa.id

如果在同一步骤中进行聚合:

select     c.id, c.customer, q.quarter, sum(s.amount) as amount
from       quarters q
cross join customers c
left join  sales s on q.quarter = s.quarter and c.id = s.id 
group by   c.id, c.customer, q.quarter

编辑:与OP稍后提供的样本数据匹配的示例

考虑到实际添加的表的结构,您需要更改要连接的列名:

select     c.id, c.customer, q.quarter, sa.amount
from       #quarters q
cross join #customers c
left join  #salesAggs sa on q.quarter = sa.quarter and c.customer = sa.customer

顺便说一下,您已经标记了Postgre,但似乎您正在使用Sql Server。

Gordon Linoff 5 年前

select c.id, c.customer, q.quarter, sa.amount
from (select distinct id, customer from salesAggs
     ) c cross join 
     (select 'Q1' as quarter union all
      select 'Q2' as quarter union all
      select 'Q3' as quarter union all
      select 'Q4' as quarter
     ) q left join 
     salesAggs sa
     on q.quarter = sa.quarter and c.id = sa.id;

select c.id, c.customer, q.quarter, sa.amount
from (select distinct id, customer from salesAggs
     ) c cross join 
     (values ('Q1'), ('Q2'), ('Q3'), ('Q4')
     ) q(quarter) left join 
     salesAggs sa
     on q.quarter = sa.quarter and c.id = sa.id;

Here 是db<&燃气轮机;不停摆弄 使用Postgres