|
11.08.2017, 11:44
#39503932
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Зеленоград, Москва, Россия
Сообщения: 20 644
Рейтинг:
0
/ 0
|
|
|
|
Эххх...
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.
mysql> CREATE TABLE test (
-> Id INT,
-> ElapsedTime INT,
-> Value1 INT,
-> Value2 CHAR(1),
-> Value3 INT
-> );
Query OK, 0 rows affected (0.31 sec)
mysql>
mysql> INSERT INTO test (Id,ElapsedTime,Value1,Value2,Value3)
-> SELECT 1,42,0,'a',0 UNION ALL
-> SELECT 2,43,0,'a',0 UNION ALL
-> SELECT 3,44,0,'a',1 UNION ALL
-> SELECT 4,45,1,'b',1 UNION ALL
-> SELECT 5,46,1,'b',2 UNION ALL
-> SELECT 6,47,1,'b',2 UNION ALL
-> SELECT 7,48,0,'c',0 UNION ALL
-> SELECT 8,49,0,'c',0 UNION ALL
-> SELECT 9,50,0,'c',1 UNION ALL
-> SELECT 10,51,0,'d',1 ;
Query OK, 10 rows affected (0.20 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM test;
+------+-------------+--------+--------+--------+
| Id | ElapsedTime | Value1 | Value2 | Value3 |
+------+-------------+--------+--------+--------+
| 1 | 42 | 0 | a | 0 |
| 2 | 43 | 0 | a | 0 |
| 3 | 44 | 0 | a | 1 |
| 4 | 45 | 1 | b | 1 |
| 5 | 46 | 1 | b | 2 |
| 6 | 47 | 1 | b | 2 |
| 7 | 48 | 0 | c | 0 |
| 8 | 49 | 0 | c | 0 |
| 9 | 50 | 0 | c | 1 |
| 10 | 51 | 0 | d | 1 |
+------+-------------+--------+--------+--------+
10 rows in set (0.00 sec)
mysql>
mysql> SET @tmp1 := NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @tmp2 := NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @tmp3 := NULL;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> UPDATE test
-> SET
-> Value1 = CASE WHEN @tmp1 = Value1 THEN NULL ELSE (@tmp1:=Value1) END,
-> Value2 = CASE WHEN @tmp2 = Value2 THEN NULL ELSE (@tmp2:=Value2) END,
-> Value3 = CASE WHEN @tmp3 = Value3 THEN NULL ELSE (@tmp3:=Value3) END
-> ORDER BY Id ASC;
Query OK, 8 rows affected (0.04 sec)
Rows matched: 10 Changed: 8 Warnings: 0
mysql>
mysql> SELECT * FROM test;
+------+-------------+--------+--------+--------+
| Id | ElapsedTime | Value1 | Value2 | Value3 |
+------+-------------+--------+--------+--------+
| 1 | 42 | 0 | a | 0 |
| 2 | 43 | NULL | NULL | NULL |
| 3 | 44 | NULL | NULL | 1 |
| 4 | 45 | 1 | b | NULL |
| 5 | 46 | NULL | NULL | 2 |
| 6 | 47 | NULL | NULL | NULL |
| 7 | 48 | 0 | c | 0 |
| 8 | 49 | NULL | NULL | NULL |
| 9 | 50 | NULL | NULL | 1 |
| 10 | 51 | NULL | d | NULL |
+------+-------------+--------+--------+--------+
10 rows in set (0.00 sec)
mysql>
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.16 sec)
|
|
|
