Using SELECT-SQL syntax in ASP's ADO (iii)

After an article:

ORDER BY

ORDER BY clause, you can set the sorted field.

In an SQL expression, the ORDER BY clause is usually placed at the end.

To sort in descending order (z~a,9 ~0), add desc words to the end of each field that you want to sort descending. Such as:

SELECT Name

From employee

Order by wage DESC, age

Indicates that the [Payroll] field is sorted in descending order, and the age field is sorted in ascending order.

The fields that are sorted in the ORDER BY clause may not contain Memo field types or OLE object types, otherwise an error occurs.

When you include more than one field in an ORDER BY clause, you first sort by the first field after the orders by. Then, if you have the same data record in this field, the column is sorted in the second field, and so on.

GROUP by

GROUP by counts the results of the query as follows:

SELECT FieldList
From table
WHERE criteria
[GROUP by Groupfieldlist]

Use the WHERE clause to set up data that you do not want to do statistics, and use the HAVING clause to filter the fields that have been counted.

You cannot make statistics on fields that are Memo field types or OLE object types, otherwise an error occurs.

For example, the ASP program rs22.asp as follows, [SELECT kind, avg (price) as average from product Group by kind] use Group by to make the results of the query [category] statistics, statistics of various kinds of statistics to average price avg (price):

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

SQLSTR = "Select Kind, avg (price) as average from product Group by Kind"

Response.Write "<p>avg (Price)"

Rs2. Open sqlstr,conn1,1,1

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("Kind") & ":" & Rs2 ("average")

Rs2. MoveNext

Loop

Rs2. Close%>

The above ASP program rs22.asp, the client uses the browser, browses the execution result, displays according to [kind] the statistic to calculate the average price.

Let's take a look at an example, such as ASP program rs22.asp as follows, [SELECT kind, Sum (quantity * price) as total from product Group by kind] use Group by to make the results of the query [category] statistics, statistics of the number of each species and the price of the lump sum su M (quantity * price):

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

SQLSTR = "Select Kind, Sum (quantity * price) as total from product Group by Kind"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>sum (quantity * price)"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("Kind") & ":" & Rs2 ("Total")

Rs2. MoveNext

Loop

Rs2. Close%>

The above ASP program rs22.asp, uses the browser in the client side, browses the execution result, displays according to [kind] to count the total price.

Groupfieldlist is the name of the field to be counted, up to 10 fields.

The order of field names in Groupfieldlist will determine the level of statistics, grouped by the highest to lowest levels.

Finally, for example, ASP program rs22.asp is as follows, [SELECT name, account, AVG (score) as average from test Group by name, subject] Use Group by to make the results of the query based on [name] and [account] statistics, statistical statistics of the [average] Number avg (score):

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

sqlstr = "Select Name, Account, AVG (score) as average from test Group by name, subject"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>group by name, subject"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("name") & "& Rs2 (" Account ") &" Average: "& rs2 (" average ")

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs22.asp, uses the browser in the client side, browses the execution result, displays according to [name] and [the account] to count [average] score.

Have you learned?