MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `accsubject`  (
  `id` varchar(4) ,
  `field` tinyint(1)
);
INSERT INTO `accsubject` VALUES ('1101', 3);
INSERT INTO `accsubject` VALUES ('1102', 1);
INSERT INTO `accsubject` VALUES ('1103', 1);
INSERT INTO `accsubject` VALUES ('1104', 3);
INSERT INTO `accsubject` VALUES ('1105', 3);
INSERT INTO `accsubject` VALUES ('1141', 1);
INSERT INTO `accsubject` VALUES ('1144', 1);
INSERT INTO `accsubject` VALUES ('1149', 3);
INSERT INTO `accsubject` VALUES ('1178', 1);
INSERT INTO `accsubject` VALUES ('1201', 1);
INSERT INTO `accsubject` VALUES ('1202', 2);
INSERT INTO `accsubject` VALUES ('1203', 2);
INSERT INTO `accsubject` VALUES ('1251', 3);
INSERT INTO `accsubject` VALUES ('1252', 1);
INSERT INTO `accsubject` VALUES ('1253', 1);
INSERT INTO `accsubject` VALUES ('1257', 3);
INSERT INTO `accsubject` VALUES ('1259', 1);
INSERT INTO `accsubject` VALUES ('1281', 3);
INSERT INTO `accsubject` VALUES ('1289', 1);
INSERT INTO `accsubject` VALUES ('1402', 1);
INSERT INTO `accsubject` VALUES ('1403', 1);
INSERT INTO `accsubject` VALUES ('1428', 3);
INSERT INTO `accsubject` VALUES ('1429', 1);
INSERT INTO `accsubject` VALUES ('1444', 1);
INSERT INTO `accsubject` VALUES ('1457', 1);
INSERT INTO `accsubject` VALUES ('1501', 1);
INSERT INTO `accsubject` VALUES ('1502', 2);
INSERT INTO `accsubject` VALUES ('1521', 3);
INSERT INTO `accsubject` VALUES ('1522', 2);
INSERT INTO `accsubject` VALUES ('1541', 1);
INSERT INTO `accsubject` VALUES ('1542', 2);
INSERT INTO `accsubject` VALUES ('1551', 3);
INSERT INTO `accsubject` VALUES ('1552', 2);
INSERT INTO `accsubject` VALUES ('1821', 3);
INSERT INTO `accsubject` VALUES ('1824', 3);
INSERT INTO `accsubject` VALUES ('1825', 3);
INSERT INTO `accsubject` VALUES ('1827', 3);
INSERT INTO `accsubject` VALUES ('2102', 2);
INSERT INTO `accsubject` VALUES ('2109', 2);
INSERT INTO `accsubject` VALUES ('2141', 2);
INSERT INTO `accsubject` VALUES ('2144', 2);
INSERT INTO `accsubject` VALUES ('2147', 2);
INSERT INTO `accsubject` VALUES ('2148', 2);
INSERT INTO `accsubject` VALUES ('2154', 2);
INSERT INTO `accsubject` VALUES ('2155', 2);
INSERT INTO `accsubject` VALUES ('2156', 2);
INSERT INTO `accsubject` VALUES ('2157', 2);
INSERT INTO `accsubject` VALUES ('2175', 2);
INSERT INTO `accsubject` VALUES ('2176', 3);
INSERT INTO `accsubject` VALUES ('2177', 2);
INSERT INTO `accsubject` VALUES ('2178', 2);
INSERT INTO `accsubject` VALUES ('2251', 3);
INSERT INTO `accsubject` VALUES ('2253', 2);
INSERT INTO `accsubject` VALUES ('2259', 2);
INSERT INTO `accsubject` VALUES ('2281', 2);
INSERT INTO `accsubject` VALUES ('2504', 2);
INSERT INTO `accsubject` VALUES ('2821', 2);
INSERT INTO `accsubject` VALUES ('2824', 2);
INSERT INTO `accsubject` VALUES ('2825', 2);
INSERT INTO `accsubject` VALUES ('3101', 2);
INSERT INTO `accsubject` VALUES ('3311', 2);
INSERT INTO `accsubject` VALUES ('3312', 2);
INSERT INTO `accsubject` VALUES ('3317', 2);
INSERT INTO `accsubject` VALUES ('3318', 2);
INSERT INTO `accsubject` VALUES ('3319', 2);
INSERT INTO `accsubject` VALUES ('4101', 2);
INSERT INTO `accsubject` VALUES ('4102', 1);
INSERT INTO `accsubject` VALUES ('4103', 1);
INSERT INTO `accsubject` VALUES ('4104', 3);
INSERT INTO `accsubject` VALUES ('4105', 2);
INSERT INTO `accsubject` VALUES ('4106', 2);
INSERT INTO `accsubject` VALUES ('5101', 1);
INSERT INTO `accsubject` VALUES ('5102', 1);
INSERT INTO `accsubject` VALUES ('5103', 3);
INSERT INTO `accsubject` VALUES ('5201', 1);
INSERT INTO `accsubject` VALUES ('5203', 2);
INSERT INTO `accsubject` VALUES ('5204', 2);
INSERT INTO `accsubject` VALUES ('5205', 2);
INSERT INTO `accsubject` VALUES ('6201', 3);
INSERT INTO `accsubject` VALUES ('6203', 3);
INSERT INTO `accsubject` VALUES ('6204', 3);
INSERT INTO `accsubject` VALUES ('6205', 3);
INSERT INTO `accsubject` VALUES ('6206', 1);
INSERT INTO `accsubject` VALUES ('6207', 3);
INSERT INTO `accsubject` VALUES ('6208', 1);
INSERT INTO `accsubject` VALUES ('6209', 1);
INSERT INTO `accsubject` VALUES ('6210', 3);
INSERT INTO `accsubject` VALUES ('6211', 1);
INSERT INTO `accsubject` VALUES ('6212', 1);
INSERT INTO `accsubject` VALUES ('6213', 3);
INSERT INTO `accsubject` VALUES ('6214', 1);
INSERT INTO `accsubject` VALUES ('6215', 3);
INSERT INTO `accsubject` VALUES ('6216', 3);
INSERT INTO `accsubject` VALUES ('6217', 1);
INSERT INTO `accsubject` VALUES ('6218', 1);
INSERT INTO `accsubject` VALUES ('6219', 3);
INSERT INTO `accsubject` VALUES ('6221', 1);
INSERT INTO `accsubject` VALUES ('6222', 3);
INSERT INTO `accsubject` VALUES ('6223', 1);
INSERT INTO `accsubject` VALUES ('6224', 1);
INSERT INTO `accsubject` VALUES ('6225', 3);
INSERT INTO `accsubject` VALUES ('6226', 1);
INSERT INTO `accsubject` VALUES ('6227', 1);
INSERT INTO `accsubject` VALUES ('6228', 3);
INSERT INTO `accsubject` VALUES ('6229', 1);
INSERT INTO `accsubject` VALUES ('6245', 3);
INSERT INTO `accsubject` VALUES ('6248', 1);
INSERT INTO `accsubject` VALUES ('6249', 3);
INSERT INTO `accsubject` VALUES ('7101', 2);
INSERT INTO `accsubject` VALUES ('7102', 2);
INSERT INTO `accsubject` VALUES ('7104', 2);
INSERT INTO `accsubject` VALUES ('7105', 3);
INSERT INTO `accsubject` VALUES ('7111', 2);
INSERT INTO `accsubject` VALUES ('7112', 3);
INSERT INTO `accsubject` VALUES ('7301', 1);
INSERT INTO `accsubject` VALUES ('7302', 3);
INSERT INTO `accsubject` VALUES ('7303', 1);
INSERT INTO `accsubject` VALUES ('7305', 3);
INSERT INTO `accsubject` VALUES ('7311', 3);
INSERT INTO `accsubject` VALUES ('7312', 1);
INSERT INTO `accsubject` VALUES ('7329', 3);
INSERT INTO `accsubject` VALUES ('8101', 1);
select *
from accsubject


