關于sql語句改寫?

csdn2003forchen 2003-12-12 04:25:05
html頁面
<form action="search.asp" method="post">
<tr >
<td colspan="2" align="center"> <input type=text size=12 name="keyword">
<br>
<select name="AreaSearch">
<option value=1>標題</option>
<option value=2>內容</option>

</select>
<br>
<select name="DateSearch">
<option value=all>所有時間</option>
<option value=1>昨天以來</option>
<option value=3>3 天以來</option>
<option value=7>7 天以來</option>
<option value=15>15天以來</option>
<option value=30>30天以來</option>
<option value=60>60天以來</option>
<option value=90>90天以來</option>

</select><br>
<input type=submit value="搜索" name="submit">
</td>
</tr>
</form>

select.asp頁面
<%
keyword=trim(request("keyword"))
dim searchday
if request("DateSearch")="all" then
searchDay=""
else
searchDay=" DATEDIFF('d',addtime,Now()) < "&request("DateSearch")&" and "
end if

dim pagecount,page,Spage,Epage,recordcount,i
page = request.querystring("page")
if page = "" or IsNull(page) then page = 1
if not IsNumeric(page) then page = 1
page = Cint(page)
set rs = server.CreateObject ("Adodb.recordset")
if request("AreaSearch")=1 then
'''''改寫下面這一句'''''
sql="select * from article where "& searchday &" (" & translate(keyword,"title") & ") order by id desc"
elseif request("AreaSearch")=2 then
sql="select * from article where "& searchday &" (" & translate(keyword,"content") & ") order by id desc"
''''''''''''
end if %>
改為''''''''
sql="select* from aticle where searchday=""and title="keyword" order by id desc"
elseif request("Areaserch")=2 then
sql="select* from article where searchday=""and content="keyword" order by
id desc"
對嗎
...全文
75 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
csdn2003forchen 2003-12-12
  • 打赏
  • 举报
回复
re.Pattern="(\[EMAIL=(\S+\@.[^\[]*)\])(.[^\[]*)(\[\/EMAIL\])"
strContent= re.Replace(strContent,"<img align=absmiddle src=../img/email1.gif><A HREF=""mailto:$2"" TARGET=_blank>$3</A>")
re.Pattern = "^(http://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "(http://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)$"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "[^>=""](http://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "^(ftp://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "(ftp://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)$"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "[^>=""](ftp://[A-Za-z0-9\.\/=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "^(rtsp://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "(rtsp://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)$"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "[^>=""](rtsp://[A-Za-z0-9\.\/=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "^(mms://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "(mms://[A-Za-z0-9\./=\?%\-&_~`@':+!]+)$"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")
re.Pattern = "[^>=""](mms://[A-Za-z0-9\.\/=\?%\-&_~`@':+!]+)"
strContent = re.Replace(strContent,"<img align=absmiddle src=../img/url.gif><a target=_blank href=$1>$1</a>")

if strIcons = "1" then
re.Pattern="(\[em(.[^\[]*)\])"
strContent=re.Replace(strContent,"<img src=img/em$2.gif border=0 align=middle>")
end if
re.Pattern="(\[HTML\])(.[^\[]*)(\[\/HTML\])"
strContent=re.Replace(strContent,"<table width='100%' border='0' cellspacing='0' cellpadding='6' bgcolor='"&abgcolor&"'><td><b>以下内容为程序代码:</b><br>$2</td></table>")
re.Pattern="(\[code\])(.[^\[]*)(\[\/code\])"
strContent=re.Replace(strContent,"<table width='100%' border='0' cellspacing='0' cellpadding='6' bgcolor='"&abgcolor&"'><td><b>以下内容为程序代码:</b><br>$2</td></table>")
re.Pattern="(\[color=(.[^\[]*)\])(.[^\[]*)(\[\/color\])"
strContent=re.Replace(strContent,"<font color=$2>$3</font>")
re.Pattern="(\[face=(.[^\[]*)\])(.[^\[]*)(\[\/face\])"
strContent=re.Replace(strContent,"<font face=$2>$3</font>")
re.Pattern="(\[align=(.[^\[]*)\])(.*)(\[\/align\])"
strContent=re.Replace(strContent,"<div align=$2>$3</div>")
re.Pattern="(\[QUOTE\])(.*)(\[\/QUOTE\])"
strContent=re.Replace(strContent,"<table cellpadding=0 cellspacing=0 border=0 WIDTH=94% bgcolor=#000000 align=center><tr><td><table width=100% cellpadding=5 cellspacing=1 border=0><TR><TD BGCOLOR='"&abgcolor&"'>$2</table></table><br>")
re.Pattern="(\[fly\])(.*)(\[\/fly\])"
strContent=re.Replace(strContent,"<marquee width=90% behavior=alternate scrollamount=3>$2</marquee>")
re.Pattern="(\[move\])(.*)(\[\/move\])"
strContent=re.Replace(strContent,"<MARQUEE scrollamount=3>$2</marquee>")
re.Pattern="\[GLOW=*([0-9]*),*(#*[a-z0-9]*),*([0-9]*)\](.[^\[]*)\[\/GLOW]"
strContent=re.Replace(strContent,"<table width=$1 style=""filter:glow(color=$2, strength=$3)"">$4</table>")
re.Pattern="\[SHADOW=*([0-9]*),*(#*[a-z0-9]*),*([0-9]*)\](.[^\[]*)\[\/SHADOW]"
strContent=re.Replace(strContent,"<table width=$1 style=""filter:shadow(color=$2, strength=$3)"">$4</table>")
re.Pattern="(\[i\])(.[^\[]*)(\[\/i\])"
strContent=re.Replace(strContent,"<i>$2</i>")
re.Pattern="(\[u\])(.[^\[]*)(\[\/u\])"
strContent=re.Replace(strContent,"<u>$2</u>")
re.Pattern="(\[b\])(.[^\[]*)(\[\/b\])"
strContent=re.Replace(strContent,"<b>$2</b>")
re.Pattern="(\[fly\])(.[^\[]*)(\[\/fly\])"
strContent=re.Replace(strContent,"<marquee>$2</marquee>")
re.Pattern="(\[size=1\])(.[^\[]*)(\[\/size\])"
strContent=re.Replace(strContent,"<font size=1>$2</font>")
re.Pattern="(\[size=2\])(.[^\[]*)(\[\/size\])"
strContent=re.Replace(strContent,"<font size=2>$2</font>")
re.Pattern="(\[size=3\])(.[^\[]*)(\[\/size\])"
strContent=re.Replace(strContent,"<font size=3>$2</font>")
re.Pattern="(\[size=4\])(.[^\[]*)(\[\/size\])"
strContent=re.Replace(strContent,"<font size=4>$2</font>")
re.Pattern="(\[center\])(.[^\[]*)(\[\/center\])"
strContent=re.Replace(strContent,"<center>$2</center>")
strContent=ChkBadWords(strContent)
set re=Nothing
UBBCode=strContent
end function
public function translate(sourceStr,fieldStr)
rem 处理逻辑表达式的转化问题
dim sourceList
dim resultStr
dim i,j
if instr(sourceStr," ")>0 then
dim isOperator
isOperator = true
sourceList=split(sourceStr)
'--------------------------------------------------------
rem Response.Write "num:" & cstr(ubound(sourceList)) & "<br>"
for i = 0 to ubound(sourceList)
rem Response.Write i
Select Case ucase(sourceList(i))
Case "AND","&","和","与"
resultStr=resultStr & " and "
isOperator = true
Case "OR","|","或"
resultStr=resultStr & " or "
isOperator = true
Case "NOT","!","非","!","!"
resultStr=resultStr & " not "
isOperator = true
Case "(","(","("
resultStr=resultStr & " ( "
isOperator = true
csdn2003forchen 2003-12-12
  • 打赏
  • 举报
回复
to a_zhe_20(兜兜裤) 里面有一个<!--#include file='char.asp'-->
<%
Function chkemail(strEmailAddr)
Dim re
Set re = new RegExp
re.pattern = "^[a-zA-Z][A-Za-z0-9_.-]+@[a-zA-Z0-9_]+?\.[a-zA-Z]{2,3}$"
chkemail=re.Test(strEmailAddr)
end function
Function chkoicq(oicq)
Dim re1
Set re1 = new RegExp
re1.IgnoreCase = false
re1.global = false
re1.Pattern = "[0-9]{4,9}$"
chkoicq = re1.Test(oicq)
End Function

function DateToStr(dtDateTime)
DateToStr = year(dtDateTime) & doublenum(Month(dtdateTime)) & doublenum(Day(dtdateTime)) & doublenum(Hour(dtdateTime)) & doublenum(Minute(dtdateTime)) & doublenum(Second(dtdateTime)) & ""
end function
function doublenum(fNum)
if fNum > 9 then
doublenum = fNum
else
doublenum = "0" & fNum
end if
end function

rem ------------ubb代码
function ChkBadWords(fString)
if not(isnull(BadWords) or isnull(fString)) then
bwords = split(BadWords, "|")
for i = 0 to ubound(bwords)
fString = Replace(fString, bwords(i), string(len(bwords(i)),"*"))
next
ChkBadWords = fString
end if
end function

function HTMLEncode(fString)
if not isnull(fString) then
fString = replace(fString, ">", ">")
fString = replace(fString, "<", "<")

fString = Replace(fString, CHR(32), " ")
fString = Replace(fString, CHR(34), """)
fString = Replace(fString, CHR(39), "'")
fString = Replace(fString, CHR(13), "")
fString = Replace(fString, CHR(10) & CHR(10), "</P><P> ")
fString = Replace(fString, CHR(10), "<BR> ")
HTMLEncode = fString
end if
end function

function HTMLcode(fString)
if not isnull(fString) then
fString = Replace(fString, CHR(13), "")
fString = Replace(fString, CHR(10) & CHR(10), "</P><P>")
fString = Replace(fString, CHR(10), "<BR>")
HTMLcode = fString
end if
end function
function HTMLDecode(fString)
if not isnull(fString) then
fString = replace(fString, ">", ">")
fString = replace(fString, "<", "<")

fString = Replace(fString, "", CHR(13))
fString = Replace(fString, "</P><P>", CHR(10) & CHR(10))
fString = Replace(fString, "<BR>", CHR(10))
HTMLDecode = fString
end if
end function

function UBBCode(strContent)
if strAllowHTML <> 1 then
strContent = HTMLEncode(strContent)
else
strContent = HTMLcode(strContent)
end if
dim re
Set re=new RegExp
re.IgnoreCase =true
re.Global=True

if strIMGInPosts = "" then
re.Pattern="(\[IMG\])(.[^\[]*)(\[\/IMG\])"
strContent=re.Replace(strContent,"<a href=""$2"" target=_blank><IMG SRC=""$2"" border=0 alt=按此在新窗口浏览图片 onload=""javascript:if(this.width>screen.width-333)this.width=screen.width-333""></a>")
end if

re.Pattern="\[DIR=*([0-9]*),*([0-9]*)\](.[^\[]*)\[\/DIR]"
strContent=re.Replace(strContent,"<object classid=clsid:166B1BCA-3F9C-11CF-8075-444553540000 codebase=http://download.macromedia.com/pub/shockwave/cabs/director/sw.cab#version=7,0,2,0 width=$1 height=$2><param name=src value=$3><embed src=$3 pluginspage=http://www.macromedia.com/shockwave/download/ width=$1 height=$2></embed></object>")
re.Pattern="\[QT=*([0-9]*),*([0-9]*)\](.[^\[]*)\[\/QT]"
strContent=re.Replace(strContent,"<embed src=$3 width=$1 height=$2 autoplay=true loop=false controller=true playeveryframe=false cache=false scale=TOFIT bgcolor=#000000 kioskmode=false targetcache=false pluginspage=http://www.apple.com/quicktime/>")
re.Pattern="\[MP=*([0-9]*),*([0-9]*)\](.[^\[]*)\[\/MP]"
strContent=re.Replace(strContent,"<object align=middle classid=CLSID:22d6f312-b0f6-11d0-94ab-0080c74c7e95 class=OBJECT id=MediaPlayer width=$1 height=$2 ><param name=ShowStatusBar value=-1><param name=Filename value=$3><embed type=application/x-oleobject codebase=http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf.cab#Version=5,1,52,701 flename=mp src=$3 width=$1 height=$2></embed></object>")
re.Pattern="\[RM=*([0-9]*),*([0-9]*)\](.[^\[]*)\[\/RM]"
strContent=re.Replace(strContent,"<OBJECT classid=clsid:CFCDAA03-8BE4-11cf-B84B-0020AFBBCCFA class=OBJECT id=RAOCX width=$1 height=$2><PARAM NAME=SRC VALUE=$3><PARAM NAME=CONSOLE VALUE=Clip1><PARAM NAME=CONTROLS VALUE=imagewindow><PARAM NAME=AUTOSTART VALUE=true></OBJECT><br><OBJECT classid=CLSID:CFCDAA03-8BE4-11CF-B84B-0020AFBBCCFA height=32 id=video2 width=$1><PARAM NAME=SRC VALUE=$3><PARAM NAME=AUTOSTART VALUE=-1><PARAM NAME=CONTROLS VALUE=controlpanel><PARAM NAME=CONSOLE VALUE=Clip1></OBJECT>")

if strflash= "" then
re.Pattern="(\[FLASH\])(.[^\[]*)(\[\/FLASH\])"
strContent= re.Replace(strContent,"<OBJECT codeBase=http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=4,0,2,0 classid=clsid:D27CDB6E-AE6D-11cf-96B8-444553540000 width=500 height=400><PARAM NAME=movie VALUE=""$2""><PARAM NAME=quality VALUE=high><embed src=""$2"" quality=high pluginspage='http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash' type='application/x-shockwave-flash' width=500 height=400>$2</embed></OBJECT>")
end if

re.Pattern="(\[ZIP\])(.[^\[]*)(\[\/ZIP\])"
strContent=re.Replace(strContent,"<br><a href=""$2"">点击下载该文件</a>")
re.Pattern="(\[RAR\])(.[^\[]*)(\[\/RAR\])"
strContent=re.Replace(strContent,"<br><a href=""$2"">点击下载该文件</a>")
re.Pattern="(\[UPLOAD=(.[^\[]*)\])(.[^\[]*)(\[\/UPLOAD\])"
strContent= re.Replace(strContent,"<br><IMG SRC=""../img/$3"" border=0 onload=""javascript:if(this.width>screen.width-333)this.width=screen.width-333"">")

re.Pattern="(\[URL\])(http:\/\/.[^\[]*)(\[\/URL\])"
strContent= re.Replace(strContent,"<A HREF=""$2"" TARGET=_blank>$2</A>")
re.Pattern="(\[URL\])(.[^\[]*)(\[\/URL\])"
strContent= re.Replace(strContent,"<A HREF=""http://$2"" TARGET=_blank>$2</A>")
csdn2003forchen 2003-12-12
  • 打赏
  • 举报
回复
回复人: xiaoyuehen(怎么可能,原来爱情可以那么狠)
出现这种错误
Microsoft JET Database Engine 错误 '80040e14'

SELECT 子句中包含一个保留字、拼写错误或丢失的参数,或标点符号不正确。
a_zhe_20 2003-12-12
  • 打赏
  • 举报
回复
这个页面没有包含<!--#include file='...'-->其他页面吗?

translate应该是个函数,转换条件的。
cdsun 2003-12-12
  • 打赏
  • 举报
回复
回家再看
csdn2003forchen 2003-12-12
  • 打赏
  • 举报
回复
translate这个词的作用是什么?
xiaoyuehen 2003-12-12
  • 打赏
  • 举报
回复
改为
sql="select* from aticle where " & searchday & " title=" & keyword & " order by id desc"
elseif request("Areaserch")=2 then
sql="select* from article where " & searchday & "content=" & keyword & " order by
id desc"

试试。。。。
shleo 2003-12-12
  • 打赏
  • 举报
回复
新代码错了
sql="select* from aticle where searchday='' and title='"&keyword&"' order by id desc"

你源代码从哪弄来的啊
我没看懂
61 2003-12-12
  • 打赏
  • 举报
回复
可以呀,不过你的searchday条件要有值,不能为空
angelheavens 2003-12-12
  • 打赏
  • 举报
回复
你测试一下不就知道了吗?
更优更快 人工智能自动SQL优化----------http://www.sina.com.cn 2001/12/12 17:48 中国电脑教育报文/SQL爱好者  所谓SQL,就是指Structured Query Language(结构化查询语言),它是目前使用最广泛的数据库语言,用来和数据库打交道,从数据库中得到用户需要的数据。但是要想熟练使用SQL语句,也不是一件简单的事,有些语句使用起来也比较麻烦。如果我们对SQL语句进行优化,那么用户使用起来 就会方便许多。  简单来说,SQL语句的优化就是将性能低下的SQL语句转换成达到同样目的的性能优异的SQL语句。人工智能自动SQL优化就是使用人工智能技术,自动对SQL语句进行重写,找到性能最好的等效SQL语句。  人工智能自动SQL 优化  随着人工智能技术的发展和在数据库优化领域应用的深入,在20世纪90年代末终于出现了突破性的进展——人工智能自动SQL优化。目前在商用数据库领域LECCO TechnologyLimited(灵高公司)拥有该技术并提供使用该技术的自动优化产品——LECCO SQL Expert,其支持Oracle、Sybase、MS SQLServer和IBMDB2数据库平台。该产品针对数据库应用的开发和维护阶段提供了几个特别的模块:SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。图1 人工智能自动SQL优化示意图  其核心模块之一“SQL语法优化器”的工作原理大致如下(如图1):  一条源SQL语句输入→“人工智能反馈式搜索引擎”对输入的SQL语句结合检测到的数据库结构和索引进行重写,产生N条等效的SQL语句输出→产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写,直至无法产生新的输出或搜索限额满→对 输出的SQL语句进行过滤,选出具有不同执行计划的SQL语句(即不同的执行效率)→对得到的SQL语句进行批量测试,找出性能最好的SQL语句。图2 优化前的SQL语句  自动优化实例  假设我们从源代码中抽取出这条SQL语句(如图2):  SELECTCOUNT(*)FROMEMPLOY-EE WHEREEXISTS(SELECT'X'FROM DEPARTMENTswheresEMP_DEPT=DPT_IDAND DPT_NAME LIKE'AC%')AND EMP_IDIN(SELECT SAL_EMP_IDFROM EMP_SAL_HISTB WHERESAL_SALARY>70000)   按“优化”按钮后,经过十几秒,SQL Expert就完成了优化的过程,从优化细节中可以看到,它在十几秒的时间内重写产生了2267条等价的SQL语句,其中136条SQL语句有不同的执行计划(如图3)。图3 优化结果  接下来我们可以对自动重写产生的136条具有不同执行计划的SQL语句进行批运行测试,以选出性能最佳的等效SQL语句。按下“批运行”按钮,在“终止条件”页选择“最佳运行时间SQL语句”(如图4),按“确定”。图4 测试条件  经过几分钟的测试运行后,我们可以发现SQL124的运行时间和反应时间最短。运行速度约有22.75倍的提升(源SQL语句运行时间为2.73秒,SQL124运行时间为0.12秒,如图5)。图5 测试结果  我们把SQL124放入源代码中,结束一条SQL语句的优化工作。从上例可以看到,LECCO SQL Expert的自动重写技术使原来需要几小时才能完成的SQL语句的优化工作,缩减到几分钟之内就可以完成。数据库管理员和开发人员可以从繁重的SQL语句优化工作中解脱出来。  边做边学式训练  LECCO SQL Expert不仅能够找到最佳的SQL语句,而且提供的“边做边学式训练”还能够教会开发人员和数据库管理员如何写出性能最好的SQL语句。LECCO SQL Expert的“SQL比较器”可以标明源SQL和待选SQL之间的不同之处。LECCO SQL Expert详尽的上下文敏感帮助系统可以指出执行计划的深层含义。图6 源语句与SQL124的比较  以上面优化的结果为例,为了查看源SQL语句和SQL124在写法上的不同,我们可以按下“比较器”按钮,对SQL124和源SQL语句进行比较。如果选择“双向比较”复选框,“SQL比较器”可以将两条互相间的不同之处以蓝色表示。当然,你也可以从 源语句和重写后的SQL语句中任选两条进行比较(如图6)。  从比较的结果可以看到,重写得到的SQL124把第一个Exists改写成了In;在字段DPT_ID上进行了合并空字符串的操作以诱导数据库先执行子查询中的(SELECTDPT_ID||'FROMDEPART-MENTWH

28,409

社区成员

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