Discussion :

[gudul]

bonjour,

Je suis en SQLServer 2019.

J'aimerai avoir une requête qui me retourne un nombre de ligne suivant le contenu d'un champs.
Exemple pour une table "Table"

Colis	Nb etiq
1	3
2	5

Code :

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

1
2
select Colis, numEtiq from Table where colis = 1
Remplacer NumEtiq par ?

résultat

1	1/3
1	2/3
1	3/3

Merci pour les réponses

[tatayo]

Bonjour,
C'est faisable avec une requête récursive:

Code sql :

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
 
with datas(colis,nbetiq)
as
(
select 1,3
union
select 2,5
),
cte (colis,nbetiq)
as
(
select colis,nbetiq
from datas
union all
select cte.colis,cte.nbetiq - 1
from datas
inner join cte on cte.colis = datas.colis and cte.nbetiq > 0
)
select *
from cte
where nbetiq > 0
order by colis

J'imagine qu'il y a d'autres méthodes, mais c'est la première qui m'est venue à l'esprit.

Tatayo.

[gudul]

Merci Tatayo. Bonne idée de la requête récursive. Cela va répondre à mon besoin. Je vais en avoir une de même type. Je referais un ticket

Code adapté

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
with datas(colis,nbetiq)
as
(
select 1,3
union
select 2,5
),
 
cte (colis, numEtiq, nbetiq)
as
(
select colis, nbetiq, nbetiq
from datas
union all
select cte.colis, cte.numEtiq - 1, cte.nbetiq
from datas
    inner join cte on cte.colis = datas.colis and cte.numEtiq > 0
)
 
select colis, numEtiq, nbetiq, cast(numEtiq as varchar) + '/' + cast(nbetiq as varchar) as Etiquette
from cte
where numEtiq > 0
order by colis, numEtiq

[SQLpro]

Mieux vaut avoir une table de nombre (et une table calendrier ) dans une base pour faire ce genre de chose.... Cela s'appelle des tables de comptes.... et ça existe depuis for longtemps... c'est un certain François Barrême les a inventé au 17e siècle !
Cela évite les calculs et en particulier des requêtes récursives épouvantablement lentes (car inoptimisable...)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_NUM (N INT PRIMARY KEY);
GO
INSERT INTO T_NUM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
GO
INSERT INTO T_NUM 
SELECT N1.N + 10*N2.N + 100*N3.N + 1000* N4.N + 10000*N5.N + 100000*N6.N
FROM   T_NUM AS N1
       CROSS JOIN T_NUM AS N2
       CROSS JOIN T_NUM AS N3
       CROSS JOIN T_NUM AS N4
       CROSS JOIN T_NUM AS N5
       CROSS JOIN T_NUM AS N6
WHERE  N1.N + 10*N2.N + 100*N3.N + 1000* N4.N + 10000*N5.N + 100000*N6.N > 9;

Dès lors votre requête s'écrit avec une thêta jointure :

Code :

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

1
2
3
4
5
 
SELECT T.* 
FROM   maTable AS T 
       JOIN T_NUM 
          ON N < nb_etq;

Beaucoup plus simple et mille fois plus efficace !

A +

[JeitEmgie]

Code :

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

1
2
3
4
5
6
7
8
9
10
with data( colis, nbetiq) as (
	select 1, 3 union all
	select 2, 5
)
select colis, concat(colis, '/', k) AS label from data d
cross apply (
SELECT k from (
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS k FROM sys.all_objects) t WHERE k <= nbetiq  
) l
;

[SQLpro]

Code :

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

1
2
3
4
5
6
7
8
9
10
with data( colis, nbetiq) as (
	select 1, 3 union all
	select 2, 5
)
select colis, concat(colis, '/', k) AS label from data d
cross apply (
SELECT k from (
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS k FROM sys.all_objects) t WHERE k <= nbetiq  
) l
;

Pareil, pas d'optimisation possible car lecture séquentielle des lignes. => pas ensembliste donc pas d'utilisation d'index ni de parallélisme !

En sus la requête est dangereuse et susceptible de donner de fausses réponse. En effet, vous vous reposez sur une table système...
1) le nombre de ligne de cette table peut être inférieur au nombre d'étiquette demandée => fausse réponse
2) l'utilisation d'une vue système peut être interdite à un utilisateur aux privilèges restreint (en gros, seul les DBA peuvent exécuter cette requête !)

A +

[vanagreg]

Bonjour,
Comme d'habitude, plus simple à faire dans Oracle:

Code :

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

1
2
3
4
5
select colis, level ||'/' || numEtiq
from table1
connect by prior colis = colis
   and level <= numEtiq
   and prior sys_guid() is not null;

Un index pourrait être utilisé, tout dépend si on a besoin de lire toute la table ou pas. Ce qui va faire mal c'est le nombre d'étiquettes, plus il y en aura, plus il y aura de lignes à générer.

Jeu d'essai, 1 millions de lignes, 10 étiquettes max:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
set timing on
create table table1(Colis number, numEtiq number);
create table table2(colis number, Etiq varchar2(500));
 
insert into table1 select level, floor(dbms_random.value(1, 10)) from dual connect by level <= 1000000;
 
insert into table2
select colis, level ||'/' || numEtiq
from table1
connect by prior colis = colis
   and level <= numEtiq
   and prior sys_guid() is not null;

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
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
 
Table created.
Elapsed: 00:00:00.03
Table created.
Elapsed: 00:00:00.01
1000000 rows created.
Elapsed: 00:00:08.72
 
4999585 rows created.
 
Elapsed: 00:00:15.72
SQL>

15 secondes pour traiter insérer les 5 millions de lignes issues du split des étiquettes.

[SQLpro]

C'est du récursif aussi dans Oracle donc pas d'index sur la table construite de manière récursive.... En plus Oracle ne fait pas de parallélisme automatique il faut un module payant...

A +