github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_anyvalue.test (about) 1 2 #ORDER BY, WHERE 3 create table t1(a int, b int, c int); 4 insert into t1 values(200,1,1),(100,1,2),(400,2,2),(300,2,1); 5 SELECT distinct 1 FROM t1 group by a order by any_value(count(*)-count(b)); 6 SELECT distinct 1 FROM t1 group by a order by any_value(count(*))-any_value(count(b)); 7 SELECT DISTINCT GP1.a AS g1 FROM t1 AS GP1 8 WHERE GP1.a >= 0 9 ORDER BY 2+ANY_VALUE(GP1.b) LIMIT 8; 10 drop table t1; 11 12 #SELECT 算式运算 13 create table t1( 14 a int, 15 b int, 16 c int 17 ); 18 create table t2( 19 a int, 20 b int, 21 c int 22 ); 23 insert into t1 values(1,10,34),(2,20,14); 24 insert into t2 values(1,-10,-45); 25 select ANY_VALUE(t1.b) from t1 left join t2 on t1.c=t1.b and t1.a=t1.c group by t1.a; 26 select 3+(5*ANY_VALUE(t1.b)) from t1 left join t2 on t1.c=t1.b and t1.a=t1.c group by t1.a; 27 delete from t1; 28 insert into t1 (a,b) values(1,10),(1,20),(2,30),(2,40); 29 select any_value(a), sum(b) from t1; 30 select any_value(a), sum(b) from (select * from t1 order by a desc) as d; 31 select a,any_value(b),sum(c) from t1 group by a; 32 select a,any_value(b),sum(c) from (select * from t1 order by a desc, b desc) as d group by a; 33 drop table t1; 34 drop table t2; 35 36 #NULL 37 select any_value(null); 38 #嵌套 39 SELECT any_value(floor(0.5413)); 40 41 #算式操作 42 SELECT any_value(floor(0.5413))-any_value(ceiling(0.553)); 43 44 #DATATYPE 45 create table t1(a tinyint, b SMALLINT, c BIGINT, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 46 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 47 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 48 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 49 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 50 select any_value(a) from t1; 51 select any_value(b) from t1; 52 select any_value(c) from t1; 53 select any_value(d) from t1; 54 select any_value(e) from t1; 55 select any_value(f) from t1; 56 select any_value(g) from t1; 57 select any_value(h) from t1; 58 select any_value(i) from t1; 59 select any_value(k) from t1; 60 select any_value(l) from t1; 61 select any_value(m) from t1; 62 select any_value(n) from t1; 63 drop table t1; 64 65 #0.5暂不支持time类型 66 #create table t1(a time) 67 #insert into t1 values("10:03:12"); 68 #insert into t1 values("10:03:12"); 69 #insert into t1 values("10:03:12"); 70 #insert into t1 values("10:03:12"); 71 #select any_value(a) from t1; 72 #drop table t1; 73 74 #EXTREME VALUE 75 --- @bvt:issue#3579 76 select any_value(9999999999999999999999999999.9999999999); 77 --- @bvt:issue 78 select any_value("0000-00-00 00:00:00"); 79 select any_value("你好"); 80 81 #WHERE, INSERT, distinct 82 drop table if exists t1; 83 create table t1(a INT, b float); 84 insert into t1 values(12124, -4213.413), (12124, -42413.409); 85 select distinct * from t1 where any_value(a)>12100; 86 drop table t1; 87 88 89 #ON CONDITION 90 drop table if exists t1; 91 drop table if exists t2; 92 create table t1(a INT, b float); 93 create table t2(a INT, b float); 94 insert into t1 values(12124, -4213.413), (1212, -42413.409); 95 insert into t2 values(14124, -4213.413), (8479, -980.409); 96 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (any_value(t1.b) = any_value(t2.b)); 97 drop table t1; 98 drop table t2; 99 100 #HAVING，比较操作 101 drop table if exists t1; 102 create table t1(a float); 103 insert into t1 values(14124.413), (-4213.413), (984798.123), (-980.409); 104 select a from t1 group by a having any_value(a)<0; 105 drop table t1; 106 107 108