MS SQL Server
  1. MS SQL Server
  3. Requête TSQL aide à returnner les lignes manquantes
Intereting Posts
SQL Server 2008 VarChar (Max) renvoie seulement 8000 caractères. Pourquoi? Est-il possible d'get des informations à partir d'une database différente avec une jointure interne ayant une database dans SQL et mySql? Classe de requête paramétrée appropriée pour le server asp.net – c # application – sql Sélectionnez top1 avec Linq Imprimer les valeurs du slider Est-ce que SQLalchemy – pymssql – pandas est toujours lent, ou est-ce que je me trompe? Requête récursive TSQL SQL UNION – résultats illogiques Deux jointures entre deux tables Sélection du haut, du regroupement et de la sum des autres avec la variable de table SQL Server xml copy les valeurs d'un nœud à l'autre Requête SQL Server conditionnelle Comment déboguer la fonction des procédures stockées SQL CLR sur SQL Server 2012 à partir de VS 2012 FreeTDS ne parvient pas à se connecter à SQL Server après des correctifs Windows récents et TLS1 étant désactivé replace getdate par le nom de la colonne

Requête TSQL aide à returnner les lignes manquantes

Besoin d'aide à la search pour le suivant J'ai un exemple de données, comme ci-dessous sur un de ma table. 

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

La requête doit afficher les lignes manquantes par rapport à la séquence temporelle, ainsi que les lignes de la table. Les lignes manquantes doivent être entrelacées entre les lignes de la table.

Vous pouvez le faire en deux étapes: utiliser cross join pour générer toutes les lignes, puis left join pour mettre les valeurs: 

 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;

La seule chose que je ne comprends pas, c'est la id . Comment déterminez-vous l' id pour une ligne non-assortie?

Hmmm, voici une façon d'get l' 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

Voici une autre méthode utilisant une table de pointage.

D'abord, générez tous les ShowTime possibles, qui devraient être 00:00 à 23:00 . Ensuite, obtenez le MIN(ShowTime) et MAX(Showtime) de chaque MovieId . Maintenant, faites un JOIN sur les deux résultats pour générer toutes les combinaisons possibles de MovieId et Showtime tels que le time est entre le MIN et MAX Showtime .

Pour get l' Id , utilisez 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

DÉMO EN LIGNE

Si vous avez SQL Server 2012 ou supérieur, vous pouvez utiliser un CTE récursif combiné avec la fonction de window LEAD comme dans l'exemple suivant: 

  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

Le principal avantage de cette méthode est qu'elle élimine le besoin de tables de search supplémentaires.