id
field


1101
3

1102
1

1103
1

1104
3

1105
3

1141
1

1144
1

1149
3

1178
1

1201
1

1202
2

1203
2

1251
3

1252
1

1253
1

1257
3

1259
1

1281
3

1289
1

1402
1

1403
1

1428
3

1429
1

1444
1

1457
1

1501
1

1502
2

1521
3

1522
2

1541
1

1542
2

1551
3

1552
2

1821
3

1824
3

1825
3

1827
3

2102
2

2109
2

2141
2

2144
2

2147
2

2148
2

2154
2

2155
2

2156
2

2157
2

2175
2

2176
3

2177
2

2178
2

2251
3

2253
2

2259
2

2281
2

2504
2

2821
2

2824
2

2825
2

3101
2

3311
2

3312
2

3317
2

3318
2

3319
2

4101
2

4102
1

4103
1

4104
3

4105
2

4106
2

5101
1

5102
1

5103
3

5201
1

5203
2

5204
2

5205
2

6201
3

6203
3

6204
3

6205
3

6206
1

6207
3

6208
1

6209
1

6210
3

6211
1

6212
1

6213
3

6214
1

6215
3

6216
3

6217
1

6218
1

6219
3

6221
1

6222
3

6223
1

6224
1

6225
3

6226
1

