数据库把1行查询为n行

一、效果展示

表中的数据：

查询到的数据：

二、SQL Server中实现

SELECT id,strAuthorId from cn_news where id<3;

with cte as
(select id,CHARINDEX(',',strAuthorId+',') as station,
 SUBSTRING(strAuthorId,1,CHARINDEX(',',strAuthorId+',')-1) as strAuthorIdSimple
 from cn_news
 union all
 select a.id,CHARINDEX(',',a.strAuthorId+',',b.station+1),
 SUBSTRING(a.strAuthorId,b.station+1,CHARINDEX(',',a.strAuthorId+',',b.station+1)-b.station-1)
 from cn_news as a
 join cte as b
 on a.id=b.id and CHARINDEX(',',a.strAuthorId+',',b.station+1)>0)
select * from cte where id<3;