Sql 查询邮件地址多行合并一行，再发送邮件

--创建测试表
use Fly
go
IF OBJECT_ID('test') IS NOT NULL
    DROP TABLE test
CREATE TABLE test(
    Id int IDENTITY(1,1) NOT NULL,
    Name varchar(50) NULL,
    Judge char(1) NULL,
    Mail varchar(50) NULL
)
GO

--插入测试数据
INSERT INTO test (
            Name ,Judge ,Mail) 
    SELECT '张三','Y','100@qq.com' UNION ALL
    SELECT '李四','Y','101@qq.com' UNION ALL
    SELECT '王五','N','102@qq.com' 
GO

SELECT * FROM test

--查询多行合并
DECLARE 
    @Maliaddress varchar(8000)
set @Maliaddress = '''' + STUFF((SELECT ';' + Mail FROM test WHERE Judge in ('Y') FOR xml path('')),1,1,'') + ''''  
select @Maliaddress as 'MailAddress' 

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Fly_Huang',                
@recipients = @Maliaddress,     
@subject = 'Test电子邮件的主题',                       
@body = 'Test电子邮件的正文',                              
@body_format = 'HTML'