SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TypeGroupTest (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
Xvalue int NULL , 
Xmin int NULL ,
Xmax int NULL); 

INSERT INTO TypeGroupTest (XTypeNo,Xtype,Xvalue,Xmin,Xmax)
VALUES 
(1,'TypeA',10,100,200),
(2,'TypeA',20,100,200),
(3,'TypeA',30,100,200),
(4,'TypeB',410,200,300),
(5,'TypeB',420,200,300),
(6,'TypeB',430,200,300),
(7,'TypeC',310,300,400),
(8,'TypeC',320,300,400),
(9,'TypeC',330,300,400),
(12,'TypeD',410,400,500),
(13,'TypeD',490,400,500),
(14,'TypeD',550,400,500),
(15,'TypeE',510,500,600),
(16,'TypeE',590,500,600),
(17,'TypeE',450,500,600),
-- 額外加入
(18,'TypeD',505,400,500),
(19,'TypeD',506,400,500),
(20,'TypeF',590,500,600),
(21,'TypeF',450,500,600),
-- 額外加入PART2
(22,'TypeG',450,600,700),
(23,'TypeG',460,600,700),
(24,'TypeG',470,600,700),
(25,'TypeG',650,600,700),
(26,'TypeG',660,600,700),
(27,'TypeG',850,600,700),
(28,'TypeG',860,600,700),
(29,'TypeG',870,600,700);
SELECT *
FROM TypeGroupTest;


XTypeNo
Xtype
Xvalue
Xmin
Xmax


1
TypeA
10
100
200

2
TypeA
20
100
200

3
TypeA
30
100
200

4
TypeB
410
200
300

5
TypeB
420
200
300

6
TypeB
430
200
300

7
TypeC
310
300
400

8
TypeC
320
300
400

9
TypeC
330
300
400

12
TypeD
410
400
500

13
TypeD
490
400
500

14
TypeD
550
400
500

15
TypeE
510
500
600

16
TypeE
590
500
600

17
TypeE
450
500
600

18
TypeD
505
400
500

19
TypeD
506
400
500

20
TypeF
590
500
600

21
TypeF
450
500
600

22
TypeG
450
600
700

23
TypeG
460
600
700

24
TypeG
470
600
700

25
TypeG
650
600
700

26
TypeG
660
600
700

27
TypeG
850
600
700

28
TypeG
860
600
700

29
TypeG
870
600
700


SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  WHEN [Xvalue] >= [Xmin] AND [Xvalue] <= [Xmax]
  THEN 'X2'
  ELSE 'X4'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=1


Xtype
XGROUP


TypeA
X1

TypeB
X3

TypeC
X2


SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  WHEN [Xvalue] BETWEEN [Xmin] AND [Xmax]
  THEN 'X2'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=1
UNION 
SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  ELSE 'X4'
END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=2 AND XGROUP <> 'X4'
UNION 
SELECT [Xtype],[XGROUP]
FROM (
SELECT [Xtype],[XGROUP],
COUNT(1) OVER(PARTITION BY [Xtype]) AS ROWNUM
FROM (
SELECT DISTINCT [Xtype],
CASE
  WHEN [Xvalue] <= [Xmin]
  THEN 'X1'
  WHEN [Xvalue] >= [Xmax]
  THEN 'X3'
  ELSE 'X2'
  END AS [XGROUP]
FROM [TypeGroupTest]) AS TempA
) AS TempB
WHERE ROWNUM=3 AND XGROUP NOT IN ('X1', 'X2')


Xtype
XGROUP


TypeA
X1

TypeB
X3

TypeC
X2

TypeD
X3

TypeE
X1

TypeF
X1

TypeG
X3

XTypeNo	Xtype	Xvalue	Xmin	Xmax
1	TypeA	10	100	200
2	TypeA	20	100	200
3	TypeA	30	100	200
4	TypeB	410	200	300
5	TypeB	420	200	300
6	TypeB	430	200	300
7	TypeC	310	300	400
8	TypeC	320	300	400
9	TypeC	330	300	400
12	TypeD	410	400	500
13	TypeD	490	400	500
14	TypeD	550	400	500
15	TypeE	510	500	600
16	TypeE	590	500	600
17	TypeE	450	500	600
18	TypeD	505	400	500
19	TypeD	506	400	500
20	TypeF	590	500	600
21	TypeF	450	500	600
22	TypeG	450	600	700
23	TypeG	460	600	700
24	TypeG	470	600	700
25	TypeG	650	600	700
26	TypeG	660	600	700
27	TypeG	850	600	700
28	TypeG	860	600	700
29	TypeG	870	600	700