Using SELECT syntax three in ADO
Author：Eve Cole
Update Time：2009-05-30 19:54:33
Continuing from the previous article:
ORDER BY
ORDER BY clause, you can set the sorting field.
In a SQL expression, the ORDER BY clause is usually placed last.
If you want to sort in descending order (Z~A, 9~0), you must add the DESC word at the end of each field you want to sort in descending order. for example:
SELECT name
FROM employees
ORDER BY salaryDESC, age
Indicates that the [Salary] field is sorted in descending order, and the [Age] field is sorted in increasing order.
The sorted fields in the ORDER BY clause cannot contain MEMO field types or OLE object types, otherwise an error will occur.
When multiple fields are included in the ORDER BY clause, the first field after ORDER BY is used for sorting. Then, if there are identical data records in this field, the second field will be used to sort, and so on.
GROUP BY
GROUP BY will make statistics on the query results. The syntax is as follows:
SELECT fieldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist]
Using the WHERE clause, you can set data that you do not want to count, and using the HAVING clause, you can filter fields that have already been counted.
Statistics cannot be made on fields of MEMO field type or OLE object type, otherwise an error will occur.
For example, the ASP program rs22.asp is as follows, [SELECT Category, Avg (Price) As Average From Product Group By Category] Use GROUP BY to make [Category] statistics on the query results, and calculate the statistics of each category to average the price Avg (Price):
<%
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 Category, Avg(Price) As Average From Product Group By Category"
Response.Write "<p>Avg(price)"
rs2.Open SqlStr,conn1,1,1
Do while not rs2.EOF
Response.Write "<BR>" & rs2("kind") & ": " & rs2("average")
rs2.MoveNext
Loop
rs2.Close%>
For the above ASP program rs22.asp, the client uses a browser to browse the execution results and display the average price based on [category] statistics.
Let us look at an example. For example, the ASP program rs22.asp is as follows, [SELECT Category, Sum(Quantity*Price) As Total From Product Group By Category] Use GROUP BY to make [Category] statistics on the query results, and count each category. The total price Sum (quantity * price) multiplied by quantity and price:
<%
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 Category, Sum(Quantity*Price) As Total From Product Group By Category"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Sum(quantity*price)"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("category") & ": " & rs2("total")
rs2.MoveNext
Loop
rs2.Close%>
For the above ASP program rs22.asp, use the browser on the client to browse the execution results and display the total price based on [category].
Groupfieldlist is the name of the fields used for statistics, with a maximum of 10 fields.
The order of the field names in the groupfieldlist will determine the statistical level, from the highest to the lowest level.
Finally, for example, the ASP program rs22.asp is as follows, [SELECT name, subject, Avg (score) As average From examination Group By name, subject] Use GROUP BY to make the query results based on [name] and [subject] statistics, statistics of each Statistical [average] score Avg (score):
<%
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"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Group By name, subject"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("Name") & " " & rs2("Subject") & " Average: " & rs2("Average")
rs2.MoveNext
Loop
rs2.Close
%>
The above ASP program rs22.asp uses a browser on the client side to browse the execution results and display the [average] score based on [name] and [subject].
Have you learned it?
