小弟最近在做一个邮件订阅系统，是用户订阅网站上的房源，可以按照自己的要求定制
mail表
id int
email nvarchar
htype nvarchar
moneystart money
moneystop money
mjstart int
mjstop int
keyword
house表
id int
htype nvarchar
mj int
money money
title nvarchar
现在要对mail的订阅记录提取相应的房源信息，再发送给用户
因为数据量比较大，循环匹配效率非常低
求教能不能通过一个存储过程来实现生成这样的结果
email 邮件地址
ebody 邮件内容
比如有这样的sample
id email mjstart mjstop keyword
1 [email protected] 20 50 上海
2 [email protected] 50 100 北京
house
id htype mj money title
1 出租 30 500 上海
2 出租 70 500 北京
3 出租 70 500 北京
4 出租 30 500 上海
5 出租 70 500 上海
得到的结果为
email ebody
[email protected] <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a>
[email protected] <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>
------解决方案--------------------
- SQL code
---测试数据---if object_id('[mail]') is not null drop table [mail]gocreate table [mail]([id] int,[email] varchar(7),[mjstart] int,[mjstop] int,[keyword] varchar(4))insert [mail]select 1,[email protected]',20,50,'上海' union allselect 2,[email protected]',50,100,'北京'if object_id('[house]') is not null drop table [house]gocreate table [house]([id] int,[htype] varchar(4),[mj] int,[money] int,[title] varchar(4))insert [house]select 1,'出租',30,500,'上海' union allselect 2,'出租',70,500,'北京' union allselect 3,'出租',70,500,'北京' union allselect 4,'出租',30,500,'上海' union allselect 5,'出租',70,500,'上海' ---创建字符连接函数---create function F_Str(@email varchar(50))returns nvarchar(1000)asbegin declare @S nvarchar(1000) select @S=isnull(@S+'','')+'<a href=house_'+ltrim(b.id)+'.htm>'+title+'</a>' from mail a, house b where a.keyword=b.title and [email protected] return @Send---查询---select distinct a.email, dbo.f_str(a.email) as ebody from mail a, house bwhere a.keyword=b.title---结果---email ebody ------- ----------------------------------------------------------------[email protected] <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a><a href=house_5.htm>上海</a>[email protected] <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>（所影响的行数为 2 行）
------解决方案--------------------
- SQL code
declare @mail TABLE(id int, email nvarchar(50), mjstart int, mjstop int, keyword nvarchar(50))insert into @mail select 1 , [email protected]', 20 , 50, '上海' union all select 2, [email protected]', 50 , 100, '北京' declare @house table(id int, htype nvarchar(50), mj int, money money, title nvarchar(50) )insert into @houseselect 1, '出租', 30, 500, '上海' union allselect 2 , '出租', 70, 500, '北京' union all select 3 , '出租', 70, 500, '北京' union allselect 4 , '出租', 30, 500, '上海' union allselect 5 , '出租', 70, 500, '上海' ;with cte as( select a.id,a.email,a.mjstart,a.mjstop,a.keyword,b.htype,b.mj,b.money, rowid = row_number() over(order by b.id) from @mail a,@house b where a.keyword = b.title)select email, ebody = (select '<a href=house_'+ cast(rowid as varchar(10)) +'.htm>' +keyword+'</a>' from cte where a.email = email for xml path(''))from cte agroup by email