Discussion :

[zoocoral]

Bonjour,

soit 2 tables A et B dans une BDD mysql, ces 2 tables ont les mêmes champs et les mêmes données à un instant T.

On modifie dans la table A les valeurs de certains champs sur certaines lignes.

Comment sortir une liste des lignes modifiées en se servant de la table B pour comparer ?

Merci de votre aide

[Artemus24]

Salut zoocoral.

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
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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`    integer unsigned AUTO_INCREMENT NOT NULL Primary Key,
  `col1`  varchar(255)                    NOT NULL,
  `col2`  integer                         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`id`,`col1`,`col2`) values
  ( 1, 'bla bla', 25),
  ( 3, 'ble ble', 12),
  (10, 'bli bli', 11),
  (25, 'blo blo',  7),
  (47, 'blu blu', 75)
--------------
 
--------------
select * from tabone
--------------
 
+----+---------+------+
| id | col1    | col2 |
+----+---------+------+
|  1 | bla bla |   25 |
|  3 | ble ble |   12 |
| 10 | bli bli |   11 |
| 25 | blo blo |    7 |
| 47 | blu blu |   75 |
+----+---------+------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`    integer unsigned AUTO_INCREMENT NOT NULL Primary Key,
  `col1`  varchar(255)                    NOT NULL,
  `col2`  integer                         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`id`,`col1`,`col2`) values
  ( 1, 'bla bla', 25),
  ( 4, 'ble ble', 12),
  (10, 'blw blw', 11),
  (25, 'blo blo', 14),
  (47, 'blu blu', 75)
--------------
 
--------------
select * from tabtwo
--------------
 
+----+---------+------+
| id | col1    | col2 |
+----+---------+------+
|  1 | bla bla |   25 |
|  4 | ble ble |   12 |
| 10 | blw blw |   11 |
| 25 | blo blo |   14 |
| 47 | blu blu |   75 |
+----+---------+------+
--------------
select id, col1, col2, any_value(tab) as 'table'
from (
      select id, 'tabone' as tab, col1, col2 from tabone
union select id, 'tabtwo' as tab, col1, col2 from tabtwo
) as x
group by id, col1, col2
having count(*) <> 2
--------------
 
+----+---------+------+--------+
| id | col1    | col2 | table  |
+----+---------+------+--------+
|  3 | ble ble |   12 | tabone |
|  4 | ble ble |   12 | tabtwo |
| 10 | bli bli |   11 | tabone |
| 10 | blw blw |   11 | tabtwo |
| 25 | blo blo |    7 | tabone |
| 25 | blo blo |   14 | tabtwo |
+----+---------+------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Si les deux lignes sont identiques, par le regroupement, tu obtiens un "count(*) = 2". Dans le cas contraire, tu obtiens "count(*) = 1".
On affiche que les lignes ayant "count(*) = 1", comme dans l'exemple que j'ai donné.

@+