According to the color serial number-CodePudding

Number of the color serial number
1 black 3-1
2 red, 2-1
3 blue 1-1
4 black 3-2
5 black 3-3
6 red 2-2

Serial number means such as a total of three black,
Number 1 is three cylinder cloth cylinder is so 3-1 of 1
Number 4 is a three cylinder cloth is 3-2 of 2 cylinder so
Number 5 is 2 cylinder of triplex cloth so is 3-3

CodePudding user response:

 with t as (
Select 1 as id, 'black' as color 
Union all select 2, 'red' 
Union all select 3, 'blue' 
Union all select 4, 'black' 
Union all select 5, 'black' 
Union all select 6, 'red' 
) 
Select *, convert (varchar, CNT) + '-' + convert (varchar, rids) as sn 
The from (
Select *, ROW_NUMBER () over (partition by color order by id) as rids 
The from t 
) a 
Cross the apply (
Select COUNT (0) as the from t where color CNT=a.c olor 
B) 
The order by id