超难度模糊查询过程,有一个存储过程.多表查询....分不够,可以再加.

[xnxqs]

现在三个表如下:
表名:TB1
列名:ID1(数字) NAME1(VARCHAR) NAME11(VARCHAR) NAME111(VARCHAR) ....
表名:TB2
列名:ID2(数字) NAME2(VARCHAR) NAME22(VARCHAR) NAME222(VARCHAR) .....
表名:TB3
列名:ID3(数字) NAME3(ntext) NAME33(VARCHAR) NAME333(VARCHAR) .........
现提供一个字符变量: @filename根据这个字符变量,找出这三个表中相应的NAME1,NAME2,NAME3这几列并返回.返回内容包括NAME1,NAME2,NAME3的值,,,还得包括TB1,TB2,TB3中各自找到多少行,请高手指教并写出存储过程和调用过程.(ASP的)
谢谢.

[xluzhong]

create table t1(id int,name1 nvarchar(10))
create table t2(id int,name2 nvarchar(10))
create table t3(id int,name3 nvarchar(10))
insert into t1 select 1,'ralph' union all select 2,'king'
union all select 3,'andy' union all select 4,'terry'
insert into t2 select 1,'ralph' union all select 2,'king'
union all select 3,'andy'
insert into t3 select 1,'ralph' union all select 2,'king'
union all select 4,'terry'

go

create function f_test(@s nvarchar(10))
returns @t table (name4 nvarchar(10),[from] nvarchar(50),ctimes int)
as
begin

declare @tt table (name4 nvarchar(10),[from] nvarchar(50),ctimes int)
insert into @tt
select name1,'t1',0 from t1 where len(name1)-len(replace(name1,@s,''))>0
union all
select name2,'t2',0 from t2 where len(name2)-len(replace(name2,@s,''))>0
union all
select name3,'t3',0 from t3 where len(name3)-len(replace(name3,@s,''))>0
update @tt
set ctimes=b.c
from @tt a left join(
select [from],count(*) as c
from @tt
group by [from])b
on a.[from]=b.[from]
insert into @t select * from @tt
return
end
go

select *
from dbo.f_test('y')

drop function f_test
drop table t1,t2,t3

[zjcxc]

没看明白是什么意思

[xnxqs]

汗~~~二大高手都来了.
偶也是看不懂继续期待中........
不过正在试验.

[xnxqs]

调用的过程能写下吗????
谢谢!

[talantlee]

create proc find
@filename nvarchar(1000)
as

declare @sql nvarchar(1000)
set @sql=N'select name1,sum(1)as tb_1 from tb1 where name1like %@filen'
exec sp_executesql @sql,N'@filen nvarchar(1000)',@filen=@filename
寫個存儲過程啊，你在我上面加上其他你要的列名看看，

[talantlee]

不好意思，上面的錯了
exec('select ,name1, count(*)as 找到的行數 from (select name1, from tb1 where NAME1 like %@filen%)T from TB1')
這條你看看，這隻是對一個表的查詢，你看看行不？

[talantlee]

exec('select NAME1,(select count(*) from (select name1, from tb1 where NAME1 like %@filen%)T ) as 找到的行數 from TB1 group by NAME1')
上面的也錯了，再看看這個，也是找TB1裡的

[zjcxc]

我是说看不懂楼主的问题

[haslong]

好复杂，看不懂。挨

[qxq321]

楼主说的不错，真是复杂.....

[xnxqs]

晕晕,这几天出差了.网都没上.

团结的回答我看了,只能说沾点边...我的意思是说,根据一个词一次查三个表,返回结果必须包括这三个表的符合条件的值和结果行数.
然后再调用三个表内各自的值和行数(就是说符合条件的有多少个,是三个表各自有多少个,而不是共计多少个.比如说:TB1找到3个,TB2找到3个,TB3找到4个而不是说一共找到10个.)

