MSSQL

CREATE TABLE XX (
P int NULL , --[編號]
Q varchar(40) NULL , --[人員]
R nvarchar(40) NULL); --[等級]

INSERT INTO XX (P,Q,R)
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',NULL),
(11,'C',N'乙'),	
(12,'C',N'乙');

SELECT DD.Q,DD.R,
DD.M,
-- EE.N,
CONVERT(VARCHAR(8),ROUND(DD.M*100/EE.N,2))+'%' AS 'PERCENT'
FROM (SELECT Q,R,
COUNT(1) AS M
FROM XX
GROUP BY Q,R) AS DD
INNER JOIN (
SELECT R,SUM(1) AS N
FROM XX
GROUP BY R
) AS EE ON EE.R=DD.R
ORDER BY CASE WHEN DD.R =N'甲' THEN 1
WHEN DD.R =N'乙' THEN 2
WHEN DD.R =N'丙' THEN 3
ELSE 4 END,DD.Q

Demo

PostgreSQL

CREATE TABLE XX (
P int NULL , --[編號]
Q varchar(40) NULL , --[人員]
R varchar(40) NULL); --[等級]

INSERT INTO XX (P,Q,R)
VALUES 
(1,'A','甲'),
(2,'A','丙'),
(3,'D','甲'),
(4,'C','丙'),
(5,'X','乙'),
(6,'A','甲'),
(7,'D','甲'),
(8,'C','乙'),
(9,'X','甲'),
(10,'D',NULL),
(11,'C','乙'),	
(12,'C','乙');

SELECT DD.Q,DD.R,
DD.M,
-- EE.N,
TO_CHAR(ROUND(DD.M*100/EE.N,2),'99')||'%' PERCENT
FROM (SELECT Q,R,
COUNT(1) AS M
FROM XX
GROUP BY Q,R) AS DD
INNER JOIN (
SELECT R,SUM(1) AS N
FROM XX
GROUP BY R
) AS EE ON EE.R=DD.R
ORDER BY CASE WHEN DD.R ='甲' THEN 1
WHEN DD.R ='乙' THEN 2
WHEN DD.R ='丙' THEN 3
ELSE 4 END,DD.Q

Demo

那你參考看看~這是MSSQL的~

declare @Tmp table(
	ID int
	,Emp Nvarchar(10)
	,[Level] Nvarchar(10)
)

insert into @Tmp
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','乙')


select Emp
,[Level]
,Convert(varchar(50),Convert(int,Convert(numeric(18, 2),Count(0))
/ Convert(numeric(18, 2),(
	select Count(0)
	from @Tmp b
	where [Level] <> ''
	and a.Level = b.Level
	group by [Level]
)) * 100)) + '%' Rate
from @Tmp a
where [Level] <> ''
group by Emp
,[Level]
order by (
	case [Level]
	when '甲' then 0
	when '乙' then 1
	when '丙' then 2
	else 3
	end
)

mysql 不知道這樣是否ok
rank => 等級, code => 人員

select t1.rank, t1.code, count() / rank_sum as percent
from table as t1
join (
select rank, count() as rank_sum
from table
where rank <> ''
group by rank
) as t2 on t2.rank = t1.rank
where t1.rank <> ''
group by t1.code, t1.rank
order by t1.rank desc , t1.code

以下請服用，這是mysql。可以再寫的更簡短。不過先這樣

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `p` varchar(1) NOT NULL,
  `lv` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 傾印資料表的資料 `test`
--

INSERT INTO `test` (`id`, `p`, `lv`) 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', '乙');

--
-- 資料表索引 `test`
--
ALTER TABLE `test`
  ADD PRIMARY KEY (`id`);

SELECT d.lv,d.p,concat(round((d.sumNum/d.allSum)*100),'%') AS v FROM
(
	SELECT test.lv,test.p,count(*) AS sumNum,sumDb.sumNum AS allSum FROM `test` 
	LEFT JOIN (SELECT lv,count(*) AS sumNum FROM `test` GROUP BY lv) AS sumDb ON (test.lv=sumDb.lv)
	WHERE test.lv!=''
	GROUP BY concat(test.lv,test.p)  
	ORDER BY `test`.`lv`  DESC , `test`.`p` ASC
) AS d

declare @Tmp table(
	ID int
	,Emp Nvarchar(10)
	,[Level] Nvarchar(10)
)

insert into @Tmp
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','乙')

select t1.Emp
,t1.Level 
,Convert(varchar(8),round(count(0)*100/t2.total,2))+'%' as Rate 
from @Tmp t1 left join 
(
select Level,count(*) as total from @Tmp group by Level
) as t2
on t1.Level=t2.Level 
where t1.Level<>''
group by t1.Emp,t1.Level,t2.total
order by (
 case t1.Level
 when '甲' then 0
 when '乙' then 1
 when '丙' then 2
 else 3
 end
),t1.Emp

SQL Server Demo
MySQL Demo

CTE JOIN (No Window Function)

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

Window Function Only

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

CTE + Window Function

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

create table it201202 (
  id int not null
, name text not null
, level text not null
);

insert into it201202
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','乙');

commit;

with t1 as (
select level
     , count(*) lvlcnt
  from it201202
 where level <> ''
 group by level
), t2 as (
select name
     , level
     , count(*) cnt
  from it201202
 where level <> ''
 group by name, level
), t3 (level, ord)  as (
values
('甲', 1), ('乙', 2), ('丙', 3)
)
select t2.name
     , t2.level
     , round(t2.cnt * 100.0 / t1.lvlcnt)::text || '%' percentage
  from t1
  join t2
    on t1.level = t2.level
  join t3
    on t2.level = t3.level
 order by t3.ord, t2.name;

 name | level | percentage 
------+-------+------------
 A    | 甲    | 40%
 D    | 甲    | 40%
 X    | 甲    | 20%
 C    | 乙    | 75%
 X    | 乙    | 25%
 A    | 丙    | 50%
 C    | 丙    | 50%
(7 rows)

-- 跟前面幾位大大類似的方法.

甲骨文的寫法:

select ename,egrade,ratio_to_report(ecount) over (partition by egrade)
from (
select ename,egrade,count(*) ecount
from pojen
group by ename,egrade
)
order by 2,1;

如果你沒有 ratio_to_report, 也可寫土法煉鋼版. (一樣的, 個人美感問題. 中間那行可以去掉,只是為了解釋而已)

select ename,egrade
,count(*) ecount,sum(count(*)) over (partition by egrade) etotal
, count(*) /sum(count(*)) over (partition by egrade) as eratio
from pojen
group by ename,egrade