TSQL查询帮助返回丢失的行

需要以下查询帮助我有一个样本数据，如下表所示。

Create table #MovieShows(Id int, Movieid varchar(20), Showtime time)
insert into #MovieShows values (11,'m1','13:00')
insert into #MovieShows values (23,'m2','14:00')
insert into #MovieShows values (34,'m1','15:00')
insert into #MovieShows values (45,'m2','16:00')
insert into #MovieShows values (55,'m2','20:00')
insert into #MovieShows values (64,'m1','16:00')
insert into #MovieShows values (66,'m2','21:00')
insert into #MovieShows values (81,'m1','20:00')
go
select * from #MovieShows order by Movieid, id

     ==========================
     Need a query to show the missing rows along with table rows. 
     Desired output should be
     Id MovieID Showtime
     11 m1  13:00
     11 m1  14:00 --New row
     34 m1  15:00
     64 m1  16:00
     64 m1  17:00 --New row 
     64 m1  18:00 --New row 
     64 m1  19:00 --New row
     81 m1  20:00
     23 m2  14:00
     23 m2  15:00 --New row
     45 m2  16:00
     45 m2  17:00 --New row
     45 m2  18:00 --New row
     45 m2  19:00 --New row
     55 m2  20:00
     66 m2  21:00

查询需要显示与时间序列相关的缺失行以及表行。缺少的行需要在表行之间交错排列。

您可以分两步完成：使用cross join生成所有行，然后使用left join输入值：

select m.moveid, s.showtime
from (select distinct movieid from movieshows) m cross join
     (select distinct showtime from movieshows) t left join
     movieshows ms
     on ms.movieid = m.movieid and ms.showtime = t.showtime;

我唯一不明白的是id。如何确定不匹配行的id？

嗯，这里有一种获得id:的方法

select ms.id, m.moveid, s.showtime
from (select distinct movieid from movieshows) m cross join
     (select distinct showtime from movieshows) t outer apply
     (select top 1 ms.*
      from movieshows ms
      where ms.movieid = m.movieid and ms.showtime <= t.showtime
      order by ms.showtime desc
     ) ms

这里是另一个使用Tally表的方法。

首先，生成所有可能的ShowTimes，应该是00:00到23:00。然后得到每个CCD_ 11的CCD_ 9和CCD_。现在对两个结果执行JOIN以生成MovieIds和Showtimes的所有可能组合，使得时间在MIN和MAXShowtime之间。

要获得Id，请使用CROSS APPLY

WITH CteHr(hr) AS(
    SELECT
        CAST(DATEADD(HOUR, hr, 0) AS TIME)
    FROM (VALUES
        (0), (1), (2), (3), (4), (5), (6), (7),
        (8), (9), (10), (11), (12), (13), (14), 
        (15), (16), (17), (18), (19), (20), (21), (22), (23)
    ) AS t(hr)
),
CteMinMax(MovieId, minHr, maxHr) AS(
    SELECT
        MovieId, MIN(Showtime), MAX(Showtime)
    FROM #MovieShows
    GROUP BY MovieId
)
SELECT
    t.Id,
    mm.MovieId,
    Showtime = h.hr
FROM CteMinMax mm
CROSS JOIN CteHr h
CROSS APPLY(
    SELECT TOP 1 Id
    FROM #MovieShows
    WHERE
        Movieid = mm.MovieId
        AND Showtime <= h.hr
    ORDER BY Showtime DESC
) t
WHERE h.hr BETWEEN mm.minHr  AND mm.maxHr

在线演示

如果您有SQL Server 2012或更高版本，您可以使用递归CTE和LEAD窗口函数，如下例所示：

    DECLARE @MovieShows TABLE
    (
        Id INT, Movieid VARCHAR(20), Showtime TIME
        PRIMARY KEY (Movieid, Showtime)
    )
    INSERT INTO @MovieShows
    SELECT 11,'m1','13:00' UNION ALL
    SELECT 34,'m1','15:00' UNION ALL
    SELECT 64,'m1','16:00' UNION ALL
    SELECT 81,'m1','21:00' UNION ALL
    SELECT 23,'m2','14:00' UNION ALL
    SELECT 45,'m2','16:00' UNION ALL
    SELECT 55,'m2','20:00' UNION ALL
    SELECT 66,'m2','21:00'
    ;WITH CTE_Shows
    AS
    (
        SELECT   Id
                ,Movieid
                ,Showtime
                ,LEAD(Showtime, 1, NULL) OVER (PARTITION BY Movieid ORDER BY Showtime) AS NextShowTime
        FROM    @MovieShows MovesBase
        UNION   ALL     -- Fill in the gaps by performing a recursive union
        SELECT   Id
                ,Movieid
                ,DATEADD(HOUR, 1, Showtime) AS Showtime -- Add one hour to the current show time.
                ,Fill.NextShowTime
        FROM    CTE_Shows Fill
        WHERE   DATEADD(HOUR, 1, Fill.Showtime) < Fill.NextShowTime -- Only perform recursive union where the current show time + 1 hour is less than the next show time in the current dataset.
    ) 
    SELECT   Id
            ,Movieid
            ,Showtime
    FROM    CTE_Shows
    ORDER BY Movieid, Showtime

这种方法的主要优点是不需要额外的查找表。

解决此问题的可能方法包括

临时表格
自/交叉加入
修改Schema，使所有可能的显示时间存储在一个单独的表中，然后使用外部联接来填充结果。

相关内容

最新更新

热门标签：