github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/subquery.sql (about) 1 2 -- @suite 3 -- @setup 4 drop table if exists t1; 5 create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime); 6 insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 7 insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11'); 8 insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 9 insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11'); 10 insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11'); 11 insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11'); 12 insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 13 insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11'); 14 insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11'); 15 16 drop table if exists t2; 17 create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime); 18 insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11'); 19 insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11'); 20 insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11'); 21 insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11'); 22 insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11'); 23 insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11'); 24 insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11'); 25 insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11'); 26 insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11'); 27 28 -- @case 29 -- @desc:test for from subquery 30 -- @label:bvt 31 select * from (select * from t1) sub where id > 4; 32 select ti as t,fl as f from (select * from t1) sub where dl <> 4; 33 select * from (select ti as t,fl as f from t1 where dl <> 4) sub; 34 35 select id,min(ti) from (select * from t1) sub group by id; 36 select * from (select id,min(ti) from (select * from t1) t1 group by id) sub; 37 38 --待确认 39 --select id,min(ti) from (select * from t1) sub order by id desc; 40 --select * from (select id,min(ti) from t1 sub order by id desc) sub; 41 42 select id,min(ti) from (select * from t1) sub group by id order by id desc; 43 select id,sum(ti) from (select * from t1) sub group by id; 44 45 select distinct(ti) from (select * from t1) sub; 46 select distinct(ti) from (select * from t1) sub where id <6; 47 48 -- mysql 不同，待确认 49 -- select distinct(ti),de from (select * from t1) sub where id < 6 order by ti asc; 50 51 select count(*) from (select * from t1) sub where id > 4 ; 52 select * from (select * from t1) sub where id > 1 limit 3; 53 select max(ti),min(si),avg(fl) from (select * from t1) sub where id < 4 || id > 5; 54 select max(ti)+10,min(si)-1,avg(fl) from (select * from t1) sub where id < 4 || id > 5; 55 56 select substr from (select * from t1) sub where id < 4 || id > 5; 57 58 select ti,-si from (select * from t1) sub order by -si desc; 59 60 select * from (select * from t1) sub where (ti=2 or si=3) and (ch = 'bye' or vch = 'subquery'); 61 62 select * from (select * from (select * from (select id,ti,si,de from (select * from t1 ) sub3 where fl <> 4.5 ) sub2 where ti > 1) sub1 where id >2 ) sub where id > 4; 63 64 select * from (select * from t1 where id > 100) sub ; 65 66 67 68 -- @suite 69 -- @setup 70 drop table if exists t1; 71 drop table if exists t2; 72 drop table if exists t3; 73 create table t1 (libname1 varchar(21) not null primary key, city varchar(20)); 74 create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60)); 75 create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int); 76 insert into t2 values ('001','Daffy','Aducklife'); 77 insert into t2 values ('002','Bugs','Arabbitlife'); 78 insert into t2 values ('003','Cowboy','Lifeontherange'); 79 insert into t2 values ('000','Anonymous','Wannabuythisbook?'); 80 insert into t2 values ('004','BestSeller','OneHeckuvabook'); 81 insert into t2 values ('005','EveryoneBuys','Thisverybook'); 82 insert into t2 values ('006','SanFran','Itisasanfranlifestyle'); 83 insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 84 insert into t3 values('000','NewYorkPublicLibra',1); 85 insert into t3 values('001','NewYorkPublicLibra',2); 86 insert into t3 values('002','NewYorkPublicLibra',3); 87 insert into t3 values('003','NewYorkPublicLibra',4); 88 insert into t3 values('004','NewYorkPublicLibra',5); 89 insert into t3 values('005','NewYorkPublicLibra',6); 90 insert into t3 values('006','SanFransiscoPublic',5); 91 insert into t3 values('007','BerkeleyPublic1',3); 92 insert into t3 values('007','BerkeleyPublic2',3); 93 insert into t3 values('001','NYC Lib',8); 94 insert into t1 values ('NewYorkPublicLibra','NewYork'); 95 insert into t1 values ('SanFransiscoPublic','SanFran'); 96 insert into t1 values ('BerkeleyPublic1','Berkeley'); 97 insert into t1 values ('BerkeleyPublic2','Berkeley'); 98 insert into t1 values ('NYCLib','NewYork'); 99 -- @case 100 -- @desc:test for from subquery with join 101 -- @label:bvt 102 select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ; 103 104 drop table if exists t1; 105 drop table if exists t2; 106 drop table if exists t3; 107 create table t1(c1 int, c2 int); 108 insert into t1 values(1,1); 109 insert into t1 values(1,2); 110 insert into t1 values(1,3); 111 insert into t1 values(1,4); 112 insert into t1 values(1,5); 113 insert into t1 values(1,6); 114 create table t2(c3 int, c4 int); 115 insert into t2 values(1,1); 116 insert into t2 values(1,2); 117 insert into t2 values(1,3); 118 insert into t2 values(1,4); 119 insert into t2 values(1,5); 120 insert into t2 values(1,6); 121 create table t3(c5 int, c6 int); 122 insert into t3 values(1,1); 123 insert into t3 values(1,2); 124 insert into t3 values(1,3); 125 insert into t3 values(1,4); 126 insert into t3 values(1,5); 127 insert into t3 values(1,6); 128 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 129 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)<10; 130 select * from t1 where(select avg(t1.c1) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 131 select * from t1 where(select avg(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 132 select * from t1 where(select avg(t2.c4) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 133 select * from t1 where(select max(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2<t2.c4)=1; 134 select * from t1 where(select count(t2.c3) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4)=1; 135 select * from t1 where(select count(t2.c3) from t2 where t1.c1=t2.c3 and t2.c4<10)=1; 136 select * from t1 where t1.c1 in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 137 select * from t1 where t1.c1 not in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 138 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4+2)=0; 139 select * from t1 where(select count(*) from t2 where t1.c1=t2.c3 and t1.c2=t2.c4)<5; 140 select * from t1 where t1.c1 not in (select c6 from t3 where t1.c1=t3.c5 and t1.c2<t3.c6 and (select count(*) from t2 where t3.c5=t2.c3 and t3.c6=t2.c4)=1); 141 select * from t1 where t1.c2 not in (select c6 from t3 where t1.c2=t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=1); 142 select * from t1 where t1.c2 not in (select c6 from t3 where t1.c2=t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=t1.c2-2); 143 select * from t1 where t1.c2 not in (select c5 from t3 where t1.c2<t3.c6 and (select count(*) from t2 where t3.c6=t2.c4+2 )=t1.c2); 144 drop table if exists t1; 145 drop table if exists t2; 146 drop table if exists t3;