◆求一SQL语句

Question

测试平台为:Asp+sqlserver表一productsort:
id    englishname
10       手机
11       电脑表二product：
id   productsort  name   machines1  machines2  
101      10       A-001     1;        1;
102      10       A-002     1;2;      1;
103      11       A-003     3;        3;
104      11       A-004     4;        3;4;表三original：
id  srotid  machines1   machines2
1     10     aaa;bbb;    ccc;ddd;
2     10     eee;fff;    ggg;hhh;现在假如输入"aaa"就搜索出以下纪录：(注：下面结果中的machines1、machines2里面所含有的1，就是表三中含有"aaa"的ＩＤ号.)id   name   machines1   machines2  
101  A-001      1;         1;
102  A-002      1;2;       1;select distinct b.*
 from product as b,original as c 
where  
(
( ';'+b.machines1  like '%;'+ltrim(str(c.id))+';%'   or   
  ';'+b.machines2  like '%;'+ltrim(str(c.id))+';%')  and 
( C.machines1 like '%aaa%'or C.machines2 like '%aaa%')
)
以上这条语句可以把关键字"aaa"的记录读出来,但是如果客户输入"手机 aaa"这样的组合关键字,就肯定不能搜索出来,但是公司规定一定要这样做,并下了最后的限期.
郁闷啊,从上个星期搞到现在还没搞定.
要根据关键字,分开空格再用组合搜索,于是我又用了一个把关键字
keywords分开组合的处理,这个处理即是根据关键字的空格来把
关键字分组.如输入关键字"aaa bbb",通过这个处理就会变成
 C.machines1 like '%aaa%' or C.machines1 like '%bbb%'
or C.machines2 like '%aaa%' or C.machines2 like '%bbb%'
*********************************************************
searchdata=replace(keywords,"'","??")
searchdata=Rtrim(LTrim(searchdata))
'sql1="" 
sql2="" 
sql3="" 
searchdatatmp=split(searchdata," ") 
max=ubound(searchdatatmp) 
if max=0 then 
sql2=sql2&" C.machines1 like '%" & searchdatatmp(i) & "%' "
sql3=sql3&" C.machines2 like '%" & searchdatatmp(i) & "%' "
else 
for i=0 to max 
if i=0 then 
sql2=sql2&" (C.machines1 like '%" & searchdatatmp(i) & "%' and "
sql3=sql3&" (C.machines2 like '%" & searchdatatmp(i) & "%' and "
else
if i=max then 
sql2=sql2&" C.machines1 like '%" & searchdatatmp(i) & "%') "
sql3=sql3&" C.machines2 like '%" & searchdatatmp(i) & "%') "
else
sql2=sql2&" C.machines1 like '%" & searchdatatmp(i) & "%' and "
sql3=sql3&" C.machines2 like '%" & searchdatatmp(i) & "%' and "
end if
end if
next 
end if
*********************************************************
所以,我上面的SQL语句改成以下也可以模糊搜索.
但是我这下面的语句只可以把"aaa bbb"即表三machines1,machines2中的
数据搜索出来,如果客户输入"手机 aaa"就不搜索不到了.
select distinct b.*
 from product as b,original as c 
where  
(
( ';'+b.machines1  like '%;'+ltrim(str(c.id))+';%'   or   
  ';'+b.machines2  like '%;'+ltrim(str(c.id))+';%')
 and "  &"("& sql2&" or "&sql3 &"))郁闷啊,希望高手们出来帮忙修改一下..
并且希望执行速度一定要快.
谢谢...期待中！

Answer 1

LZ:我不是已经给你写了么
   你为什么一定要写成 From Product as b,original as c Where b.!!!=c.!!! ..这样的呢？
   它等同于   From 
                  Product as b
              Inner Join
                  original as c
              On b.!!!=c.!!!

Answer 2

SoftwKLC,
如果把关键字加在这下面的循环里面,这个要好长的啊.
 On Charindex(Right(@str,Len(@str)-Charindex(';',@str)+1),';'+Cast(B.machines1 As Varchar(8000)))>0 Or
Charindex(Right(@str,Len(@str)-Charindex(';',@str)+1),';'+Cast(B.machines2 As Varchar(8000)))>0

Answer 3

SoftwKLC
你能不能帮我再精简一下,因为如果用组合的关键字"aaa bbb"来搜索,如:
sql2=sql2&" C.machines1 like '%" & searchdatatmp(i) & "%' "
sql3=sql3&" C.machines2 like '%" & searchdatatmp(i) & "%' "
.....
它可能这么长:
C.machines1 like '%aaa%' or C.machines1 like '%bbb%'
or C.machines2 like '%aaa%' or C.machines2 like '%bbb%'如果用你那个语句,可能就要更长了.
On Charindex(Right('aaa',Len('aaa')-Charindex(';','aaa')+1),';'+Cast(B.machines1 As Varchar(8000)))>0 Or
Charindex(Right('aaa',Len('aaa')-Charindex(';','aaa')+1),';'+Cast(B.machines2 As Varchar(8000)))>0 orOn Charindex(Right('bbb',Len('bbb')-Charindex(';','bbb')+1),';'+Cast(B.machines1 As Varchar(8000)))>0 Or
Charindex(Right('bbb',Len('bbb')-Charindex(';','bbb')+1),';'+Cast(B.machines2 As Varchar(8000)))>0 orOn Charindex(Right('aaa',Len('aaa')-Charindex(';','aaa')+1),';'+Cast(B.machines1 As Varchar(8000)))>0 Or
Charindex(Right('bbb',Len('bbb')-Charindex(';','bbb')+1),';'+Cast(B.machines2 As Varchar(8000)))>0 or
On Charindex(Right('bbb',Len('bbb')-Charindex(';','bbb')+1),';'+Cast(B.machines1 As Varchar(8000)))>0 Or
Charindex(Right('aaa',Len('aaa')-Charindex(';','aaa')+1),';'+Cast(B.machines2 As Varchar(8000)))>0 or这样子我们的语句就会变得好长好长了.所以我就想这循环中的语句尽量简短点.

