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

One, sub-query

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

Clause returns the value of a value:

For example:

I only know c026 this number, I want to check the car price is lower than the entire vehicle information
Select *from Car where price< (select price from car where code= ' c026 ')--Performs a value in parentheses before executing the outside. When a value is returned in parentheses

If the clause returns a column value instead of a value:

For example:

Search for all vehicle information equivalent to c016 or equivalent to c029 or equivalent to c014

SELECT * from car where oil in (select oil from car where code= ' c016 ' or code= ' c029 ' or code= ' c014 ')

In usage:

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

If you want to fuel consumption other than 7.4,8,8.3 car information: SELECT * from the car where oil is not in (7.4,8,8.3)

Fuel consumption from 7 to 8 vehicle information (including 7 and 8)

Execute the same results, but the first one is better

Any usage:

Fuel consumption is greater than any one number, that is, the fuel consumption is greater than the minimum value. If the fuel consumption is less than any one number, then the fuel consumption is less than the maximum value.

All usage:

SELECT * from car where oil> all (7.4,8,8.3)

Fuel consumption is greater than all, then the fuel consumption is greater than the maximum value. If fuel consumption is less than all, then the fuel consumption is less than the minimum value.

To return a query from more than one table to a table:

Query the sname, CNO, and degree columns for all students.

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

Query the SNO, CNAME, and degree columns for all students.

Select Sno, (select Cname from Course where course.cno=score.cno), degree from score

--where Sno and degree are in the score table, the CNAME is in the course table, where two tables have COURSE.CNO=SCORE.CNO connections

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

Second, aggregate function

1, Max,min: Take maximum value, minimum value

--inquire about the car with the lowest fuel consumption

SELECT * FROM car where oil= (select max (oil) from car)

2, Avg: Averaging

--Check the average price of the car

Where as is used to alias

3, Count: Take the total number

--Find out how many cars there are.

is to find out how many lines

4. Sum: Sum

--Find out the total price of all cars

Group by fuel consumption and see how many cars are in each group

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