oceanbase
47 строк · 1.6 Кб
1--disable_query_log
2set @@session.explicit_defaults_for_timestamp=off;
3--enable_query_log
4# owner: link.zt
5# owner group: sql4
6# description: from子查询测试集
7--disable_warnings
8DROP TABLE IF EXISTS t1;
9--enable_warnings
10--disable_warnings
11DROP TABLE IF EXISTS t2;
12--enable_warnings
13#--real_sleep 1
14CREATE TABLE t1 (pk int primary key,c1 INT, c2 CHAR(100),c3 FLOAT);
15#--real_sleep 1
16SELECT * FROM t1 where pk = 1;
17INSERT INTO t1 VALUES (1, null,null,0.0);
18INSERT INTO t1 VALUES (2, 1,'',1.0);
19INSERT INTO t1 VALUES (3, 2,'abcde',2.0);
20INSERT INTO t1 VALUES (4, 100,'abcdefghij',3.0);
21CREATE TABLE t2 (pk int primary key,c1 INT, c2 VARCHAR(100));
22#--real_sleep 1
23INSERT INTO t2 VALUES (1, 1,'abcde');
24INSERT INTO t2 VALUES (2, 2,'abcde');
25SELECT sb1,sb2,sb3 FROM (SELECT c1 AS sb1, c2 AS sb2, c3*2 AS sb3
26FROM t1) AS sb WHERE sb1 > 1;
27SELECT AVG(sum_column1) FROM (SELECT SUM(c1) AS sum_column1 FROM t1
28GROUP BY c1) AS t1;
29DROP TABLE t1;
30DROP TABLE t2;
31#--real_sleep 1
32CREATE TABLE t1 (pk int primary key, c1 INT, c2 VARCHAR(100),c3 FLOAT);
33#--real_sleep 1
34INSERT INTO t1 VALUES (1, null,null,0.0);
35INSERT INTO t1 VALUES (2, 1,'',1.0);
36INSERT INTO t1 VALUES (3, 2,'abcde',2.0);
37INSERT INTO t1 VALUES (4, 100,'abcdefghij',3.0);
38CREATE TABLE t2 (pk int primary key, c1 INT, c2 VARCHAR(100));
39#--real_sleep 1
40INSERT INTO t2 VALUES (1, 1,'abcde');
41INSERT INTO t2 VALUES (2, 2,'abcde');
42SELECT sb1,sb2,sb3 FROM (SELECT c1 AS sb1, c2 AS sb2, c3*2 AS sb3
43FROM t1) AS sb WHERE sb1 > 1;
44SELECT AVG(sum_column1) FROM (SELECT SUM(c1) AS sum_column1 FROM t1
45GROUP BY c1) AS t1;
46DROP TABLE t1;
47DROP TABLE t2;
48