SQL statement used in sqls. SQL statement

SQL statement used in sqls. SQL statement

Sqlite has been used for many queries recently, including table connection queries. Record it to avoid forgetting it!

1. First sort by time and then select the first 10:

Select * from MyBill order by createTime desc limit 10;

2. Sum of price:

Select sum (price) as sum from MyBill;

3. There is a category table and a bill table, which is sorted by the primary category statistics in the category:

Select sum (mysum), tb. pid from"
+ "(Select sum (price) as mysum, consumetype. title as tt, consumetype. parentid as pid from mybill"
+ "Left join consumetype on mybill. typeid = consumetype. id"
+ "Group by mybill. typeid) as tb group by tb. pid order by sum (mysum) desc ";

4. There is a category table and a bill table, which is sorted by subcategory statistics in the category:

Select sum (price), consumetype. title from mybill"
+ "Left join consumetype on mybill. typeid = consumetype. id"
+ "Group by mybill. typeid order by sum (price) desc ";

5. Compare and select Based on Time:

Select * from MyBill where createTime <datetime ('2017-05-19 ')

The corresponding time functions are as follows:

* Select datetime ('now ');
* Select datetime ('2017-10-17 00:20:00 ',' + 1 hour ','-12 minute ');
* Select date ('2014-10-17 ',' + 1 Day', '+ 1 year ');
* Select datetime ('now ', 'start of year ');
* Select datetime ('now ', 'start of month ');
* Select datetime ('now ', 'start of Day ');
* Select datetime ('now ',' + 10 hour ', 'start of Day',' + 10 hour ');
* Select datetime ('now ', 'localtime ');
* Select datetime ('now ',' + 8 hour ');

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.