mssql sqlserver 使用sql指令碼輸出交替不同的背景色的html資訊的方法分享

 

轉自:http://www.maomao365.com/?p=6679

摘要:
下文將分享使用sql指令碼輸出交替變換的不同背景顏色的sql指令碼的方法分享，如下所示:
實驗環境:sqlserver 2008 R2

---

例:
下文 首先採用 over() row_number 函式生成的行編號，
然後對每行進行顏色變化操作，生成不同的背景色，如下所示:

create table test(keyId int,info varchar(30))
go
insert into test(keyId,info)values(10,`測試資訊20180625-1`)
insert into test(keyId,info)values(20,`測試資訊20180626-2`)
insert into test(keyId,info)values(21,`測試資訊20180628-3`)
insert into test(keyId,info)values(81,`測試資訊20180620-4`)
insert into test(keyId,info)values(92,`測試資訊20180608-5`)
insert into test(keyId,info)values(101,`測試資訊20180605-6`)
insert into test(keyId,info)values(102,`測試資訊20180606-7`)
go


declare @tmp varchar(max)
set @tmp =`<table>`
set @tmp =@tmp+`<tr><td>流水號<td>keyId<td>info</tr>`

select 
@tmp=@tmp+`<tr style=``background-color:`+ case when t.[編號] %2=0 then `blue` else `` end+```>`
+`<td>`+ convert(varchar(100),t.[編號])
+`<td>`+ convert(varchar(100),t.keyId)
+`<td>`+t.info
+`</tr>`
from 
(
select row_number() over(order by keyId asc ) as [編號],
keyId,info from test ) as t 


set @tmp =@tmp+`</table>`
select @tmp ---列印生成的html資訊 

go
drop table test