You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-29 08:21:15 +03:00
59 lines
1.3 KiB
Plaintext
59 lines
1.3 KiB
Plaintext
DROP DATABASE IF EXISTS mcol5195;
|
|
CREATE DATABASE mcol5195;
|
|
USE mcol5195;
|
|
create table t1 (a int, b int) engine=columnstore;
|
|
create table t2 (a int, b int) engine=columnstore;
|
|
insert into t1 values (1, 2), (1, 3), (1, 4), (2, 5), (2, 6), (2, 7);
|
|
insert into t2 values (1, 2), (1, 2), (1, 4), (2, 5), (2, 6), (2, 8);
|
|
select * from t1, t2 where t1.a = t2.a and t2.b = (select max(b) from t2 where t1.a = t2.a);
|
|
a b a b
|
|
1 2 1 4
|
|
1 3 1 4
|
|
1 4 1 4
|
|
2 5 2 8
|
|
2 6 2 8
|
|
2 7 2 8
|
|
select * from t1, t2 where t1.a = t2.a and t2.b < (select max(b) from t2 where t1.a = t2.a);
|
|
a b a b
|
|
1 2 1 2
|
|
1 2 1 2
|
|
1 3 1 2
|
|
1 3 1 2
|
|
1 4 1 2
|
|
1 4 1 2
|
|
2 5 2 5
|
|
2 5 2 6
|
|
2 6 2 5
|
|
2 6 2 6
|
|
2 7 2 5
|
|
2 7 2 6
|
|
select * from t1, t2 where t1.a = t2.a and t2.b > (select max(b) from t2 where t1.a = t2.a);
|
|
a b a b
|
|
select * from t1, t2 where t1.a = t2.a and t1.b = (select avg(t2.b) from t2 where t1.a = t2.a group by t2.a);
|
|
a b a b
|
|
select * from t1, t2 where t1.a = t2.a and t2.b < (select avg(t2.b) from t2 where t1.a = t2.a group by t2.a);
|
|
a b a b
|
|
1 2 1 2
|
|
1 2 1 2
|
|
1 3 1 2
|
|
1 3 1 2
|
|
1 4 1 2
|
|
1 4 1 2
|
|
2 5 2 5
|
|
2 5 2 6
|
|
2 6 2 5
|
|
2 6 2 6
|
|
2 7 2 5
|
|
2 7 2 6
|
|
select * from t1, t2 where t1.a = t2.a and t2.b > (select avg(t2.b) from t2 where t1.a = t2.a group by t2.a);
|
|
a b a b
|
|
1 2 1 4
|
|
1 3 1 4
|
|
1 4 1 4
|
|
2 5 2 8
|
|
2 6 2 8
|
|
2 7 2 8
|
|
drop table t1;
|
|
drop table t2;
|
|
DROP DATABASE mcol5195;
|