oceanbase
44 строки · 1.4 Кб
1--disable_query_log
2set @@session.explicit_defaults_for_timestamp=off;
3--enable_query_log
4# owner: bin.lb
5#owner group:sql2
6# tags: dml
7#description:this case is use for test online use of ob1.0
8
9
10--disable_warnings
11drop table if exists t1;
12--enable_warnings
13
14create table t1(c1 int primary key, c2 int);
15
16insert into t1 values(7,3),(8,3),(1,1),(2,0),(3,1),(4,0),(5,2),(6,2);
17
18# group by test
19select c1 as a1,c2 as a2 from t1 group by a2 order by a1;
20select c1 as a1,c2 as a2 from t1 group by a1 order by a2;
21select c1 as a1,c2 as a2 from t1 group by a2 order by a2;
22select c1 as a1,c2 as a2 from t1 group by a1 order by a1;
23
24# order by test
25select c1 as a1,c2 as a2 from t1 order by a2;
26select c1 as a1,c2 as a2 from t1 order by a1;
27
28# having test
29select c1 as a1,c2 as a2 from t1 group by a2 having a2>0 order by a1;
30select c1 as a1,c2 as a2 from t1 group by a2 having a2>0 order by a2;
31select c1 as a1,c2 as a2 from t1 group by a2 having a1>0 order by a1;
32select c1 as a1,c2 as a2 from t1 group by a2 having a1>0 order by a2;
33
34
35# select expr
36select c1+c2 as total from t1 order by total;
37select c1, c1+c2 as total from t1 group by total order by c1;
38select c1, c1+c2 as total from t1 group by total having c1>2 order by c1 desc;
39
40
41# refer to column alias in where
42select c1 as a1, c2 as a2 from t1 where c1>1;
43select c1 as a1, c2 as a2 from t1 where c2>1;
44select c1,c1+c2 as a1 from t1 where c1+c2>2;
45
46
47
48