"2017-03-12" SQL Sever subquery, aggregate function

One, sub-query

Use a query statement as a value
The query result of a clause must be a column
Clause can return multiple rows of data, but must be a column

SELECT * FROM table name where column name =,>,< (SELECT * from table name where column name ...)

In/not in

Column name in (value, value, value) indicates that the value is within the value provided below, equivalent to column name = value or column name = value or column name = value

Example: SELECT * from car where oil=7.4 or oil=8 or oil=8.3 is identical to select *from car where oil in (7.4,8,8.3) execution results

Column name not in (value, value, value) means that the value is not within the value provided below, which is equivalent to the column name! = value and column name! = value and column name! = value

Example: SELECT * from car where oil not in (7.4,8,8.3) with select *from Cat where oil!=7.4 and oil!=8 and oil!=8.3

Between and

The value between A and B is equivalent to the value >=a and value <=b, which represents a value between a, B and both

Any

Value >any (a,b,c) is the minimum value greater than the number of A,B,C3

Value <any (a,b,c) is the maximum value less than the number of A,B,C3

All

Value >all (A,B,C) is greater than the maximum value in the number of A,B,C3

The value <all (A,B,C) is less than the minimum value in the A,B,C3 number

* Query two columns of data from two different tables

Make contact with the same column in the two tables through a subquery

Example: Query all students for Sname, CNO, and degree columns.

Select (select Sname from Student where Student.sno=score.sno), Cno,degree from score

--where sname is in the Student table, CNO and degree in the score table, where two tables have Student.sno=score.sno connections

-- use a table with more data to query the main table, where score is the main table

Second, aggregate function

1.Max (max), min (min)

2.avg Average

3.count counts rows

4.Sum sum

* Data queried using an aggregate function does not have a column name, you can add the column name by adding the as name after the aggregate function

Group queries and count ascending and descending order

Select Column name, COUNT (*) from table name Group By column name order by COUNT (*) Asc/desc

Group Query and Count filter grouping after filtering requires having to replace where

Select Column name, COUNT (*) from table name Group BY column name having COUNT (*) >,<,= value

"2017-03-12" SQL Sever subquery, aggregate function