SQL Server 2017

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

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



SET LANGUAGE 繁體中文;
CREATE TABLE Tmp(
	ID INT
	,Emp Nvarchar(10)
	,Level Nvarchar(10)
);

INSERT INTO Tmp(ID, Emp, Level)
VALUES (1,'A',N'甲')
,(2,'A',N'丙')
,(3,'D',N'甲')
,(4,'C',N'丙')
,(5,'X',N'乙')
,(6,'A',N'甲')
,(7,'D',N'甲')
,(8,'C',N'乙')
,(9,'X',N'甲')
,(10,'D',N'')
,(11,'C',N'乙')
,(12,'C',N'乙');
已將語言設定變更為 繁體中文。
12 rows affected

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
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%

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%

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%
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%
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%
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%
C	乙	3	4	75%
X	乙	1	4	25%
A	丙	1	2	50%
C	丙	1	2	50%