Récupérer les valeurs manquant dans un auto-incrément

Version imprimable

28/11/2019, 14h35
thierryinfo2012

Récupérer les valeurs manquant dans un auto-incrément

bonjour

Supposons que j'ai une liste de valeurs, telle que 1, 2, 3, 4, 5 ..... et une table dans laquelle certaines de ces valeurs existent dans une colonne. Voici un exemple:

id name

1 Alice

3 Cindy

5 Elmore

6 Felix

Je souhaite créer une SELECT instruction qui inclura toutes les valeurs de ma liste ainsi que les informations des lignes qui correspondent à ces valeurs, c’est-à-dire que vous LEFT OUTER JOIN devez effectuer une opération entre ma liste et la table. Le résultat serait alors le suivant:

id name

1 Alice

2 (null)

3 Cindy

4 (null)

5 Elmore

Comment faire cela sans créer une table temporaire ni utiliser plusieurs UNION
opérateurs?

merci

id	name
1	Alice
3	Cindy
5	Elmore
6	Felix

id	name
1	Alice
2	(null)
3	Cindy
4	(null)
5	Elmore

Une solution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
select * from dbo.LaTable
 
id          Value
----------- ----------
1           val1
5           val5
6           val6
8           val8
 
(4 rows affected)
 
 
; WITH Bornes as (select min(id) as BorneInf ,max(id) as BorneSup from dbo.LaTable)
, CTE AS ( SELECT 0 AS Id UNION ALL SELECT Id + 1 FROM CTE WHERE Id < 32000 ) 
, CTE_Borne AS (select CTE.Id + BO.BorneInf as Id from CTE CROSS JOIN Bornes as BO where CTE.Id <= BO.BorneSup - BO.BorneInf )
SELECT CT.id , LT.Value
FROM CTE_Borne as CT 
LEFT OUTER JOIN dbo.LaTable as LT on LT.id = CT.Id
OPTION (MAXRECURSION 32000) ;
 
id          Value
----------- ----------
1           val1
2           NULL
3           NULL
4           NULL
5           val5
6           val6
7           NULL
8           val8
 
(8 rows affected)

une autre méthode

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
declare @incrément table 
(
id int ,
name varchar(50)
)
 
insert into @incrément values ('1' ,'Alice')
insert into @incrément values ('2' ,'Cindy')
insert into @incrément values ('5' ,'Elmore')
insert into @incrément values ('6' ,'Felix')
 
Select v.valueId, I.name 
 From (values (1), (2), (3), (4), (5)) v(valueId)
     left Join  @incrément I
        on I.id = v.valueId

Ou de manière récursive, avec votre table :

Code:

1
2
CREATE TABLE LA_TABLE (ID INT, NAME VARCHAR(32));
GO

et ses valeurs :

Code:

1
2
3
4
5
6
INSERT INTO LA_TABLE VALUES 
(1, 'Alice'),
(3, 'Cindy'),
(5, 'Elmore'),
(6, 'Felix');
GO

La requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH 
M AS (SELECT MAX(ID) AS MXD
	  FROM   LA_TABLE),
N AS (SELECT 1 AS ENTIER
      UNION ALL
	  SELECT ENTIER + 1
	  FROM   N
	  WHERE  ENTIER < (SELECT MXD FROM M))
SELECT *
FROM   LA_TABLE
UNION ALL
SELECT ENTIER, NULL
FROM   N
       LEFT OUTER JOIN LA_TABLE AS T
	      ON N.ENTIER = T.ID
WHERE  ID IS NULL
OPTION (MAXRECURSION 0);

Pour information, c'est un exercice que je donne ici :
https://sqlpro.developpez.com/Exerci...e=part-1#LII-I

A +