github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_year.test (about)

     1  # 嵌套
     2  # SELECT YEAR(NOW()) AS Current_Year;
     3  SELECT YEAR(date_add("2021-09-30", interval 200 day)) AS year;
     4  
     5  
     6  # EXTREME VALUE
     7  SELECT YEAR("10000-12-21") as year;
     8  SELECT YEAR("-1234-12-21") as year;
     9  SELECT YEAR("12415141412-12-21") as year;
    10  SELECT YEAR("0000-00-00") as year;
    11  SELECT YEAR("2049-14-34") as year;
    12  
    13  
    14  
    15  # 多种类型
    16  SELECT YEAR("2015-09") as year;
    17  
    18  
    19  SELECT YEAR(2015) as year;
    20  
    21  SELECT YEAR(1340124) as year;
    22  SELECT YEAR(0.45133) as year;
    23  
    24  SELECT YEAR(1=1) as year;
    25  
    26  
    27  SELECT YEAR('2015-09.30') as year;
    28  
    29  
    30  SELECT YEAR('2015-0') as year;
    31  
    32  
    33  SELECT YEAR('2015-09.30 12') as year;
    34  
    35  
    36  SELECT YEAR('2015-09-26 08:09:22') AS Year ;
    37  
    38  #算术操作
    39  
    40  SELECT YEAR("2015-09-10")- YEAR("2016-04-24");
    41  
    42  SELECT YEAR("2015-09-10")+ YEAR("2016-04-24");
    43  
    44  SELECT YEAR("2015-09-10")*YEAR("2016-04-24");
    45  
    46  SELECT YEAR("2015-09-10")/YEAR("2016-04-24");
    47  
    48  #比较操作
    49  SELECT YEAR("2015-09-10")>YEAR("2016-04-24");
    50  SELECT YEAR("2015-09-10")<YEAR("2016-04-24");
    51  SELECT YEAR("2015-09-10")=YEAR("2016-04-24");
    52  SELECT YEAR("2015-09-10")<>YEAR("2016-04-24");
    53  SELECT YEAR("2015-09-10")!=YEAR("2016-04-24");
    54  
    55  
    56  #NULL值
    57  SELECT YEAR(NULL) AS Year ;
    58  
    59  #SELECT, ORDER BY, GROUP BY
    60  CREATE TABLE Product(Product_id INT,  Product_name VARCHAR(100) NOT NULL,Buying_price DECIMAL(13, 2) NOT NULL,Selling_price DECIMAL(13, 2) NOT NULL,Selling_Date Date NOT NULL,PRIMARY KEY(Product_id));
    61  INSERT INTO  Product(Product_name, Buying_price, Selling_price, Selling_Date) VALUES ('Audi Q8', 10000000.00, 15000000.00, '2018-01-26' ),('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20' ),('Audi A6', 4000000.00, 5000000.00, '2018-07-25' ),('BMW X5', 5000500.00, 7006500.00, '2018-10-18'  ),('Jaguar XF', 5000000, 7507000.00, '2019-01-27'  ),('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2019-04-01'  ),('Jaguar F-PACE', 5000000.00, 7000000.00, '2019-12-26'  ),('Porsche Macan', 6500000.00, 8000000.00, '2020-04-16' ) ;
    62  SELECT * FROM Product;
    63  SELECT  YEAR (Selling_Date) year,  COUNT(Product_id) Product_Sold FROM Product GROUP BY YEAR (Selling_Date) ORDER BY YEAR (Selling_Date);
    64  DROP TABLE Product;
    65  
    66  #INSERT
    67  drop table if exists t1;
    68  create table t1(a INT,  b date);
    69  insert into t1 select year("2012-10-12"), "2012-10-12";
    70  insert into t1 select year("2004-04-24"), "2004-04-24";
    71  insert into t1 select year("2008-12-04"), "2008-12-04";
    72  select * from t1;
    73  drop table t1;
    74  
    75  #WHERE
    76  
    77  drop table if exists t1;
    78  create table t1(a INT,  b date);
    79  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    80  select * from t1 where year(b)="2012";
    81  drop table t1;
    82  
    83  
    84  #ON CONDITION
    85  drop table if exists t1;
    86  drop table if exists t2;
    87  create table t1(a INT,  b date);
    88  create table t2(a INT,  b date);
    89  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    90  insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12");
    91  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (year(t1.b) = year(t2.b));
    92  drop table t1;
    93  drop table t2;
    94  
    95  
    96  #HAVING
    97  
    98  drop table if exists t1;
    99  create table t1(a INT,  b date);
   100  insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   101  select b from t1 group by b having year(b)="2012";
   102  drop table t1;
   103  
   104  
   105  #distinct
   106  drop table if exists t1;
   107  create table t1(a INT,  b date);
   108  insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   109  select distinct year(b) from t1;
   110  drop table t1;
   111  
   112  
   113  CREATE TABLE t3(c1 DATE NOT NULL);
   114  INSERT INTO t3 VALUES('2000-01-01');
   115  INSERT INTO t3 VALUES('1999-12-31');
   116  INSERT INTO t3 VALUES('2000-01-01');
   117  INSERT INTO t3 VALUES('2006-12-25');
   118  INSERT INTO t3 VALUES('2008-02-29');
   119  SELECT YEAR(c1) FROM t3;
   120  DROP TABLE t3;
   121  CREATE TABLE t3(c1 DATETIME NOT NULL);
   122  INSERT INTO t3 VALUES('2000-01-01');
   123  INSERT INTO t3 VALUES('1999-12-31');
   124  INSERT INTO t3 VALUES('2000-01-01');
   125  INSERT INTO t3 VALUES('2006-12-25');
   126  INSERT INTO t3 VALUES('2008-02-29');
   127  SELECT YEAR(c1) FROM t3;
   128  DROP TABLE t3;
   129  
   130  
   131  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   132  INSERT INTO t3 VALUES('2000-01-01');
   133  INSERT INTO t3 VALUES('1999-12-31');
   134  INSERT INTO t3 VALUES('2000-01-01');
   135  INSERT INTO t3 VALUES('2006-12-25');
   136  INSERT INTO t3 VALUES('2008-02-29');
   137  SELECT YEAR(c1) FROM t3;
   138  DROP TABLE t3;