2 CREATE TABLE t1(f1 INT,f2 BIGINT,f3 DOUBLE,f4 DATE);
4 INSERT INTO t1 VALUES(100,100,100,'2009-11-23');
5 INSERT INTO t1 VALUES(200,200,200,'2009-11-25');
6 INSERT INTO t1 VALUES(100,300,300,'2009-11-27');
7 INSERT INTO t1 VALUES(200,400,400,'2009-11-29');
8 INSERT INTO t1 VALUES(300,500,500,'2009-12-01');
10 f1(int)=100 | f2(bigint)=100 | f3(double)=100.000000 | f4(date)=2009-11-23 |
11 f1(int)=200 | f2(bigint)=200 | f3(double)=200.000000 | f4(date)=2009-11-25 |
12 f1(int)=100 | f2(bigint)=300 | f3(double)=300.000000 | f4(date)=2009-11-27 |
13 f1(int)=200 | f2(bigint)=400 | f3(double)=400.000000 | f4(date)=2009-11-29 |
14 f1(int)=300 | f2(bigint)=500 | f3(double)=500.000000 | f4(date)=2009-12-1 |
16 select count(f4) from t1 = 5
18 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1;
19 f1=100 | sum(f2)=400 | avg(f3)=200.000000 | max(f4)=2009-11-27 |
20 f1=200 | sum(f2)=600 | avg(f3)=300.000000 | max(f4)=2009-11-29 |
21 f1=300 | sum(f2)=500 | avg(f3)=500.000000 | max(f4)=2009-12-1 |
23 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having max(f4)>'2009/11/29';
24 f1=300 | sum(f2)=500 | avg(f3)=500.000000 | max(f4)=2009-12-1 |
26 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having (max(f4) >'2009/11/26' and max(f4) < '2009-11-28') or avg(f3) > 400;
27 f1=100 | sum(f2)=400 | avg(f3)=200.000000 | max(f4)=2009-11-27 |
28 f1=300 | sum(f2)=500 | avg(f3)=500.000000 | max(f4)=2009-12-1 |