Discussion :

[lucienkany]

Bonjour,
Voici un exercice dont je n'ai pas trouvé solution (je n'ai peut-être pas bien formulé mes recherches).

Nous avons une table contenant plusieurs listes de chiffres (séquences) consécutifs.
exemple.
liste 1 : (NUMDEBUT) 150001 à (NUMFIN)150500
liste 2 : (NUMDEBUT) 230001 à (NUMFIN) 230500
liste 3 : (NUMDEBUT) 110001 à (NUMFIN) 110700

Comment faire une liste de nombres commençant par le plus petit (110001) et se terminant par le plus grand (230500)
La liste des chiffres doit être triée du plus petit au plus grand.
Donc une première séquence de 110001 à 1107000 suivie de 150001 à 150500 puis de 230001 à 230500

j'ai utilsé le trigger ci-dessous pour insérer une liste (UNE SEQUENCE) dans la table DETDISTRIBUTION, maintenant il me faut inserer non pas une liste mais trois triées par ordre de grandeur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
USE [AFC00]
GO
/****** Object:  StoredProcedure [dbo].[sequenceur]    Script Date: 14/12/2017 22:45:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		LUCIEN KANYEMBO
-- Create date: 13/10/2013
-- Description:	insere les num des tickets 
-- =============================================
ALTER PROCEDURE [dbo].[sequenceur]
	-- Add the parameters for the stored procedure here
	@NUMDEBUT BIGINT , 
	@NUMFIN BIGINT, 
	@IDLIEU INT,
	@IDAGENT INT
 
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
 
	DECLARE  @DATA int
	SET @DATA = (SELECT count(*) FROM DETDISTRIBUTION D  WHERE D.NUMTICKET = @NUMDEBUT )
 
	IF @DATA = 0 -- @data=0 si il n’ya pas ce numéro de ticket dans la table
 
 
    -- Insert statements for procedure here
	WITH cteSequence ( numero_sequence) as
(
      SELECT @NUMDEBUT
      UNION ALL
      SELECT numero_sequence + 1
      FROM cteSequence
      WHERE numero_sequence < @NUMFIN
)
 
 
 
INSERT INTO DETDISTRIBUTION       ([NUMTICKET]  ,[IDAGENT], [IDLIEU]  )
 
SELECT  numero_sequence,@IDAGENT, @IDLIEU FROM cteSequence
OPTION ( MAXRECURSION 0);
END

D'avance merci.

Lucien K

[SQLpro]

Une seule requête suffit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE LISTE (LST_ID INT, LST_DEBUT INT, LST_FIN INT)
 
INSERT INTO LISTE VALUES 
(1, 150001, 150500),
(2, 230001, 230500),
(3, 110001, 110700);

La voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
WITH T AS
(SELECT *, LST_DEBUT AS NUM
 FROM  LISTE
 UNION ALL
 SELECT LST_ID, LST_DEBUT, LST_FIN, NUM + 1
 FROM   T
 WHERE  NUM < LST_FIN)
SELECT LST_ID, NUM
FROM   T
ORDER BY 1 , 2
OPTION (maxrecursion 32760)

A +

[lucienkany]

Merci SqlPro,
J'ai juste modifier un petit bout.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY NUM ASC

pour avoir le résultat souhaité.

Avec tous mes compliments.

[SQLpro]

Le plus performant serait d'avoir une table des nombres, codée en dur dans votre base.

Dans toutes les bases que je modélise il y a toujours une table des nombres de 1 à 1 millions (voir plus) et une table de calendrier contenant une vingtaine de colonnes.

A +

[lucienkany]

SqlPro,

Là tu me laisses sur ma faim!!!

Comment sont structurées ces tables et comment m'en servir?

ça doit être très intéressant.

Si tu peux m'en dire plus,

D'avance merci et meilleures salutations.

Lucien K.

[SQLpro]

Comment sont structurées ces tables et comment m'en servir?

ça doit être très intéressant.

Comme je suis en train de conner un cours à Orsys sur le sujet et que j'ai repris ta demande pour exercice, voici la solution et la comparaison :

Table de compte (voir https://media.cdnws.com/_i/30585/m84...7/28079-2.jpeg)

la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE  TABLE T_NUM (I INT NOT NULL);
 
INSERT INTO T_NUM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ;
 
INSERT INTO T_NUM
SELECT            T6.I + 
             10 * T1.I + 
			100 * T2.I + 
		   1000 * T3.I + 
		  10000 * T4.I + 
		 100000 * T5.I
FROM   T_NUM AS T1
       CROSS JOIN T_NUM AS T2
	   CROSS JOIN T_NUM AS T3
	   CROSS JOIN T_NUM AS T4
	   CROSS JOIN T_NUM AS T5
	   CROSS JOIN T_NUM AS T6
WHERE  T6.I + 10 * T1.I + 100 * T2.I + 1000 * T3.I 
      + 10000 *  T4.I + 100000 * T5.I > 9;
 
ALTER TABLE T_NUM 
   ADD CONSTRAINT U PRIMARY KEY (I);

la solution avec la table de compte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT LST_ID, I
FROM   LISTE
       JOIN T_NUM 
	      ON I BETWEEN LST_DEBUT AND LST_FIN

Les performances :
Table 'T_NUM'. Nombre d'analyses 3, lectures logiques 16
Table 'LISTE'. Nombre d'analyses 1, lectures logiques 1
SQL ServerTemps d'exécution*: Temps UC = 0*ms, temps écoulé = 27*ms.

Avec la requête précédente :

Table 'Worktable'. Nombre d'analyses 2, lectures logiques 10197
Table 'LISTE'. Nombre d'analyses 1, lectures logiques 1
SQL Server Temps d'exécution*: Temps UC = 31*ms, temps écoulé = 221*ms.

Y'a pas photo !!!!

A +

[lucienkany]

SqlPro,

Merci je suis flatté pour l'exercice.

Pour moi ce n'est pas juste pour m'exercer, je dois le faire fonctionner dans un logiciel.

Voici le topo.

A la fin de chaque année, les sociétés doivent déclarer le revenu de CHAQUE agent sur une fiche individuelle qui portent déjà des numéros. Chaque rame contient 500 fiches et il faut le remplir à la main.

Depuis quelques années, j'ai utilisé une petite base de données access pour "caler" les données sur un état et imprimer sur les fiches.

Plus récemment, mes clients ont reçu la permission de reproduire les fiches mais à condition de payer les numéros et il arrive qu'ils reçoivent des "restes" de plusieurs rames entamées.

D'où "l'exercice" pour les utilisateurs...

Maintenant que j'ai la liste des numéros bien triées dans une table, il me faut relier chaque numéros à un seul enregistrement de la table principale.

Je crois que je reviendrait vers vous bientôt...

Meilleures salutations.

Lucien K

[SQLpro]

Comme je suis en train de donner un cours à Orsys sur le sujet et que j'ai repris ta demande pour exercice, voici la solution et la comparaison :

Table de compte (voir https://media.cdnws.com/_i/30585/m84...7/28079-2.jpeg)

la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE  TABLE T_NUM (I INT NOT NULL);
 
INSERT INTO T_NUM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ;
 
INSERT INTO T_NUM
SELECT          T6.I + 
           10 * T1.I + 
          100 * T2.I + 
         1000 * T3.I + 
        10000 * T4.I + 
       100000 * T5.I
FROM   T_NUM AS T1
       CROSS JOIN T_NUM AS T2
	   CROSS JOIN T_NUM AS T3
	   CROSS JOIN T_NUM AS T4
	   CROSS JOIN T_NUM AS T5
	   CROSS JOIN T_NUM AS T6
WHERE           T6.I + 
           10 * T1.I + 
          100 * T2.I + 
         1000 * T3.I + 
        10000 * T4.I + 
       100000 * T5.I > 9;
 
ALTER TABLE T_NUM 
   ADD CONSTRAINT U PRIMARY KEY (I);

la solution avec la table de compte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT LST_ID, I
FROM   LISTE
       JOIN T_NUM 
	      ON I BETWEEN LST_DEBUT AND LST_FIN

Les performances :
Table 'T_NUM'. Nombre d'analyses 3, lectures logiques 16
Table 'LISTE'. Nombre d'analyses 1, lectures logiques 1
SQL ServerTemps d'exécution*: Temps UC = 0*ms, temps écoulé = 27*ms.

Avec la requête précédente :

Table 'Worktable'. Nombre d'analyses 2, lectures logiques 10197
Table 'LISTE'. Nombre d'analyses 1, lectures logiques 1
SQL Server Temps d'exécution*: Temps UC = 31*ms, temps écoulé = 221*ms.

Y'a pas photo !!!!

A +