SQL选择查询帮助 [英] SQL Select Query help
问题描述
试图从数据库中检索邮政编码，但只希望查询在尝试使用的代码的前3位数看起来是
（LEFT（dbo.booking_form.COLL_POST_CODE），3）但这不起作用。我不希望
希望查询计算单个邮政编码，而是查看前3个数字中找到的区域
，例如HA0 3TD适用于特定的房屋，但HA）
适用于Harrow区域。
SELECT Count（dbo.booking_form.COLL_POST_CODE）AS CountOfCOLL_POST_CODE，
COLL_POST_CODE FROM dbo.booking_form GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POST_CODE DESC"
问候
Simon Gare
Gare Group Limited
网站： www.thegaregroup.co.uk
网站： www.privatehiresolutions.co.uk
Simon Gare于2007年1月5日在
microsoft上写道.public.inetserver.asp.general：
试图从数据库中检索邮政编码但只想要查询
查看使用
（LEF）尝试的代码的前3位数字T（dbo.booking_form.COLL_POST_CODE），3）但这不起作用。我不想让查询计算个别邮政编码，而是在前3位数字区域看看
，例如： HA0 3TD适用于特定房屋，但HA）适用于Harrow区域。
SELECT Count（dbo.booking_form.COLL_POST_CODE）AS
CountOfCOLL_POST_CODE，COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
你应该提到用于正确答案的db-engine。
我在Jet引擎中使用它： br />
SQL =" SELECT left（postcode，3）as pc，count（pc）as tal" & _
" FROM myTbl GROUP BY left（postcode，3）"
''''''response.write SQL&"< hr>"
set mDATA = CONNECT.Execute（SQL）
Response.Write"< table border = 1>< tr>" &安培; vbcrlf
Do mDATA.Eof
tal = mDATA（" tal"）
pc = mDATA（" pc"）
如果pc =""然后pc =没有邮政编码： else pc =" Postcode：" &安培;电脑和电脑""
Response.Write"< td>"& pc&"< td>< b>"& tal&"< / b> ;< TR>" &安培; vbcrlf
mDATA.MoveNext
循环
Response.Write"< / table>" &安培; vbcrlf
-
Evertjan。
荷兰。
（请更改x' 我的电子邮件地址中的点数
谢谢Evertjan，虽然有问题但你可以看下面并建议。
< ;％
Dim AreaColl
Dim AreaColl_numRows
设置AreaColl = Server.CreateObject（" ADODB.Recordset"）
AreaColl.ActiveConnection = MM_TobiasNET_STRING
AreaColl.Source =" SELECT（左（Count（dbo.booking_form.COLL_POST_CODE），3））
AS CountOfCOLL_POST_CODE，COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open（）
AreaColl_numRows = 0
％> ;
和身体
< td colspan = 2>十大收藏后合作des< / td>
< / tr>
<％
While（（Repeat1__numRows< 0）AND（不是AreaColl。 EOF））
％>
< tr>
< td width =" 100"
class =" DataSetText"><％=（AreaColl.Fields.Item（" COLL _POST_CODE"）。。Value）％>< / td>
< td width = " 790"
class =" DataSetText"><％=（AreaColl.Fields.Item（" Coun tOfCOLL_POST_CODE"）。。Value）
％> ;< / td>
< / tr>
<％
Repeat1__index = Repeat1__index + 1
Repeat1__numRows = Repeat1__numRows-1
AreaColl.MoveNext（）
Wend
％>
提前致谢
Evertjan。 < ex ************** @ interxnl.netwrote in message
news：Xn **************** **** @ 194.109.133.242 ...
Simon Gare于2007年1月5日在
microsoft.public.inetserver.asp上写道.general：
尝试从数据库中检索邮政编码，但只想查询
查看使用
（LEFT（dbo.booking_form.COLL_POST_CODE），3）尝试的代码的前3位数字，但这不起作用。我不想让查询计算个别邮政编码，而是在前3位数字区域看看
，例如： HA0 3TD适用于特定房屋，但HA）适用于Harrow区域。
SELECT Count（dbo.booking_form.COLL_POST_CODE）AS
CountOfCOLL_POST_CODE，COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
您应该提到用于正确答案的db-engine。
我在Jet引擎中使用它：
SQL =" SELECT left（postcode，3）as pc，count（pc）as tal" & _
" FROM myTbl GROUP BY left（postcode，3）"
''''''response.write SQL&"< hr>"
set mDATA = CONNECT.Execute（SQL）
Response.Write"< table border = 1>< tr>" &安培; vbcrlf
Do mDATA.Eof
tal = mDATA（" tal"）
pc = mDATA（" pc"）
如果pc =""然后pc =没有邮政编码： else pc =" Postcode：" &安培;电脑和电脑""
Response.Write"< td>"& pc&"< td>< b>"& tal&"< / b> ;< TR>" &安培; vbcrlf
mDATA.MoveNext
循环
Response.Write"< / table>" &安培; vbcrlf
-
Evertjan。
荷兰。
（请更改x' 我的电子邮件地址中的点数
Simon Gare于2007年1月5日在
microsoft.public上写道.inetserver.asp.general：
" Evertjan。" < ex ************** @ interxnl.netwrote in message
news：Xn **************** **** @ 194.109.133.242 ...
> Simon Gare于2007年1月5日在
microsoft.public.inetserver.asp.general上写道：
SQL =" SELECT left（postcode，3）as pc，count（pc）as tal" & _
" FROM myTbl GROUP BY left（postcode，3）"
[请不要在usenet上使用]
谢谢Evertjan，遇到问题虽然你能看到下面和
建议。
设置AreaColl = Server.CreateObject（" ADODB.Recordset"）
<％=（AreaColl.Fields.Item（" CountOfCOLL_POST_CODE"）。。Value）％>
我从不使用Recordset [你可以轻松地使用它]
你所展示的代码对我来说很复杂所有那些长的
名字有多个_和'和不必要的（）'。
....有问题虽然你能看到下面和
建议。
如果你可以自己测试你的代码，从最小的和最可靠的
可读代码开始提出问题，也许你甚至可以来用
来解释你遇到的问题，Simon。
顺便说一下，我的代码是否适合你？
-
Evertjan。
荷兰。
（请将x''es更改为我的电子邮件地址中的点数）
Hi,
trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn''t work. I don''t
want the query to count individual post codes but instead look at an area
found in the first 3 digits e.g. HA0 3TD is for a particular house but HA)
is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS CountOfCOLL_POST_CODE,
COLL_POST_CODE FROM dbo.booking_form GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POST_CODE DESC"
Regards
Simon Gare
The Gare Group Limited
website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
Hi,
trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn''t work. I
don''t want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"You should mention the db-engine used for a correct answer.
I use this with the Jet engine:
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
''''''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
--
Evertjan.
The Netherlands.
(Please change the x''es to dots in my emailaddress)
Thanks Evertjan, having a problem though could you look below and suggest.
<%
Dim AreaColl
Dim AreaColl_numRows
Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()
AreaColl_numRows = 0
%>
and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>
Thanks in advance
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@194.109.133.242...Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
Hi,
trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn''t work. I
don''t want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
You should mention the db-engine used for a correct answer.
I use this with the Jet engine:
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
''''''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
--
Evertjan.
The Netherlands.
(Please change the x''es to dots in my emailaddress)
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@194.109.133.242...>Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
[Please do not toppost on usenet]
Thanks Evertjan, having a problem though could you look below and
suggest.
Set AreaColl = Server.CreateObject("ADODB.Recordset")
<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE"). Value)%>I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those long
names with multiple _''s and unnecessary ()''s.
.... having a problem though could you look below and
suggest.If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.
btw, did my code work with you?
--
Evertjan.
The Netherlands.
(Please change the x''es to dots in my emailaddress)
这篇关于SQL选择查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