而团结写的只是针对一个表来的.我要的是一次查三个表并能分别调用,,麻烦高手写上存储过程和调用过程.
谢谢哈.
这个应该如麻将三QUE一所写的那样,可能要建个临时表.不过他的写的偶看不懂.(偶也测试了好像没通过..)
路过的帮忙顶下,,这事有点吃紧.

TO:邹建..
不怕你不答就怕你说看不懂...5555555.......
当你说看不懂时,偶就开始怀疑偶的表达能力了..
偶这段的补充说明不知道你再看明白我的意思没有.
帮忙解下.

[xnxqs]

好的谢谢哈如果能补上调用过程那就更爽了.!!
嘿嘿...

[xnxqs]

还有,,请注意看我的最开始的提问有个字段是NTEXT型的..

在这里这个字段需要一些特殊的处理吗???还是直接按你的过程写就行??

[xnxqs]

晕晕,,经过测试发现,,你这个是FUNCTION不是存储过程..

我想变成存储过程参照你的方法没有成功,,能补写下存储过程吗??
并写下调用代码,,
不胜感激!!!谢谢!!
谢谢

[zjcxc]

create table t1(id int,name1 nvarchar(10),name2 nvarchar(10),name3 nvarchar(10))
create table t2(id int,name1 nvarchar(10),name2 nvarchar(10),name3 nvarchar(10))
create table t3(id int,name1 nvarchar(10),name2 nvarchar(10),name3 nvarchar(10))
insert t1 select 1,'ralph' ,'ralpy' ,'ralph'
union all select 2,'king' ,'ralph' ,'ralpy'
union all select 3,'andy' ,'ralph' ,'ralph'
union all select 4,'teyrry','ralph' ,'ralph'

insert t2 select 1,'sfdsf' ,'dfdsaf' ,'rtyty'
union all select 2,'king' ,'ralph' ,'ralph'
union all select 3,'rty' ,'4534' ,'xcvbbcb'
union all select 4,'teyrry','ralph' ,'ralph'

insert t3 select 1,'ralph' ,'ralph' ,'ralph'
union all select 2,'king' ,'ralph' ,'ralph'
go

create function f_test(@s nvarchar(10))
returns @t table (tbname sysname,fdname sysname,value nvarchar(10),ctimes int)
as
begin
declare @ss nvarchar(20)
set @ss='%'+@s+'%'

insert @t(tbname,fdname,value,ctimes)
select tbname='t1',fdname='name1',value=name1,cnt=count(*)
from t1 where name1 like @ss group by name1
union all
select tbname='t1',fdname='name2',value=name2,cnt=count(*)
from t1 where name2 like @ss group by name2
union all
select tbname='t1',fdname='name3',value=name3,cnt=count(*)
from t1 where name3 like @ss group by name3

union all
select tbname='t2',fdname='name1',value=name1,cnt=count(*)
from t2 where name1 like @ss group by name1
union all
select tbname='t2',fdname='name2',value=name2,cnt=count(*)
from t2 where name2 like @ss group by name2
union all
select tbname='t2',fdname='name3',value=name3,cnt=count(*)
from t2 where name3 like @ss group by name3

union all
select tbname='t3',fdname='name1',value=name1,cnt=count(*)
from t3 where name1 like @ss group by name1
union all
select tbname='t3',fdname='name2',value=name2,cnt=count(*)
from t3 where name2 like @ss group by name2
union all
select tbname='t3',fdname='name3',value=name3,cnt=count(*)
from t3 where name3 like @ss group by name3

update a set ctimes=b.cnt
from @t a,(select tbname,fdname,cnt=sum(ctimes) from @t group by tbname,fdname)b
where a.tbname=b.tbname and a.fdname=b.fdname
return
end
go

select *
from dbo.f_test('y')
go
drop function f_test
drop table t1,t2,t3

/*--结果
tbname fdname value ctimes
---------------------- ------------------------ ---------- -----------
t1 name1 andy 2
t1 name1 teyrry 2
t1 name2 ralpy 1
t1 name3 ralpy 1
t2 name1 rty 2
t2 name1 teyrry 2
t2 name3 rtyty 1

（所影响的行数为 7 行）

--*/