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

Having

Having is used in a select expression to filter records that are already group by statistics. After the group by statistic record, the having will filter the 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 that the statistical records that should be filtered are determined.

Having is similar to where it is used to determine which records to select. When you use group by to Count Records, a having determines which records should be displayed, such as:

SELECT Product Name

From product

GROUP by Category

Having unit price > 1000

A HAVING clause can contain up to 40 expressions, and the expressions are linked by logical operators such as and OR OR.

Let's look at an example of using this SQL instruction in an ASP program.

We can use the HAVING clause to determine which records should be displayed, such as the ASP program rs23.asp as follows, [SELECT name, account, AVG (score) as average from test Group by name, subject having AVG (score) >=60], use hav ing avg (score) >=60 to 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") & ";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 having AVG (score) >=60"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>having AVG (score) >=60"

Do as not rs2. Eof

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

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs23.asp, uses the browser in the client side, browses the execution result, displays finds the average score is greater than or equal to 60 points record.

We can also use the HAVING clause to find duplicate records, such as the ASP program rs23.asp as follows, [SELECT code from product Group by symbol having Count (code name) > 1], using having count (code) > 1 Find the record of the Code name repetition:

<%

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 code from product Group by symbol having Count (code name) > 1"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p> Find duplicates having Count (code name) > 1"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("code")

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs23.asp, uses the browser in the client side, browses the execution result, displays the name duplicate record.

Union

The 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, no duplicate records are returned; to return all records, you can add all to the union, plus all to execute the query faster.

All queries in a union operation must have the same number of fields. Field sizes can vary, and field data types can be different.

Aliases can only be used in the first select expression and are omitted from other select expressions.

You can use a group by or a HAVING clause in each select expression to count the results of a query.

You can use the ORDER BY clause in the last select expression to specify the ordering of the results of the query.

Let's look at an example of using this SQL instruction in an ASP program.

You can combine the results of two sets of queries using Union, such as the ASP program rs25.asp, [(select name, account, score from test Where subject = ' arithmetic ' and name = ' Dick ') Union (select name, subject, score from Test W Here subjects = ' arithmetic ' and name = ' John '], using union to combine the results of two select queries, one for the dick of the arithmetic record of the query and the other for the John's arithmetical record:

<%

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, score from exam where account = ' arithmetic ' and name = ' Dick ') Union (select name, account, score from exam where subject = ' arithmetic ' and name = ' John ')"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>union"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("name") & "& Rs2 (" Account ") &" Score: "& Rs2 (" Score ")

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs25.asp, uses the browser in the client side, browses the execution result, displays the dick and John's arithmetic score record.