MySQL 8.0

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

select version();


version()


8.0.22


CREATE TABLE Tmp(
	ID INT
	,Emp varchar(10)
	,Level varchar(10)
);

INSERT INTO Tmp(ID, Emp, Level)
VALUES (1,'A','甲')
,(2,'A','丙')
,(3,'D','甲')
,(4,'C','丙')
,(5,'X','乙')
,(6,'A','甲')
,(7,'D','甲')
,(8,'C','乙')
,(9,'X','甲')
,(10,'D','')
,(11,'C','乙')
,(12,'C','乙');

WITH totalLevel AS (
  SELECT Level,
         COUNT(Level) AS LevelCount
  FROM Tmp
  GROUP BY Level
), EmployeeLevel AS (
  SELECT Emp,
         Level,
         CAST(COUNT(Level) AS float) AS LevelCount
  FROM Tmp
  GROUP BY Emp,
           Level
)
SELECT EmployeeLevel.Emp,
       EmployeeLevel.Level,
       EmployeeLevel.LevelCount,
       totalLevel.LevelCount AS TotalLevelCount,
       CONCAT(EmployeeLevel.LevelCount / totalLevel.LevelCount * 100, '%') AS Rate
FROM EmployeeLevel
INNER JOIN totalLevel
ON EmployeeLevel.Level = totalLevel.Level
WHERE EmployeeLevel.Level != ''
ORDER BY EmployeeLevel.Level DESC, Emp


Emp
Level
LevelCount
TotalLevelCount
Rate


A
甲
2
5
40%

D
甲
2
5
40%

X
甲
1
5
20%

C
乙
3
4
75%

X
乙
1
4
25%

A
丙
1
2
50%

C
丙
1
2
50%


WITH LevelCount AS (
  SELECT Emp, 
         Level,
         COUNT(Level) AS LevelCount,
         SUM(COUNT(Level)) OVER(PARTITION BY Level) AS TotalLevelCount
  FROM Tmp
  GROUP BY Emp, Level
)
SELECT LevelCount.Emp,
       LevelCount.Level,
       LevelCount.LevelCount,
       LevelCount.TotalLevelCount,
       CONCAT(CAST(LevelCount.LevelCount AS float) / LevelCount.TotalLevelCount * 100, '%') AS Rate
FROM LevelCount
WHERE Level != ''
ORDER BY Level DESC, Emp


Emp
Level
LevelCount
TotalLevelCount
Rate


A
甲
2
5
40%

D
甲
2
5
40%

X
甲
1
5
20%

C
乙
3
4
75%

X
乙
1
4
25%

A
丙
1
2
50%

C
丙
1
2
50%


SELECT Emp, 
       Level,
       COUNT(Level) AS LevelCount,
       SUM(COUNT(Level)) OVER(PARTITION BY Level) AS TotalLevelCount,
       CONCAT(CAST(COUNT(Level) AS float) / SUM(COUNT(Level)) OVER(PARTITION BY Level) * 100, '%') AS Rate
FROM Tmp
WHERE Level != ''
GROUP BY Emp, Level
ORDER BY Level DESC


Emp
Level
LevelCount
TotalLevelCount
Rate


A
甲
2
5
40%

D
甲
2
5
40%

X
甲
1
5
20%

X
乙
1
4
25%

C
乙
3
4
75%

A
丙
1
2
50%

C
丙
1
2
50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
X	乙	1	4	25%
C	乙	3	4	75%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
X	乙	1	4	25%
C	乙	3	4	75%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%

Emp	Level	LevelCount	TotalLevelCount	Rate
A	甲	2	5	40%
D	甲	2	5	40%
X	甲	1	5	20%
X	乙	1	4	25%
C	乙	3	4	75%
A	丙	1	2	50%
C	丙	1	2	50%