add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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,'G1Data',98,100,200),
(2,'G1Data',99,100,200),
(3,'G1Data',100,100,200),
(4,'G1Data',101,100,200),
(5,'G1Data',102,100,200),
(6,'G1Data',103,100,200),
(7,'G1Data',104,100,200),
(8,'G1Data',105,100,200),
(9,'G1Data',106,100,200),
(10,'G1Data',107,100,200),
(11,'G1Data',108,100,200),
(12,'G1Data',109,100,200),
(13,'G1Data',110,100,200),
(14,'G1Data',111,100,200),
(15,'G1Data',112,100,200),
(16,'G1Data',113,100,200),
(17,'G1Data',114,100,200),
(18,'G1Data',115,100,200),
(19,'G1Data',116,100,200),
(20,'G1Data',117,100,200),
(21,'G2Data',190,100,200),
(22,'G2Data',191,100,200),
(23,'G2Data',192,100,200),
(24,'G2Data',193,100,200),
(25,'G2Data',194,100,200),
(26,'G2Data',195,100,200),
(27,'G2Data',196,100,200),
XTypeNo Xtype Xvalue Xmin Xmax
1 G1Data 98 100 200
2 G1Data 99 100 200
3 G1Data 100 100 200
4 G1Data 101 100 200
5 G1Data 102 100 200
6 G1Data 103 100 200
7 G1Data 104 100 200
8 G1Data 105 100 200
9 G1Data 106 100 200
10 G1Data 107 100 200
11 G1Data 108 100 200
12 G1Data 109 100 200
13 G1Data 110 100 200
14 G1Data 111 100 200
15 G1Data 112 100 200
16 G1Data 113 100 200
17 G1Data 114 100 200
18 G1Data 115 100 200
19 G1Data 116 100 200
20 G1Data 117 100 200
21 G2Data 190 100 200
22 G2Data 191 100 200
23 G2Data 192 100 200
24 G2Data 193 100 200
25 G2Data 194 100 200
26 G2Data 195 100 200
27 G2Data 196 100 200
28 G2Data 197 100 200
29 G2Data 198 100 200
30 G2Data 199 100 200
31 G2Data 200 100 200
32 G2Data 201 100 200
33 G2Data 202 100 200
34 G2Data 203 100 200
35 G2Data 204 100 200
36 G2Data 205 100 200
37 G2Data 206 100 200
38 G2Data 207 100 200
39 G2Data 208 100 200
40 G2Data 209 100 200
41 G3Data 197 0 200
42 G3Data 198 0 200
43 G3Data 199 0 200
44 G3Data 200 0 200
45 G3Data 201 0 200
46 G3Data 202 0 200
47 G3Data 203 0 200
48 G3Data 204 0 200
49 G3Data 205 0 200
50 G3Data 206 0 200
51 G4Data 150 100 200
52 G4Data 151 100 200
53 G4Data 152 100 200
54 G4Data 153 100 200
55 G4Data 154 100 200
56 G4Data 155 100 200
CREATE TABLE ExpectData (
Xtype nvarchar(40) NULL ,
XGroup nvarchar(40) NULL);
-- 以上測試資料集，預期分類資料狀況
INSERT INTO ExpectData (Xtype,XGroup)
VALUES
('G1Data','G1'),
('G2Data','G2'),
('G3Data','G3'),
('G4Data','G4');
SELECT *
FROM ExpectData
Xtype XGroup
G1Data G1
G2Data G2
G3Data G3
G4Data G4
SELECT Xtype,G1,G2,G3,
CASE
WHEN G3 <> 0 THEN 'G3'
WHEN G3 = 0 AND G1 <> 0 THEN 'G1'
WHEN G3 = 0 AND G1 = 0 AND G2 <> 0 THEN 'G2'
WHEN G3 = 0 AND G1 = 0 AND G2 = 0 THEN NULL
END AS XGROUP_NEW
FROM (
SELECT Xtype,
SUM(CASE WHEN XGROUP='G1' THEN TempValue ELSE 0 END) AS 'G1',
SUM(CASE WHEN XGROUP='G2' THEN TempValue ELSE 0 END) AS 'G2',
SUM(CASE WHEN XGROUP='G3' THEN TempValue ELSE 0 END) AS 'G3'
FROM (
SELECT Xtype,1 AS TempValue,
CASE
WHEN Xvalue <= Xmin
THEN 'G1'
WHEN Xvalue >= Xmax
THEN 'G3'
WHEN Xvalue > Xmin AND Xvalue < Xmax
THEN 'G2'
END AS XGROUP
FROM TypeGroupTest) AS TempA
GROUP BY Xtype
) AS TempB
ORDER BY Xtype
Xtype G1 G2 G3 XGROUP_NEW
G1Data 3 17 0 G1
G2Data 0 10 10 G3
G3Data 0 3 7 G3
G4Data 0 6 0 G2