oceanbase

1
drop table if exists t1,t2;
2
CREATE TABLE t1 (pk int primary key, a VARCHAR(400));
3
INSERT INTO t1  VALUES (1,'A'), (2,'a'), (3,'a '), (4,'a   '),
4
(5,'B'), (6,'b'), (7,'b '), (8,'b   ');
5
SELECT COUNT(DISTINCT a) FROM t1;
6
COUNT(DISTINCT a)
7
2
8
DROP TABLE t1;
9
CREATE TABLE t1 (pk int primary key, a int, b int, c int);
10
INSERT INTO t1 (pk, a, b, c) VALUES
11
(1,1,1,1), (2,1,1,2), (3,1,1,3),
12
(4,1,2,1), (5,1,2,2), (6,1,2,3),
13
(7,1,3,1), (8,1,3,2), (9,1,3,3),
14
(10,2,1,1), (11,2,1,2), (12,2,1,3),
15
(13,2,2,1), (14,2,2,2), (15,2,2,3),
16
(16,2,3,1), (17,2,3,2), (18,2,3,3),
17
(19,3,1,1), (20,3,1,2), (21,3,1,3),
18
(22,3,2,1), (23,3,2,2), (24,3,2,3),
19
(25,3,3,1), (26,3,3,2), (27,3,3,3);
20
SELECT b/c as v, a FROM t1 ORDER BY v, a;
21
v	a
22
0.3333	1
23
0.3333	2
24
0.3333	3
25
0.5000	1
26
0.5000	2
27
0.5000	3
28
0.6667	1
29
0.6667	2
30
0.6667	3
31
1.0000	1
32
1.0000	1
33
1.0000	1
34
1.0000	2
35
1.0000	2
36
1.0000	2
37
1.0000	3
38
1.0000	3
39
1.0000	3
40
1.5000	1
41
1.5000	2
42
1.5000	3
43
2.0000	1
44
2.0000	2
45
2.0000	3
46
3.0000	1
47
3.0000	2
48
3.0000	3
49
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v ORDER BY v;
50
v	SUM(a)
51
0.3333	6
52
0.5000	6
53
0.6667	6
54
1.0000	18
55
1.5000	6
56
2.0000	6
57
3.0000	6
58
SELECT SUM(a) as suma FROM t1 GROUP BY b/c ORDER BY suma;
59
suma
60
6
61
6
62
6
63
6
64
6
65
6
66
18
67
DROP TABLE t1;
68