USE [MyDatabase]
/*
CREATE TABLE [dbo].question(
NO1 INT NOT NULL,
NAME nvarchar NOT NULL,
NO2 INT NOT NULL,
TYPE INT NOT NULL

)
--DROP TABLE question
INSERT INTO question VALUES (1,'A',5,1)
INSERT INTO question VALUES (2,'B',4,1)
INSERT INTO question VALUES (3,'C',6,1)
INSERT INTO question VALUES (5,'D',1,1)
INSERT INTO question VALUES (7,'E',2,1)
INSERT INTO question VALUES (1,'F',4,2)
INSERT INTO question VALUES (2,'G',4,2)
INSERT INTO question VALUES (2,'H',5,2)
INSERT INTO question VALUES (3,'I',5,2)
INSERT INTO question VALUES (4,'J',6,2)
INSERT INTO question VALUES (5,'K',6,2)
INSERT INTO question VALUES (6,'L',3,2)

*/
SELECT *FROM question

SELECT NO2R,FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NO2 ORDER BY TYPE) AS NO2R,
FROM question --ORDER BY NO2
)T
WHERE NO2R =1
ORDER BY T.NO1

---结果--排序已经正确--
NO2R NO2R NO1 NAME NO2 TYPE
1 1 1 A 5 1
1 1 2 B 4 1
1 1 3 C 6 1
1 1 5 D 1 1
1 1 6 L 3 2
1 1 7 E 2 1

接下来join下，以NO2为关联字段，left join原表。

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY T1.NO1) AS order1,NO1,NAME,NO2,TYPE FROM(
SELECT NO2R,NO1,NAME,NO2,TYPE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NO2 ORDER BY TYPE) AS NO2R,NO1,NAME,NO2,TYPE
FROM question --ORDER BY NO2
)T
WHERE NO2R =1
--ORDER BY T.NO1 <-用partition代替
)T1
)T2
left join
(SELECT NO1,NAME,NO2,TYPE FROM question) T3 ON T3.NO2= T2.NO2

-----结果最右四列-----
order1 NO1 NAME NO2 TYPE NO1 NAME NO2 TYPE
1 1 A 5 1 1 A 5 1
1 1 A 5 1 2 H 5 2
1 1 A 5 1 3 I 5 2
2 2 B 4 1 2 B 4 1
2 2 B 4 1 1 F 4 2
2 2 B 4 1 2 G 4 2
3 3 C 6 1 3 C 6 1
3 3 C 6 1 4 J 6 2
3 3 C 6 1 5 K 6 2
4 5 D 1 1 5 D 1 1
5 6 L 3 2 6 L 3 2
6 7 E 2 1 7 E 2 1