📄 451.html
字号:
<STYLE type=text/css>
<!--
body,td { font-size:9pt;}
hr { color: #000000; height: 1px}
-->
</STYLE>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML>
<HEAD><TITLE>论坛精华 >> SQL之家 >> 使用SQL COUNT命令</title>
</head>
<body >
<p><IMG SRC="../image/jsp001_middle_logo.gif" WIDTH="180" HEIGHT="60" BORDER=0 ALT=""></p>
<table width=100% bgcolor="#cccccc" align=center cellpadding="2" cellspacing="0" border=1 bordercolorlight="#000000" bordercolordark="#FFFFFF">
<tr bgcolor="#EFF8FF"><td>
<a href=http://www.jsp001.com/list_thread.php?int_attribute=4>论坛精华</a>
>> <a href=http://www.jsp001.com/list_thread.php?forumid=45&int_attribute=4>SQL之家</a>
>> 使用SQL COUNT命令 [<a href=http://www.jsp001.com/forum/showthread.php?goto=newpost&threadid=451>查看别人的评论</a>]<br>
<hr><p>由 amtd 发布于: 2001-02-15 09:39</p><p> </p><p><br><br>Using The SQL COUNT Command<br> <br>Well here we are you have a database with several thousand or maybe even several million records, and you <br>are wanting to pull Statistics out of it to provide to the big boss men. Well there are really two ways to <br>do this and we will cover both even though one is better than the other and one also brings back your <br>results a lot quicker. <br> First I will cover what I call LOOP Statistics, this is where a search is run on the database and you <br>use a LOOP through the entire database adding 1 to a variable when a specific condition is meet. Below is <br>an example of this......<br><br> <br><br><%<br>Set objConn = Server.CreateObject("ADODB.Connection")<br>objConn.Open "Appointments_Database"<br>sql = "SELECT Appt_Type FROM Appointments WHERE Appt_Date = #1/1/1999#"<br>Set RS = objConn.Execute(sql)<br>Do While NOT RS.EOF 'Start Of Loop<br>If RS("Appt_Type") = 'FOLLOW-UP' Then<br> nFollowup = nFollowup + 1<br>ElseIf RS("Appt_Type") = 'NEW' Then<br> nNew = nNew + 1<br>End If<br>RS.MoveNext<br>Loop<br>objConn.Close<br>%><br><HTML><br><HEAD>Test Counting Page</HEAD><br><BODY><br>Follow-Up Appointments = <%=nFollowup%><br>New Appointments = <%=nNew%><br></BODY><br></HTML> <br><br> As you can see in the above example we are looking through an Appointments Database and are pulling <br>out all Appointment Types on the day of 1/1/1999. The SQL Code is executed and the LOOP begins. If the <br>Appointment Type is FOLLOW-UP then 1 is added to the nFollowup Variable or if it is a NEW Appointment Type <br>then 1 is added to the nNew Variable. This will keep going until the End Of File is reached. Then we write <br>out the Nfollowup and nNew variables to display the numbers. This is an OK way of doing this on small <br>databases, but as your database grows the slower and slower this SQL Query will run.<br><br> So how do I do the COUNTing with out looping through the entire database. Well your in luck that SQL <br>has the COUNT Parameter that you can use to get the statistics your looking for. Lets try the same example <br>from above but use the COUNT Parameter....<br><br><%<br>Set objConn = Server.CreateObject("ADODB.Connection")<br>objConn.Open "Appointments_Database"<br>%><br><HTML><br><HEAD>Test Counting Page</HEAD><br><BODY><br><%<br>sql = "SELECT COUNT(Appt_Type) AS ApptFollowup_Count FROM Appointments WHERE Appt_Date = #1/1/1999# AND <br>Appt_Type = 'FOLLOW-UP'"<br>Set RS = objConn.Execute(sql)<br>%><br>Follow-Up Appointments = <%=RS("ApptFollowUp_Count")%><br><%<br>sql = "SELECT COUNT(Appt_Type) AS ApptNew_Count FROM Appointments WHERE Appt_Date = #1/1/1999# AND <br>Appt_Type = 'New'"<br>Set RS = objConn.Execute(sql)<br>%><br>New Appointments = <%=RS("ApptNew_Count")%><br><% objConn.Close %><br></BODY><br></HTML> <br><br> Well as you can see this way to COUNT records is a lot different from the previous example as we do <br>not run the LOOP through the database. And the best part of using the COUNT Parameter is that this will <br>drastically speed up you SQL Query and provide information to your users a lot quicker. <br><br> <br> <br><br>__________________<br><font color=red>真实源于生活！ </font><br>请访问我们的网站： <br>（VB爱好者乐园） <br><a href="http://www.vbgood.com" target=_blank>http://www.vbgood.com</a><br><a href="http://www.d1vb.com" target=_blank>http://www.d1vb.com</a><br><a href="http://61.128.97.225/vbgood/index.asp" target=_blank>http://61.128.97.225/vbgood/index.asp</a><br>拥有1800多个资料！ </p></td>
</tr>
</table>
<p>
<CENTER><a href="http://www.jsp001.com/forum/newreply.php?action=newreply&threadid=451">点这里对该文章发表评论</a></CENTER>
<p>该文章总得分是 <font color=red>0</font> 分，你认为它对你有帮助吗？
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=451&intVote=4","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>非常多</a>](<font color=red>0</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=451&intVote=2","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>有一些</a>](<font color=red>0</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=451&intVote=1","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>无帮助</a>](<font color=red>0</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=451&intVote=-1","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>是灌水</a>](<font color=red>0</font>) </p>
<script language="javascript" src="http://www.jsp001.com/include/read_thread_script.php?threadid=451"></script>
<p><CENTER>
Copyright © 2001 - 2009 JSP001.com . All Rights Reserved <P>
<IMG SRC="../image/jsp001_small_logo.gif" WIDTH="85" HEIGHT="30" BORDER=0 ALT="">
</CENTER></p>
</body>
</html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -