asp 中这样的查询 SQL怎么写?

[bnabinge]

Select Top 4 ID,NewsID,ClassID,NewsTitle,CurtTitle,NewsNaviContent,URLAddress,Keywords,NewsSmallPicFile,Hits,addtime From FS_NS_News Where Mid(NewsProperty,21,1) = 1 and ClassID in('6E0J8829E009G1A','D05H162ABHGF65B','1B48JEDDKHI8A5H') and 这里不怎么怎么写进去 Order By addtime Desc


在上面的红色部分我是想判断 NewsTitle字段中是否含有:'昆明','春城','七彩云南','大观','安宁温泉','云南民族村','滇池','安宁','呈贡','富民','晋宁','禄劝','石林','寻甸','宜良','嵩明','九乡 ','斗南花卉','轿子雪山','世博园','玉龙湾','金马碧鸡坊','东川红土地','西山','翠湖','阳宗海','金殿','筇竹寺','长湖','圆通寺','岩泉寺','马蹄河','盘龙寺','大叠水瀑布','炼象关','昙华寺','近日楼','东风广场','巫家坝','黑龙潭'

我想用最简化的SQL来实现。但不会。哪位能帮一下？

[bnabinge]

charindex
这函数不会用啊。能用在我这上面不

[月光下的土豆]

dim str
str="'昆明','春城','七彩云南','大观','安宁温泉','云南民族村','滇池','安宁','呈贡','富民','晋宁','禄劝','石林','寻甸','宜良','嵩明','九乡 ','斗南花卉','轿子雪山','世博园','玉龙湾','金马碧鸡坊','东川红土地','西山','翠湖','阳宗海','金殿','筇竹寺','长湖','圆通寺','岩泉寺','马蹄河','盘龙寺','大叠水瀑布','炼象关','昙华寺','近日楼','东风广场','巫家坝','黑龙潭'"
charindex(str,NewsTitle)>0

[yan11cn]

<%
dim sql,str
str="'昆明','春城','七彩云南','大观','安宁温泉','云南民族村','滇池','安宁','呈贡','富民','晋宁','禄劝','石林','寻甸','宜良','嵩明','九乡 ','斗南花卉','轿子雪山','世博园','玉龙湾','金马碧鸡坊','东川红土地','西山','翠湖','阳宗海','金殿','筇竹寺','长湖','圆通寺','岩泉寺','马蹄河','盘龙寺','大叠水瀑布','炼象关','昙华寺','近日楼','东风广场','巫家坝','黑龙潭'"
sql="Select Top 4 ID,NewsID,ClassID,NewsTitle,CurtTitle,NewsNaviContent,URLAddress,Keywords,NewsSmallPicFile,Hits,addtime From FS_NS_News Where Mid(NewsProperty,21,1) = 1 and ClassID in ('6E0J8829E009G1A','D05H162ABHGF65B','1B48JEDDKHI8A5H') and (" & str & ") Order By addtime Desc "
set rs=conn.execute(sql)
%>

[yan11cn]

修正下：

<%
dim sql,str
str="'昆明','春城','七彩云南','大观','安宁温泉','云南民族村','滇池','安宁','呈贡','富民','晋宁','禄劝','石林','寻甸','宜良','嵩明','九乡 ','斗南花卉','轿子雪山','世博园','玉龙湾','金马碧鸡坊','东川红土地','西山','翠湖','阳宗海','金殿','筇竹寺','长湖','圆通寺','岩泉寺','马蹄河','盘龙寺','大叠水瀑布','炼象关','昙华寺','近日楼','东风广场','巫家坝','黑龙潭'"
sql="Select Top 4 ID,NewsID,ClassID,NewsTitle,CurtTitle,NewsNaviContent,URLAddress,Keywords,NewsSmallPicFile,Hits,addtime From FS_NS_News Where Mid(NewsProperty,21,1) = 1 and ClassID in ('6E0J8829E009G1A','D05H162ABHGF65B','1B48JEDDKHI8A5H') and NewsTitle in (" & str & ") Order By addtime Desc "
set rs=conn.execute(sql)
%>

[bnabinge]

怕不是这样写啊
yan11cn 大侠
比如:NewsTitle 的内容为 "昆明旅游商务有限公司" 那么在用IN时就判断不出来
因为条件中就没有和"昆明旅游商务有限公司"匹配的。我觉得是用LIKE 但条件实在是太多。想问问有没有可以简化实用的方法

[yan11cn]