6227
1

6228
3

6229
1

6245
3

6248
1

6249
3

7101
2

7102
2

7104
2

7105
3

7111
2

7112
3

7301
1

7302
3

7303
1

7305
3

7311
3

7312
1

7329
3

8101
1


select TempA.id,TempB.id,TempC.id
from (
select ROW_NUMBER() OVER (order by id) as row_num 
from accsubject
) as TempX
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=1
) as TempA on TempA.row_num=TempX.row_num
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=2
) as TempB on TempB.row_num=TempX.row_num
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=3
) as TempC on TempC.row_num=TempX.row_num
where 1=1
and not (
TempA.id=''
and TempB.id=''
and TempC.id=''
)


id
id
id


1102
1202
1101

1103
1203
1104

1141
1502
1105

1144
1522
1149

1178
1542
1251

1201
1552
1257

1252
2102
1281

1253
2109
1428

1259
2141
1521

1289
2144
1551

1402
2147
1821

1403
2148
1824

1429
2154
1825

1444
2155
1827

1457
2156
2176

1501
2157
2251

1541
2175
4104

4102
2177
5103

4103
2178
6201

5101
2253
6203

5102
2259
6204

5201
2281
6205

6206
2504
6207

6208
2821
6210

6209
2824
6213

6211
2825
6215

6212
3101
6216

6214
3311
6219

6217
3312
6222

6218
3317
6225

6221
3318
6228

6223
3319
6245

6224
4101
6249

6226
4105
7105

6227
4106
7112

6229
5203
7302

6248
5204
7305

7301
5205
7311

7303
7101
7329

7312
7102
null

8101
7104
null

null
7111
null
id	field
1101	3
1102	1
1103	1
1104	3
1105	3
1141	1
1144	1
1149	3
1178	1
1201	1
1202	2
1203	2
1251	3
1252	1
1253	1
1257	3
1259	1
1281	3
1289	1
1402	1
1403	1
1428	3
1429	1
1444	1
1457	1
1501	1
1502	2
1521	3
1522	2
1541	1
1542	2
1551	3
1552	2
1821	3
1824	3
1825	3
1827	3
2102	2
2109	2
2141	2
2144	2
2147	2
2148	2
2154	2
2155	2
2156	2
2157	2
2175	2
2176	3
2177	2
2178	2
2251	3
2253	2
2259	2
2281	2
2504	2
2821	2
2824	2
2825	2
3101	2
3311	2
3312	2
3317	2
3318	2
3319	2
4101	2
4102	1
4103	1
4104	3
4105	2
4106	2
5101	1
5102	1
5103	3
5201	1
5203	2
5204	2
5205	2
6201	3
6203	3
6204	3
6205	3
6206	1
6207	3
6208	1
6209	1
6210	3
6211	1
6212	1
6213	3
6214	1
6215	3
6216	3
6217	1
6218	1
6219	3
6221	1
6222	3
6223	1
6224	1
6225	3
6226	1
6227	1
6228	3
6229	1
6245	3
6248	1
6249	3
7101	2
7102	2
7104	2
7105	3
7111	2
7112	3
7301	1
7302	3
7303	1
7305	3
7311	3
7312	1
7329	3
8101	1