自己对比/* 表的结构: year_month card_id rest_days 2004-01 0100001 01,07,09,14 2004-01 0100002 01,07,12,21 2004-01 0100003 01,04,13,29 .................................................... 2004_04 0100001 01,07,09,14 2004_04 0100002 12,07,12,21 2004_04 0100003 21,04,13,29 */ declare @a table(year_month varchar(10),card_id varchar(10),rest_days varchar(20))insert @a select '2004-01','0100001','01,07,09,14' union all select '2004-01','0100002','01,07,12,21' union all select '2004-01','0100003','01,04,13,29' union all select '2004_04','0100001','01,07,09,14' union all select '2004_04','0100002','12,07,12,21' union all select '2004_04','0100003','21,04,13,29'select card_id,year_month+'-'+substring(rest_days,N,charindex(',',rest_days +',',N)-N) as d from @a a, (select 1 as N union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) b where substring(','+rest_days,N,1)= ',' order by card_id,N /* card_id d ---------- ------------------------------- 0100001 2004-01-01 0100001 2004_04-01 0100001 2004_04-07 0100001 2004-01-07 0100001 2004-01-09 0100001 2004_04-09 0100001 2004_04-14 0100001 2004-01-14 0100002 2004-01-01 0100002 2004_04-12 0100002 2004_04-07 0100002 2004-01-07 0100002 2004-01-12 0100002 2004_04-12 0100002 2004_04-21 0100002 2004-01-21 0100003 2004-01-01 0100003 2004_04-21 0100003 2004_04-04 0100003 2004-01-04 0100003 2004-01-13 0100003 2004_04-13 0100003 2004_04-29 0100003 2004-01-29(所影响的行数为 24 行)*/
create table tb(col varchar(20)) go declare @str varchar(8000) set @str='国家地理,美国国家地理,华夏地理,中国国家地理,旅游,摄影'set @str='insert into tb select * from (select col = ''' + replace(@str , ',' , ''' union all select ''') set @str = @str + ''')t'exec(@str)select * from tbdrop table tb/* col -------------------- 国家地理 美国国家地理 华夏地理 中国国家地理 旅游 摄影(所影响的行数为 6 行) */
create table tb(col varchar(20)) go declare @str varchar(8000) set @str='国家地理,美国国家地理,华夏地理,中国国家地理,旅游,摄影'set @str='insert into tb select * from (select col = ''' + replace(@str , ',' , ''' union all select ''') set @str = @str + ''')t'exec(@str)select * from tbdrop table tb/* col -------------------- 国家地理 美国国家地理 华夏地理 中国国家地理 旅游 摄影(所影响的行数为 6 行) */
初学者,能问问 select col = ''' + replace(@str , ',' , ''' union all select '''是什么意思呀,,,
union all select 怎么理解
print @str ,看具体情况.insert into tb select * from (select col = '国家地理' union all select '美国国家地理' union all select '华夏地理' union all select '中国国家地理' union all select '旅游' union all select '摄影')t
把','替换成'union all select',在前面再加上一个select 构造一个@sql语句 exec(@sql)
表的结构:
year_month card_id rest_days 2004-01 0100001 01,07,09,14
2004-01 0100002 01,07,12,21
2004-01 0100003 01,04,13,29
....................................................
2004_04 0100001 01,07,09,14
2004_04 0100002 12,07,12,21
2004_04 0100003 21,04,13,29
*/
declare @a table(year_month varchar(10),card_id varchar(10),rest_days varchar(20))insert @a select '2004-01','0100001','01,07,09,14'
union all select '2004-01','0100002','01,07,12,21'
union all select '2004-01','0100003','01,04,13,29'
union all select '2004_04','0100001','01,07,09,14'
union all select '2004_04','0100002','12,07,12,21'
union all select '2004_04','0100003','21,04,13,29'select card_id,year_month+'-'+substring(rest_days,N,charindex(',',rest_days +',',N)-N) as d
from @a a,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11) b
where substring(','+rest_days,N,1)= ','
order by card_id,N
/*
card_id d
---------- -------------------------------
0100001 2004-01-01
0100001 2004_04-01
0100001 2004_04-07
0100001 2004-01-07
0100001 2004-01-09
0100001 2004_04-09
0100001 2004_04-14
0100001 2004-01-14
0100002 2004-01-01
0100002 2004_04-12
0100002 2004_04-07
0100002 2004-01-07
0100002 2004-01-12
0100002 2004_04-12
0100002 2004_04-21
0100002 2004-01-21
0100003 2004-01-01
0100003 2004_04-21
0100003 2004_04-04
0100003 2004-01-04
0100003 2004-01-13
0100003 2004_04-13
0100003 2004_04-29
0100003 2004-01-29(所影响的行数为 24 行)*/
go
declare @str varchar(8000)
set @str='国家地理,美国国家地理,华夏地理,中国国家地理,旅游,摄影'set @str='insert into tb select * from (select col = ''' + replace(@str , ',' , ''' union all select ''')
set @str = @str + ''')t'exec(@str)select * from tbdrop table tb/*
col
--------------------
国家地理
美国国家地理
华夏地理
中国国家地理
旅游
摄影(所影响的行数为 6 行)
*/
go
declare @str varchar(8000)
set @str='国家地理,美国国家地理,华夏地理,中国国家地理,旅游,摄影'set @str='insert into tb select * from (select col = ''' + replace(@str , ',' , ''' union all select ''')
set @str = @str + ''')t'exec(@str)select * from tbdrop table tb/*
col
--------------------
国家地理
美国国家地理
华夏地理
中国国家地理
旅游
摄影(所影响的行数为 6 行)
*/
select col = ''' + replace(@str , ',' , ''' union all select '''是什么意思呀,,,
构造一个@sql语句
exec(@sql)