|
|
论坛精华
>> SQL之家
>> 使用SQL COUNT命令
由 amtd 发布于: 2001-02-15 09:39
Using The SQL COUNT Command
Well here we are you have a database with several thousand or maybe even several million records, and you
are wanting to pull Statistics out of it to provide to the big boss men. Well there are really two ways to
do this and we will cover both even though one is better than the other and one also brings back your
results a lot quicker.
First I will cover what I call LOOP Statistics, this is where a search is run on the database and you
use a LOOP through the entire database adding 1 to a variable when a specific condition is meet. Below is
an example of this......
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Appointments_Database"
sql = "SELECT Appt_Type FROM Appointments WHERE Appt_Date = #1/1/1999#"
Set RS = objConn.Execute(sql)
Do While NOT RS.EOF 'Start Of Loop
If RS("Appt_Type") = 'FOLLOW-UP' Then
nFollowup = nFollowup + 1
ElseIf RS("Appt_Type") = 'NEW' Then
nNew = nNew + 1
End If
RS.MoveNext
Loop
objConn.Close
%>
<HTML>
<HEAD>Test Counting Page</HEAD>
<BODY>
Follow-Up Appointments = <%=nFollowup%>
New Appointments = <%=nNew%>
</BODY>
</HTML>
As you can see in the above example we are looking through an Appointments Database and are pulling
out all Appointment Types on the day of 1/1/1999. The SQL Code is executed and the LOOP begins. If the
Appointment Type is FOLLOW-UP then 1 is added to the nFollowup Variable or if it is a NEW Appointment Type
then 1 is added to the nNew Variable. This will keep going until the End Of File is reached. Then we write
out the Nfollowup and nNew variables to display the numbers. This is an OK way of doing this on small
databases, but as your database grows the slower and slower this SQL Query will run.
So how do I do the COUNTing with out looping through the entire database. Well your in luck that SQL
has the COUNT Parameter that you can use to get the statistics your looking for. Lets try the same example
from above but use the COUNT Parameter....
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Appointments_Database"
%>
<HTML>
<HEAD>Test Counting Page</HEAD>
<BODY>
<%
sql = "SELECT COUNT(Appt_Type) AS ApptFollowup_Count FROM Appointments WHERE Appt_Date = #1/1/1999# AND
Appt_Type = 'FOLLOW-UP'"
Set RS = objConn.Execute(sql)
%>
Follow-Up Appointments = <%=RS("ApptFollowUp_Count")%>
<%
sql = "SELECT COUNT(Appt_Type) AS ApptNew_Count FROM Appointments WHERE Appt_Date = #1/1/1999# AND
Appt_Type = 'New'"
Set RS = objConn.Execute(sql)
%>
New Appointments = <%=RS("ApptNew_Count")%>
<% objConn.Close %>
</BODY>
</HTML>
Well as you can see this way to COUNT records is a lot different from the previous example as we do
not run the LOOP through the database. And the best part of using the COUNT Parameter is that this will
drastically speed up you SQL Query and provide information to your users a lot quicker.
__________________
|
资料来源: JSP001.com
|