你这样的确实比较麻烦 我感觉还是别写成一个查询语句了
把这个条件去掉后查询数据库
然后在返回记录集之后在asp代码中循环判断 比较合适

[bnabinge]

Select Top 4 ID,NewsID,ClassID,NewsTitle,CurtTitle,NewsNaviContent,URLAddress,Keywords,NewsSmallPicFile,Hits,addtime From FS_NS_News Where Mid(NewsProperty,21,1) = 1 and ClassID in('6E0J8829E009G1A%'or NewsTitle like '%D05H162ABHGF65B%'or NewsTitle like '%1B48JEDDKHI8A5H') and NewsTitle like '%昆明%' or NewsTitle like '%春城%'or NewsTitle like '%七彩云南%'or NewsTitle like '%大观%'or NewsTitle like '%安宁温泉%'or NewsTitle like '%云南民族村%'or NewsTitle like '%滇池%'or NewsTitle like '%安宁%'or NewsTitle like '%呈贡%'or NewsTitle like '%富民%'or NewsTitle like '%晋宁%'or NewsTitle like '%禄劝%'or NewsTitle like '%石林%'or NewsTitle like '%寻甸%'or NewsTitle like '%宜良%'or NewsTitle like '%嵩明%'or NewsTitle like '%九乡 %'or NewsTitle like '%斗南花卉%'or NewsTitle like '%轿子雪山%'or NewsTitle like '%世博园%'or NewsTitle like '%玉龙湾%'or NewsTitle like '%金马碧鸡坊%'or NewsTitle like '%东川红土地%'or NewsTitle like '%西山%'or NewsTitle like '%翠湖%'or NewsTitle like '%阳宗海%'or NewsTitle like '%金殿%'or NewsTitle like '%筇竹寺%'or NewsTitle like '%长湖%'or NewsTitle like '%圆通寺%'or NewsTitle like '%岩泉寺%'or NewsTitle like '%马蹄河%'or NewsTitle like '%盘龙寺%'or NewsTitle like '%大叠水瀑布%'or NewsTitle like '%炼象关%'or NewsTitle like '%昙华寺%'or NewsTitle like '%近日楼%'or NewsTitle like '%东风广场%'or NewsTitle like '%巫家坝%'or NewsTitle like '%黑龙潭%' Order By addtime Desc
其实就是相问问这个的模糊部分能不能简化

[月光下的土豆]

baidu SQL charindex

[bnabinge]

晕。一替换就整错了。呵呵
补充下
Select Top 4 ID,NewsID,ClassID,NewsTitle,CurtTitle,NewsNaviContent,URLAddress,Keywords,NewsSmallPicFile,Hits,addtime From FS_NS_News Where Mid(NewsProperty,21,1) = 1 and ClassID in('6E0J8829E009G1A','05H162ABHGF65B','1B48JEDDKHI8A5H') and (NewsTitle like '%昆明%' or NewsTitle like '%春城%'or NewsTitle like '%七彩云南%'or NewsTitle like '%大观%'or NewsTitle like '%安宁温泉%'or NewsTitle like '%云南民族村%'or NewsTitle like '%滇池%'or NewsTitle like '%安宁%'or NewsTitle like '%呈贡%'or NewsTitle like '%富民%'or NewsTitle like '%晋宁%'or NewsTitle like '%禄劝%'or NewsTitle like '%石林%'or NewsTitle like '%寻甸%'or NewsTitle like '%宜良%'or NewsTitle like '%嵩明%'or NewsTitle like '%九乡 %'or NewsTitle like '%斗南花卉%'or NewsTitle like '%轿子雪山%'or NewsTitle like '%世博园%'or NewsTitle like '%玉龙湾%'or NewsTitle like '%金马碧鸡坊%'or NewsTitle like '%东川红土地%'or NewsTitle like '%西山%'or NewsTitle like '%翠湖%'or NewsTitle like '%阳宗海%'or NewsTitle like '%金殿%'or NewsTitle like '%筇竹寺%'or NewsTitle like '%长湖%'or NewsTitle like '%圆通寺%'or NewsTitle like '%岩泉寺%'or NewsTitle like '%马蹄河%'or NewsTitle like '%盘龙寺%'or NewsTitle like '%大叠水瀑布%'or NewsTitle like '%炼象关%'or NewsTitle like '%昙华寺%'or NewsTitle like '%近日楼%'or NewsTitle like '%东风广场%'or NewsTitle like '%巫家坝%'or NewsTitle like '%黑龙潭%') Order By addtime Desc