Discussion :

[DJ2809]

Bonjour,
Je cherche à résoudre un problème de requête UPDATE pour numéroter des occurrences (id) du plus ancien au plus récent :
Je dispose d'une table A contenant ces colonnes : Id, date, numb_occ

id	Date	num_ occ
127	01/05/2014
321	24/02/2019
114	01/04/2021
127	01/02/2012
127	02/04/2013
144	01/04/2022
321	01/02/2020
444	01/04/2020
127	01/07/2022

Résultat:

id	Date	num_ occ
127	01/05/2014	3
321	24/02/2019	1
114	01/04/2021	1
127	01/02/2012	1
127	02/04/2013	2
144	01/04/2022	1
321	01/02/2020	2
444	01/04/2020	1
127	01/07/2022	4

En vous remerciant par avance
Bonne journée
DE

[Artemus24]

Salut DJ2809.

Code mysql :

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`    integer unsigned NOT NULL,
  `date`  date             NOT NULL,
  `incr`  integer unsigned     NULL default NULL,
  primary key (`id`,`date`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`date`) VALUES
  (127, '2014-05-01'),
  (321, '2019-02-24'),
  (114, '2021-04-01'),
  (127, '2012-02-01'),
  (127, '2013-04-02'),
  (144, '2022-04-01'),
  (321, '2020-02-01'),
  (444, '2020-04-01'),
  (127, '2022-07-01')
--------------
 
--------------
select * from `test` order by id, date
--------------
 
+-----+------------+------+
| id  | date       | incr |
+-----+------------+------+
| 114 | 2021-04-01 | NULL |
| 127 | 2012-02-01 | NULL |
| 127 | 2013-04-02 | NULL |
| 127 | 2014-05-01 | NULL |
| 127 | 2022-07-01 | NULL |
| 144 | 2022-04-01 | NULL |
| 321 | 2019-02-24 | NULL |
| 321 | 2020-02-01 | NULL |
| 444 | 2020-04-01 | NULL |
+-----+------------+------+
--------------
update     `test` as t1
inner join (    select  @i:=if (id=@p,@i+1,1) as incr,
                        @p:=id                as id,
                        date                  as date
                  from `test`
            cross join (  SELECT @i:=0,@p:=''  ) as y
              order by id, date asc
            ) as t2
        on t2.id = t1.id
       and t2.date = t1.date
       set t1.incr = t2.incr
--------------
 
--------------
select * from `test` order by id, date
--------------
 
+-----+------------+------+
| id  | date       | incr |
+-----+------------+------+
| 114 | 2021-04-01 |    1 |
| 127 | 2012-02-01 |    1 |
| 127 | 2013-04-02 |    2 |
| 127 | 2014-05-01 |    3 |
| 127 | 2022-07-01 |    4 |
| 144 | 2022-04-01 |    1 |
| 321 | 2019-02-24 |    1 |
| 321 | 2020-02-01 |    2 |
| 444 | 2020-04-01 |    1 |
+-----+------------+------+
--------------
update `test` set incr=NULL
--------------
 
--------------
update          `test` as t1
left outer join (         select t3.id,
                                 t3.date,
                                 count(*) as incr
                            from `test` as t3
                 left outer join `test` as t4
                              on t4.id   = t3.id
                             and t4.date <= t3.date
                   group by t3.id, t3.date
                ) as t2
             on t2.id   = t1.id
            and t2.date = t1.date
            set t1.incr = t2.incr
--------------
 
--------------
select * from `test` order by id, date
--------------
 
+-----+------------+------+
| id  | date       | incr |
+-----+------------+------+
| 114 | 2021-04-01 |    1 |
| 127 | 2012-02-01 |    1 |
| 127 | 2013-04-02 |    2 |
| 127 | 2014-05-01 |    3 |
| 127 | 2022-07-01 |    4 |
| 144 | 2022-04-01 |    1 |
| 321 | 2019-02-24 |    1 |
| 321 | 2020-02-01 |    2 |
| 444 | 2020-04-01 |    1 |
+-----+------------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

[DJ2809]

Bonjour Artemus24
Merci beaucoup .. c’est parfait!
Bonne soirée
DE