SELECT DISTINCT 物料id , 工序id , ROW_NUMBER() OVER ( ORDER BY 工序id ) AS 序号 FROM ( SELECT 物料id , 前工序id AS 工序id FROM TB UNION SELECT 物料id , 后工序id AS 工序id FROM TB ) TT
if object_id('[TB]') is not null drop table [TB] create table [TB] (物料id int,前工序id int,后工序id int) insert into [TB] select 11,3,4 union all select 11,4,5 union all select 11,5,6select * from [TB] select distinct 物料id,工序id,row_number() over(order by 工序id) as 序号 from ( select 物料id,前工序id as 工序id from TB union select 物料id,后工序id as 工序id from TB ) TT/* 物料id 工序id 序号 11 3 1 11 4 2 11 5 3 11 6 4*/
SELECT DISTINCT
物料id ,
工序id ,
ROW_NUMBER() OVER ( ORDER BY 工序id ) AS 序号
FROM ( SELECT 物料id ,
前工序id AS 工序id
FROM TB
UNION
SELECT 物料id ,
后工序id AS 工序id
FROM TB
) TT
create table [TB] (物料id int,前工序id int,后工序id int)
insert into [TB]
select 11,3,4 union all
select 11,4,5 union all
select 11,5,6select * from [TB]
select distinct 物料id,工序id,row_number() over(order by 工序id) as 序号
from (
select 物料id,前工序id as 工序id
from TB
union
select 物料id,后工序id as 工序id
from TB ) TT/*
物料id 工序id 序号
11 3 1
11 4 2
11 5 3
11 6 4*/
数据如下:
物料id 前工序id 后工序id
11 34 23
11 23 56
11 56 30想用语句转换成如下:
物料id 工序id 序号
11 34 1
11 23 2
11 56 3
11 30 4这样该怎么实现呢? 用order by 工序id 不能代表顺序