I. case when... then... else... end usage
The data table is as follows. When we want to query the data table, we can directly judge: for example, we can query Zhang San, male, Li Si 3, and female only need an SQL statement.
Select [name], (case when sex = 1 then 'male' else 'female' end) as sex from Table Name
Ii. Use of group
For example, the table is as follows:
ID name (name) warehouse quantity
01 watermelon 1 10
02 watermelon 2 30
03 rice 1 30
04 Apple 1 40
05 Apple 2 45
06 apple 3 5
Select name, Sun (price) from Table group by name
The preceding SQL statement returns the total number of items, regardless of the warehouse ..
Result:
Watermelon, 40
Rice, 30
Apple, 90
3. Having clauses:
Syntax format:
Select "column_name1", sum ("column_name2") 0
From "table_name" group by "column_name1"
Having (arithematic function condition)
For example, we may only want to see information about stores with a total sales volume of more than 1500 US dollars in the store_information table. Then we need to use the having clause:
Select store_name, sum (sales)
From store_information
Group by store_name
Having sum (sales)> 1500
4. A date-based SQL statement
For example, please retrieve all records whose date (sendtime field) is the current day from the tb_send table? (The sendtime field is of the datetime type, including the date and time)
------------------------------------------
Select * from TB where datediff (DD, sendtime, getdate () = 0
5. query all records of a table in. If the time field is recordtime, the table name is Table1.
Statement 1:
Select * From Table1 where year (recordtime) = 2009 and month (recordtime) = 11
Statement 2:
Select * From Table1 where convert (nchar (10), logintime, 112) between '2017-12-01 'and '2017-12-31'