一个邮件订阅匹配信息的sql语句
小弟最近在做一个邮件订阅系统，是用户订阅网站上的房源，可以按照自己的要求定制
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
mail
id email mjstart mjstop keyword
1 1@1.com 20 50 上海
2 2@2.com 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
1@1.com <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a>
2@2.com <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>
[解决办法]
---测试数据---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,'1@1.com',20,50,'上海' union allselect 2,'2@2.com',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 a.email=@email return @Send---查询---select distinct a.email, dbo.f_str(a.email) as ebody from mail a, house bwhere a.keyword=b.title---结果---email ebody ------- ----------------------------------------------------------------1@1.com <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a><a href=house_5.htm>上海</a>2@2.com <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>（所影响的行数为 2 行）
[解决办法]
declare @mail TABLE(id int, email nvarchar(50), mjstart int, mjstop int, keyword nvarchar(50))insert into @mail select 1 , '1@1.com', 20 , 50, '上海' union all select 2, '2@2.com', 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
[解决办法]
declare @mail TABLE(id int, email nvarchar(50), mjstart int, mjstop int, keyword nvarchar(50))insert into @mail select 1 , '1@1.com', 20 , 50, '上海' union all select 2, '2@2.com', 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 = replace(replace(ebody,'<','<'),'>','>')from( 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 a group by email)T
[解决办法]
create table mail(id int identity,email varchar(20),mjstart int,mjstop int,keyword char(10))insert into mail select '1@1.com',20,50 ,'上海'union all select'2@2.com',50,100,'北京'create table house (id int identity,htype char(10),mj int,money int,title char(10))insert into house select '出租',30,500,'上海' union all select '出租',70,500,'北京' union all select '出租',70,500,'北京' union all select '出租',30,500,'上海' union all select '出租',70,500,'上海'select px=(case when title='北京' then 1 else 2 end),h.*,email into ntabl from mail join house h on mail.keyword=h.title CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN DECLARE @r varchar(8000) SET @r = '<' SELECT @r = @r + '<a href=house_'+cast(id as varchar(4))+'.htm>'+rtrim(title)+'</a>' FROM ntabl WHERE px=@id RETURN STUFF(@r, 1, 1, '')ENDGOselect (select top 1 email from ntabl n where n.px=px) as 邮箱, value = dbo.f_str(px) from ntabl group by px/*邮箱 value -------------------- ---------------------------------------------------------------------------------------------------------------- 1@1.com <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>1@1.com <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a><a href=house_5.htm>上海</a>（所影响的行数为 2 行）*/
[解决办法]
alter function dbo.f_getebody
(@id int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
select @str=''
select @str=@str+'<a href=house_'+convert(varchar,house.id)+'.htm>'+house.title+' </a>'
from house,mail
where mail.id = @id and
mail.keyword = house.title and house.mj between mail.mjstart and mail.mjstop
order by house.id
return (@str)
end
select mail.email,dbo.f_getebody(mail.id) from mail