解决了第一个问题。 后面的问题不是很明白。 create table A(ID int,NO1 int,NO2 int,NO3 int) insert A select 1,1,1,1 insert A select 2,4,3,2 insert A select 3,1,2,3 create table B(NUM int identity(1,1),ID int,RENO int)insert B select ID,NO1 from (select ID, case NO1 when 1 then 1 else 0 end [NO1], case NO2 when 2 then 2 else 0 end [NO2], case NO3 when 3 then 3 else 0 end [NO3] from A where NO1=1 or NO2=2 or NO3=3) a where NO1<>0 union all select ID,NO2 from (select ID, case NO1 when 1 then 1 else 0 end [NO1], case NO2 when 2 then 2 else 0 end [NO2], case NO3 when 3 then 3 else 0 end [NO3] from A where NO1=1 or NO2=2 or NO3=3) a where NO2<>0 union all select ID,NO3 from (select ID, case NO1 when 1 then 1 else 0 end [NO1], case NO2 when 2 then 2 else 0 end [NO2], case NO3 when 3 then 3 else 0 end [NO3] from A where NO1=1 or NO2=2 or NO3=3) a where NO3<>0select * from Bdrop table A drop table B /* NUM ID RENO ----------- ----------- ----------- 1 1 1 2 3 1 3 3 2 4 3 3(4 行受影响) */
后面的问题不是很明白。
create table A(ID int,NO1 int,NO2 int,NO3 int)
insert A select 1,1,1,1
insert A select 2,4,3,2
insert A select 3,1,2,3
create table B(NUM int identity(1,1),ID int,RENO int)insert B
select ID,NO1
from
(select ID,
case NO1 when 1 then 1 else 0 end [NO1],
case NO2 when 2 then 2 else 0 end [NO2],
case NO3 when 3 then 3 else 0 end [NO3]
from A
where NO1=1 or NO2=2 or NO3=3) a
where NO1<>0
union all
select ID,NO2
from
(select ID,
case NO1 when 1 then 1 else 0 end [NO1],
case NO2 when 2 then 2 else 0 end [NO2],
case NO3 when 3 then 3 else 0 end [NO3]
from A
where NO1=1 or NO2=2 or NO3=3) a
where NO2<>0
union all
select ID,NO3
from
(select ID,
case NO1 when 1 then 1 else 0 end [NO1],
case NO2 when 2 then 2 else 0 end [NO2],
case NO3 when 3 then 3 else 0 end [NO3]
from A
where NO1=1 or NO2=2 or NO3=3) a
where NO3<>0select * from Bdrop table A
drop table B
/*
NUM ID RENO
----------- ----------- -----------
1 1 1
2 3 1
3 3 2
4 3 3(4 行受影响)
*/