Using SELECT syntax four in ADO
Author：Eve Cole
Update Time：2009-05-30 19:54:28
HAVING
HAVING is used in SELECT expressions to filter records that have been counted by GROUP BY. After GROUP BY counts records, HAVING will filter records that match the conditions in the HAVING clause.
The syntax is as follows:
SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
[HAVING groupcriteria]
.groupcriteria indicates the statistical records that determine which should be filtered.
HAVING is similar to WHERE and is used to decide which records to select. When GROUP BY is used to count records, HAVING will determine the records that should be displayed, for example:
SELECT product name
FROM products
GROUP BY Category
HAVING unit price > 1000
A HAVING clause can contain up to 40 operational expressions, and the operational expressions will be connected by logical operators such as AND or OR.
Let's look at an example of using this SQL command in an ASP program.
We can use the HAVING clause to determine the records that should be displayed. For example, the ASP program rs23.asp is as follows, [SELECT Name, Subject, Avg (Score) As Average From Exam Group By Name, Subject Having Avg (Score) >=60], use Having Avg(score) >=60 Find records with an average score greater than or equal to 60 points:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT Name, Subject, Avg (Score) As Average From Exam Group By Name, Subject Having Avg (Score) >=60"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Having Avg(score) >=60"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("Name") & " " & rs2("Subject") & " Average: " & rs2("Average")
rs2.MoveNext
Loop
rs2.Close
%>
The above ASP program rs23.asp uses a browser on the client to browse the execution results and display the records whose average score is greater than or equal to 60 points.
We can also use the HAVING clause to find duplicate records. For example, the ASP program rs23.asp is as follows, [SELECT Code From Product Group By Code Having Count (Code) > 1], use Having Count (Code) > 1 to find duplicate codes Records:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT Code From Product Group By Code Having Count(code) > 1"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Find duplicates Having Count(code) > 1"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("codename")
rs2.MoveNext
Loop
rs2.Close
%>
For the above ASP program rs23.asp, use a browser on the client side to browse the execution results and display records with duplicate code names.
Union
Union can combine the results of multiple sets of queries.
The syntax is as follows:
Query 1 UNION [ALL] Query 2 [UNION [ALL] Query 3 [ ... ]]
The query is a SELECT expression.
When you use a UNION operation, duplicate records will not be returned; if you want to return all records, you can add ALL after UNION. Adding ALL will execute the query faster.
All queries in a UNION operation must have the same number of fields. Field sizes can be different, and field data types can also be different.
Aliases can be used only in the first SELECT expression and are omitted in other SELECT expressions.
You can use the GROUP BY or HAVING clause in each SELECT expression to count the results of the query.
You can use the ORDER BY clause in the last SELECT expression to specify the order in which the query results are sorted.
Let's look at an example of using this SQL command in an ASP program.
Union can be used to merge the results of two sets of queries. For example, the ASP program rs25.asp is as follows, [(SELECT name, subject, score From exam Where subject='arithmetic' and name='Li Si') Union (SELECT name, subject, score) From exam Where subject='arithmetic' and name='Zhang San')], use Union to merge the results of two sets of SELECT queries, one group is to query the arithmetic score records of Li Si, and the other is to query the arithmetic score records of Zhang San:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "(SELECT name, subject, scoreFrom exam Where subject='arithmetic' and name='Li Si') Union (SELECT name, subject, scoreFrom exam Where subject='arithmetic' and name='Zhang San') "
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Union"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")
rs2.MoveNext
Loop
rs2.Close
%>
The above ASP program rs25.asp uses a browser on the client to browse the execution results and display the arithmetic fraction records of John Doe and John Doe.
