我已經根據 id_card 對臨時表中的資料進行了分組。每張卡有多個活動。我想為該特定卡上發生的所有活動發送 1 封電子郵件。但是，我不知道如何根據 id 獲取資料。我應該使用游標來獲取 id 嗎？表中的示例，id_card = 1 需要 2 行，id_card = 3 需要 2 行，依此類推
id_card dat_actitity type
1 2022-04-21 tag on
1 2022-04-23 tag off
2 2022-04-28 tag on
2 2022-04-20 tag off
2 2022-04-01 tag on
3 2022-04-27 tag on
你能幫忙嗎？非常感謝
uj5u.com熱心網友回復：
select @id = min(id_card) from #tmp
while @id is not null
begin
drop table if exists #rows
-- get the rows
select * into #rows from #tmp where id_card = @id
if (@@ROWCOUNT > 0)
begin
-- send mail
SET @Subject = 'Flagged Card activity noticed on Card with ID ' convert(varchar(10), @id)
SET @Message =
N'<H3>Details of the activity are as follows:</H3>'
N'<table border="1">'
N'<tr bgcolor=#ADD6FF>'
N'<th>Activity Date</th>'
N'<th>Activity Day</th>'
N'<th>Time</th>'
N'<th>Activity Type</th>'
N'</tr>'
CAST((
SELECT
td = Convert(varchar(10), dat_activity, 103), '',
td = FORMAT(dat_activity,'ddd'), '',
td = Convert(varchar(8), Convert(time, dat_activity)), '',
td = ISNULL(str_activitydesc,'N/A')
FROM #tmp
order by dat_activity desc
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
N'</table>'
PRINT 'Email Sent'
--Now send the email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = @recipientList,
@subject = @Subject,
@body = @Message,
@body_format = 'HTML',
@importance = 'High'
end
-- get next id
select @id = min(id_card) from #tmp where id_card > @id
end
uj5u.com熱心網友回復：
你可以使用一個while回圈，像這樣
declare @id int
-- get the first id (minimum)
select @id = min(id) from #tmp
while @id is not null
begin
-- get the rows
select * from #tmp where id = @id
-- send mail
exec msdb.dbo.sp_send_dbmail . . .
-- get next id
select @id = min(id) from #tmp where id > @id
end
db<>小提琴演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/467825.html