一个邮件订阅匹配信息的sql语句

Question

---测试数据---
if object_id('[mail]') is not null drop table [mail]
go
create table [mail]([id] int,[email] varchar(7),[mjstart] int,[mjstop] int,[keyword] varchar(4))
insert [mail]
select 1,'[email protected]',20,50,'上海' union all
select 2,'[email protected]',50,100,'北京'
if object_id('[house]') is not null drop table [house]
go
create table [house]([id] int,[htype] varchar(4),[mj] int,[money] int,[title] varchar(4))
insert [house]
select 1,'出租',30,500,'上海' union all
select 2,'出租',70,500,'北京' union all
select 3,'出租',70,500,'北京' union all
select 4,'出租',30,500,'上海' union all
select 5,'出租',70,500,'上海'
 
---创建字符连接函数---
create function F_Str(@email varchar(50))
returns nvarchar(1000)
as
begin
    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 @S
end---查询---
select
  distinct
  a.email,
  dbo.f_str(a.email) as ebody 
from 
  mail a,
  house b
where
  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 行）

Answer 1

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 @house
select 1,        '出租',          30,          500,            '上海' union all
select 2 ,       '出租',          70,          500,            '北京' union all 
select 3 ,       '出租',          70,          500,            '北京'  union all
select 4 ,       '出租',          30,          500,            '上海'  union all
select 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 a
group by email

Answer 2

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 @house
select 1,        '出租',          30,          500,            '上海' union all
select 2 ,       '出租',          70,          500,            '北京' union all 
select 3 ,       '出租',          70,          500,            '北京'  union all
select 4 ,       '出租',          30,          500,            '上海'  union all
select 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,'&lt;','<'),'&gt;','>')
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

Answer 3

create table mail(id int  identity,email varchar(20),mjstart int,mjstop int,keyword char(10))
insert into mail select 
'[email protected]',20,50 ,'上海'union all select
'[email protected]',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)
AS
BEGIN
    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, '')
END
GOselect (select top 1 email from ntabl n where n.px=px) as 邮箱, value = dbo.f_str(px) from ntabl group by px/*
邮箱                   value                                                                                                                                                                                                                                                            
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
[email protected]              <a href=house_2.htm>北京</a><a href=house_3.htm>北京</a>
[email protected]              <a href=house_1.htm>上海</a><a href=house_4.htm>上海</a><a href=house_5.htm>上海</a>（所影响的行数为 2 行）*/

Answer 4

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)
endselect mail.email,dbo.f_getebody(mail.id) from mail

Answer 5

追加一点条件，就是所有的mail表中的条件都是可选的，得判断是否为空，如果为空就是不加限制的6楼的方式不太可取，因为按照该方式还得建立一个表，而且需要实时更新再次感谢各位大大，明天加分

Answer 6

不加限制是什么意思？ 没有mail怎么发？

Answer 7

mial是必须的，其他的都可以为空
比如我没有填keyword那么就是任意关键字都可以