mssql根据条件数据列分解成多行数据,急

发布网友发布时间：2022-04-15 01:23

共1个回答

热心网友时间：2022-04-15 02:52

创建测试表

create table t
(id int,
name varchar(10),
type varchar(10))

insert into t values (1,'苹果','003')
insert into t values (2,'香蕉','A04')
insert into t values (3,'西瓜',NULL)
insert into t values (4,'石榴','002')

运行

select t.*,t.name+s.name2 name2 from t left join
(select distinct type,
case when isnumeric(replace(type,0,''))=0 then
left(replace(type,0,''),1)+cast(t1.id as varchar)+'级' else cast(t1.id as varchar)+'级' end name2
from t,
(select 1 id union all
select 2 id union all
select 3 id union all
select 4 id ) t1
where type is not null
and t1.id<=right(replace(type,0,''),1)) s
on t.type=s.type

结果

你可以把这部分单独拿出来运行一下，看下结果

select distinct type,
case when isnumeric(replace(type,0,''))=0 then
left(replace(type,0,''),1)+cast(t1.id as varchar)+'级' else cast(t1.id as varchar)+'级' end name2
from t,
(select 1 id union all
select 2 id union all
select 3 id union all
select 4 id ) t1
where type is not null
and t1.id<=right(replace(type,0,''),1)