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 null 200
42 G3Data 198 null 200
43 G3Data 199 null 200
44 G3Data 200 null 200
45 G3Data 201 null 200
46 G3Data 202 null 200
47 G3Data 203 null 200
48 G3Data 204 null 200
49 G3Data 205 null 200
50 G3Data 206 null 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
57 G5Data 98 98 200
58 G5Data 99 98 200
59 G5Data 100 98 200
60 G5Data 101 98 200
61 G5Data 102 98 200
62 G5Data 103 98 200
63 G5Data 104 98 200
64 G5Data 105 98 200
65 G5Data 106 98 200
66 G5Data 107 98 200
67 G5Data 108 98 200
68 G5Data 109 98 200
69 G5Data 110 98 200
70 G5Data 111 98 200
71 G5Data 112 98 200
72 G5Data 113 98 200
73 G5Data 114 98 200
74 G5Data 115 98 200
75 G5Data 116 98 200
76 G5Data 117 98 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,G4,
CASE WHEN G3 <> 0 THEN 'G3'
WHEN G3 = 0 AND G2 <> 0 THEN 'G2'
WHEN G3 = 0 AND G2 = 0 AND G1 <> 0 THEN 'G1'
WHEN G3 = 0 AND G1 = 0 AND G2 = 0 AND G4 <> 0 THEN 'G4'
END AS XGROUP_NEW
FROM (
SELECT Xtype,
SUM(CASE WHEN Xmin=0 OR Xmax=0 THEN 1 ELSE 0 END) AS 'G3',
SUM(CASE WHEN XvalueTypeMin>Xmin AND XvalueTypeMax<Xmax THEN 1 ELSE 0 END) AS 'G4',
SUM((CASE WHEN Group1='S1' AND Xvalue <=Xmin THEN 1 ELSE 0 END) + (CASE WHEN Group1='S5' AND Xvalue >=Xmax THEN 1 ELSE 0 END)) AS 'G1',
SUM((CASE WHEN Group1='S2' AND Xvalue <=Xmin THEN 1 ELSE 0 END) + (CASE WHEN Group1='S4' AND Xvalue >=Xmax THEN 1 ELSE 0 END)) AS 'G2'
FROM (
SELECT *,
CASE WHEN (Percentage>0 AND Percentage<=5) THEN 'S1'
WHEN (Percentage>5 AND Percentage<=25) THEN 'S2'
WHEN (Percentage>25 AND Percentage<=75) THEN 'S3'
WHEN (Percentage>75 AND Percentage<=95) THEN 'S4'
WHEN (Percentage>95 AND Percentage<=100) THEN 'S5' END AS Group1,
MIN(Xvalue) OVER(PARTITION BY Xtype) AS XvalueTypeMin,
MAX(Xvalue) OVER(PARTITION BY Xtype) AS XvalueTypeMax
FROM (
SELECT Xtype,Xvalue,ISNULL(Xmin,0) AS Xmin,ISNULL(Xmax,0) AS Xmax,ROW_NUMBER() OVER(PARTITION BY Xtype ORDER BY Xvalue) AS RowNum,
100.00*ROW_NUMBER() OVER(PARTITION BY Xtype ORDER BY Xvalue)/COUNT(1) OVER(PARTITION BY Xtype) AS Percentage
FROM TypeGroupTest
) AS TEMPA
) AS TEMPB
GROUP BY Xtype
) AS TEMPC
ORDER BY Xtype
Xtype G1 G2 G3 G4 XGROUP_NEW
G1Data 1 2 0 0 G2
G2Data 1 4 0 0 G2
G3Data 1 2 10 0 G3
G4Data 0 0 0 6 G4
G5Data 1 0 0 0 G1