---测试数据---
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,'1@1.com',20,50,'上海' union all
select 2,'2@2.com',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                                                                                                
------- ----------------------------------------------------------------
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 行）

------解决方案--------------------

SQL code

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 @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