Answer 4

谁让你拆开的啊?
你直接就把 aaa bbb 整个传进去 然后charindex 就是了 不要再用split在asp里面拆成数组了.
直接传 aaa bbb 进去

Answer 5

---创建测试数据
Create Table productsort(id int,englishname Varchar(10))
 Insert productsort Select 10,       '手机'
    Union All Select 11,       '电脑'
Create Table product(id int,productsort int,name varchar(10),machines1 ntext,machines2 ntext)
 Insert product Select 101,      10,       'A-001',     '1;',        '1;'
       Union All Select 102,      10,       'A-002',     '1;2;',      '1;'
       Union All Select 103,      11,       'A-003',     '3;',        '3;'
       Union All Select 104,      11,       'A-004',     '4;',        '3;4;'
Create Table original(id int,srotid int,machines1 ntext,machines2 ntext)
 Insert original Select 1,     10,     'aaa;bbb;',    'ccc;ddd;'
        Union All Select 2,     10,     'eee;fff;',    'ggg;hhh;'
        Union All Select 3,     11,     'qqq;www;',    'eee;rrr;'  ---加此行
        Union All Select 4,     11,     'zzz;xxx;',    'vvv;mmm;'  ---加此行/************************************
LZ如果你的查询固定是两个参数的话，
    用这个存储过程试试看，这样的话
    你在前台ASP就少点代码直接调用即可,
    当然你也可以在ASP前台用Split切割分组再传...
    呵呵^_^，我也期待高手给你更好的解决方法吧
*************************************/
Create Proc Proc_Value
      @Value1 Varchar(5),
      @Value2 Varchar(5)
As 
   Begin
    Delete From #          
        Insert # 
               Select A.id,A.productsort,A.name,
                      C.englishname+';'+Cast(C.machines1 As Varchar(8000)) As machines1,
                      Cast(C.machines2 As Varchar(8000)) As machines2
               From 
                   product A
               Inner Join
                  (Select A.englishname,B.id,B.machines1,B.machines2 
                   From productsort A 
                   Inner Join original B 
                   On Charindex(@Value1,B.machines1)>0 Or
                      Charindex(@Value1,B.machines2)>0 Where A.id=B.srotid) C
               On 
                 Charindex(Cast(C.id As Varchar),A.machines1)>0 Or 
                 Charindex(Cast(C.id As Varchar),A.machines2)>0    
        Union All
               Select A.id,A.productsort,A.name,
                      C.englishname+';'+Cast(C.machines1 As Varchar(8000)) As machines1,
                      Cast(C.machines2 As Varchar(8000)) As machines2
               From 
                   product A
               Inner Join
                  (Select A.englishname,B.id,B.machines1,B.machines2 
                   From productsort A 
                   Inner Join original B 
                   On Charindex(@Value2,B.machines1)>0 Or
                      Charindex(@Value2,B.machines2)>0 Where A.id=B.srotid) C
               On 
                 Charindex(Cast(C.id As Varchar),A.machines1)>0 Or 
                 Charindex(Cast(C.id As Varchar),A.machines2)>0 
        Select * From # 
    End
Go /*调用PROC查看结果   
   注意如果你 Exec Proc_Value 'aaa','bbb' 就会有重复的数据
   因为你用的是Ntext所以处理起来很麻烦
*/
Exec Proc_Value 'aaa','mmm'
     
/*
id          productsort name       machines1      machines2         
----------- ----------- ---------- --------------- -----------------
101         10          A-001      手机;aaa;bbb;   ccc;ddd;
102         10          A-002      手机;aaa;bbb;   ccc;ddd;
104         11          A-004      电脑;zzz;xxx;   vvv;mmm;（所影响的行数为 3 行）
*/

Answer 6

SoftwKLC,真的很感谢您热心的帮助,我都觉得不好意思再烦你.不过我这个组合后的关键字不是固定的,可能有更多,或者只有一个.完了,如果连你也要期待高手.
唉,难道这个问题就没辙了~~~

Answer 7

azpow() 
我的那个方法
无论你要传递多少个参数都是可以的~~~
不过就是麻烦了点,呵呵!!!!!你自己UNION 加可以了
最好还是不要用Ntext类型的
建议如果你传递的参数很多的话,最好还是用Procedure或者Function来实现

Answer 8

SoftwKLC
azpow() 
我的那个方法
无论你要传递多少个参数都是可以的~~~但是一经过处理组合又要变得好长好长,这样子速度就会变慢了.最好还是不要用Ntext类型的
我也不想用的,不过,里面的要存符的字符好多啊.郁闷,期待中..

Answer 9

SoftwKLC
你搞不定的,也没有人再搞了..

Answer 10

SoftwKLC
谢谢.
郁闷,这个SQL怎么这么麻烦的.

Answer 11

对你的数据结构有点迷惑，你的original与product表有什么关系？有个表中的machines 有什么关系吗？结构搞清楚了别人才能帮你

Answer 12

对你的数据结构有点迷惑，你的original与product表有什么关系？有个表中的machines 有什么关系吗？结构搞清楚了别人才能帮你谢谢.orininal是详细的数据,product中的machines里面存的都是original里面的ID号,当中以";"号区分.