sql - 如何使用单个语句而不是多个步骤来填充 SQL 表中的空白

Question

假设我有以下表格:

我想像这样填补 table 上的空白

现在我可以使用 CTE 和临时表构建组和最大/最小值然后插入它们的多个步骤来完成此操作。

但是我想知道是否有更优雅(!)的方式在单个语句中执行此操作，将source自身与months连接起来。 p>

与 tsql 相比，在 ansi sql 中执行此操作可加分，但两者都可以接受。

===

这里是一些创建测试的sql

drop table months;
create table months (year int, month int);

insert into months(year, month) values
(2000, 200007),   (2001, 200101),  (2002, 200201),
(2000, 200008),   (2001, 200102),  (2002, 200202),
(2000, 200009),   (2001, 200103),  (2002, 200203),
(2000, 200010),   (2001, 200104),  (2002, 200204),
(2000, 200011),   (2001, 200105),  (2002, 200205),
(2000, 200012),   (2001, 200106),  (2002, 200206),
                  (2001, 200107),  (2002, 200007),
                  (2001, 200108),  (2002, 200208),
                  (2001, 200109),  (2002, 200208),
                  (2001, 200110),
                  (2001, 200111),
                  (2001, 200112)

drop table source;
create table source (name varchar(10), month int, item int, val float);

insert into source(name, month, item, val) values
('bob', 200101, 1, 1.5),
('bob', 200102, 2, 1.5),
('bob', 200103, 2, 2),
('bob', 200108, 1, 0),
('bob', 200109, 10, 6.6),
('bob', 200110, 11, 2.2),
('bob', 200111, 9, 1),
('bob', 200207, 23, 0)

Answer 1

SQLFiddle example solution对于 SQL Server 2008

也许不是最优雅的，但这是一种实现方式

表格和数据

create table sources 
(
  source varchar(20),
  month int,
  item int,
  val decimal
);

insert into sources values
('bob', 200101, 1, 1.5),
('bob', 200102, 2, 1.5),
('bob', 200104, 2, 1.5);

注意 200103 不可用。我们将使用 SQL 来填充它。

代码

-- Recursive CTE to create all dates between 2001-01-01 and 2001-03-02
WITH Dates as
(
  select cast('2001-01-01' as date) as CalendarDate
  union all     
  select dateadd(day , 1, CalendarDate) AS CalendarDate
  from Dates
  where dateadd (day, 1, CalendarDate) < '2001-03-02'
),

-- CTE to get distinct yyyymm
FormattedDates as
(
  select 
    distinct cast(replace(left(CalendarDate,7), '-', '') as int) dt
  from Dates
)

-- compare list of yyyymm with sources and fill missing information
select 
  'bob', 
  FormattedDates.dt, 
  (select top 1 item from sources where month < formatteddates.dt order by month desc), 
  0
from FormattedDates
left join sources on FormattedDates.dt = sources.month
where source is null

-- add sources to the mix
union
select * from sources;

这是如何运作的

首先我们使用递归列出all dates between two dates有 CTE

WITH Dates as
(
  select cast('2001-01-01' as date) as CalendarDate
  union all     
  select dateadd(day , 1, CalendarDate) AS CalendarDate
  from Dates
  where dateadd (day, 1, CalendarDate) < '2001-03-02'
)
select * from Dates;

这是一个递归 CTE(公用表表达式)，它以日期开始并递归直到到达结束日期。它输出从 2001-01-01 到 2001-03-01 的日期。

我们使用 cast(replace(left(CalendarDate,7), '-', '') as int) 将日期粗略地转换为 yyyymm 格式。但这会创建多个 200101、200102 等。因此我们使用 distinct。

WITH Dates as
(
  SELECT cast('2001-01-01' as date) as CalendarDate

  UNION ALL

  SELECT dateadd(day , 1, CalendarDate) AS CalendarDate
  FROM Dates
  WHERE dateadd (day, 1, CalendarDate) < '2001-03-02'
)
select 
  distinct cast(replace(left(CalendarDate,7), '-', '') as int) dt
from Dates;

这将列出 200101、200102 和 200103。以下代码具有相同的效果:

-- Recursive CTE to create all dates between 2001-01-01 and 2001-03-02
WITH Dates as
(
  select cast('2001-01-01' as date) as CalendarDate
  union all     
  select dateadd(day , 1, CalendarDate) AS CalendarDate
  from Dates
  where dateadd (day, 1, CalendarDate) < '2001-03-02'
),

-- CTE to get distinct yyyymm
FormattedDates as
(
  select 
    distinct cast(replace(left(CalendarDate,7), '-', '') as int) dt
  from Dates
)

-- List out the distinct dates
select dt from FormattedDates;

我们将这些日期与源表中的日期连接起来，并仅选择源表中没有记录的那些记录。

-- Recursive CTE to create all dates between 2001-01-01 and 2001-03-02
WITH Dates as
(
  select cast('2001-01-01' as date) as CalendarDate
  union all     
  select dateadd(day , 1, CalendarDate) AS CalendarDate
  from Dates
  where dateadd (day, 1, CalendarDate) < '2001-03-02'
),

-- CTE to get distinct yyyymm
FormattedDates as
(
  select 
    distinct cast(replace(left(CalendarDate,7), '-', '') as int) dt
  from Dates
)

-- compare list of yyyymm with sources and fill missing information
select 
  'bob', 
  FormattedDates.dt, 
  (select top 1 item from sources where month < formatteddates.dt order by month desc), 
  0
from FormattedDates
left join sources on FormattedDates.dt = sources.month
where source is null;

这将列出 'bob', 200103, 2, 0。太好了，所以我们有丢失的信息。让我们将它与来自源的数据结合起来以获得完整的数据集。

现在需要添加的是 union all select * from sources; 以获得您想要的。