SQL nested Query Summary 01

1. The difference between where and having

Where is conditional filtering before the result of the query, and having is the conditional filter after the query result.

The most obvious difference is that the field name appended to the where must be a field that exists in the entity table to be queried, and the name of the field after having is the field that exists in the virtual table after the query results.

For example: SELECT Scoreid,cid as Cid_count from score have cid_count>2

2, group by followed by the field name, the role is to be grouped in this field.

Example: SELECT sid,count (*) as Cid_count from score GROUP by Sid

The results are displayed for each SID corresponding to the number of records.

3, where and group by the nesting use:

Example: SELECT sid,count (CID) as Cid_count from score WHERE score >90 GROUP by Sid

The results show the number of records for each SID that corresponds to score greater than 90.

4, COUNT (*) is empty, how to display as 0:

COUNT (case if score. ' Score ' >90 then 1 ELSE NULL END)

That is, when the number of records that find score greater than 90 is empty, it is displayed directly as 0 without this condition.

For example: SELECT student.name, COUNT (case if score. ' Score ' >90 then 1 ELSE NULL END) as Course_count from Course,student,sco Re WHERE student.sid=score. ' Sid ' and score. ' CID ' = course. ' CID ' GROUP by Score.sid;

The result shows the number of records for each SID corresponding to score greater than 90, which is displayed as 0 when empty.

Resources

Http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html

This article is from the "Work essay" blog, please be sure to keep this source http://oanyt.blog.51cto.com/6168303/1666104

SQL nested Query Summary 01