iT邦幫忙

0

請問SQL 要如何下才好

sql

我的table如下
https://ithelp.ithome.com.tw/upload/images/20201202/20130156SN350IojS4.png

我想要得到人員的等級比例如下
https://ithelp.ithome.com.tw/upload/images/20201202/20130156TDCobPVimt.png
SQL 指令要如何下比較好呢?

你的結果應該是這樣吧??
A 甲 2 40%
D 甲 2 40%
X 甲 1 20%

X 乙 1 25%
C 乙 3 75%

A 丙 1 50%
C 丙 1 50%
shenglee iT邦新手 5 級 ‧ 2020-12-02 16:11:34 檢舉
嗯 對,或是說先取得甲的總數,再取各人員甲的數量,比例再由後台AP去算
0
rogeryao
iT邦大師 3 級 ‧ 2020-12-02 17:08:14
最佳解答

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

看更多先前的回應...收起先前的回應...
shenglee iT邦新手 5 級 ‧ 2020-12-08 17:22:27 檢舉

rogeryao 大大您好,感謝你的PostgreSQL,讓我很快得出各人員等級,因為我資質駑鈍,還有下列一個問題可幫忙嗎? 新增一個如下的部門table, 然後輸出結果為 部門,等級,數量,百分比 ,不知道這樣要如何下SQL指令呢? 感謝!
https://ithelp.ithome.com.tw/upload/images/20201208/20130156SboO7eWFXk.png

rogeryao iT邦大師 3 級 ‧ 2020-12-08 18:09:12 檢舉

假設先前的邏輯不變,只是顯示多了部門欄位

CREATE TABLE YY (
S varchar(40) NULL, --[部門]
T varchar(40) NULL); --[人員]

INSERT INTO YY (S,T)
VALUES 
('ㄅ','A'),
('ㄆ','D'),
('ㄇ','C'),
('ㄈ','X');
SELECT YY.S 部門,DD.R 等級,DD.Q 人員,DD.M 數量,
-- EE.N,
TO_CHAR(ROUND(DD.M*100/EE.N,2),'99')||'%' 百分比
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
LEFT JOIN YY ON YY.T=DD.Q
ORDER BY CASE WHEN DD.R ='甲' THEN 1
WHEN DD.R ='乙' THEN 2
WHEN DD.R ='丙' THEN 3
ELSE 4 END,DD.Q

Demo

shenglee iT邦新手 5 級 ‧ 2020-12-08 22:40:57 檢舉

太感謝了,如果輸出結果為 部門,等級,該部門等級數量,該部門等級數量百分比
不需要人員,因為我想人員做一個報表,部門也單獨做一個報表
這樣SQL指令好下嗎?

rogeryao iT邦大師 3 級 ‧ 2020-12-08 22:45:52 檢舉

看無,把你要的結果貼出來吧

shenglee iT邦新手 5 級 ‧ 2020-12-09 00:18:18 檢舉

感謝大大
https://ithelp.ithome.com.tw/upload/images/20201209/20130156yjPUprBM9i.png

rogeryao iT邦大師 3 級 ‧ 2020-12-09 09:36:14 檢舉
CREATE TABLE YY (
S varchar(40) NULL, --[部門]
T varchar(40) NULL); --[人員]

INSERT INTO YY (S,T)
VALUES 
('ㄅ','A'),
('ㄅ','D'),
('ㄆ','C'),
('ㄇ','X');
SELECT YY.S 部門,XX.R 等級,
COUNT(1) AS 數量,
TO_CHAR(ROUND(COUNT(1)*100/SUM(COUNT(1)) OVER (PARTITION BY XX.R),2),'99')||'%' 百分比
FROM XX
LEFT JOIN YY ON YY.T=XX.Q
WHERE XX.R IS NOT NULL
GROUP BY YY.S,XX.R
ORDER BY CASE WHEN XX.R ='甲' THEN 1
WHEN XX.R ='乙' THEN 2
WHEN XX.R ='丙' THEN 3
ELSE 4 END,YY.S

Demo

shenglee iT邦新手 5 級 ‧ 2020-12-09 12:48:33 檢舉

真的太感謝了!

shenglee iT邦新手 5 級 ‧ 2020-12-09 16:06:41 檢舉

不好意思又有一個問題,如果各部門都要統計該部門各等級的%,例如ㄅ部門甲等級佔ㄅ部門80%,丙等級佔ㄅ部門20%,乙等級在ㄅ部門是0%,如下列圖所示
https://ithelp.ithome.com.tw/upload/images/20201209/20130156ycpg65epZO.png

https://ithelp.ithome.com.tw/upload/images/20201209/20130156QxYMpB5uVx.png
我想了好久,我還是寫不出指令
可以再麻煩你嗎?謝謝

rogeryao iT邦大師 3 級 ‧ 2020-12-09 16:41:32 檢舉

你的部門是不是有另一個 Table ?

shenglee iT邦新手 5 級 ‧ 2020-12-09 16:59:34 檢舉

YY 這個

shenglee iT邦新手 5 級 ‧ 2020-12-09 17:03:27 檢舉

還是要再建一個部門的新Table也可以

rogeryao iT邦大師 3 級 ‧ 2020-12-09 19:27:01 檢舉
CREATE TABLE YY (
S varchar(40) NULL, --[部門]
T varchar(40) NULL); --[人員]

INSERT INTO YY (S,T)
VALUES 
('ㄅ','A'),
('ㄅ','D'),
('ㄆ','C'),
('ㄇ','X'),
('ㄉ','Y');
SELECT MM.S 部門,LL.R 等級,COALESCE(UU.NUM,0) 數量,
COALESCE(UU.PERCENT,'  0'||'%') 百分比
FROM 
(SELECT DISTINCT YY.S
FROM YY) AS MM
FULL JOIN (SELECT DISTINCT XX.R
FROM XX
WHERE XX.R IS NOT NULL) AS LL ON 1=1
LEFT JOIN (
SELECT YY.S,XX.R,
COUNT(1) AS NUM,
TO_CHAR(ROUND(COUNT(1)*100/SUM(COUNT(1)) OVER (PARTITION BY YY.S),2),'99')||'%' PERCENT
FROM XX
LEFT JOIN YY ON YY.T=XX.Q
WHERE XX.R IS NOT NULL
GROUP BY YY.S,XX.R
) AS UU ON UU.S=MM.S AND UU.R=LL.R
ORDER BY 
CASE WHEN LL.R ='甲' THEN 1
WHEN LL.R ='乙' THEN 2
WHEN LL.R ='丙' THEN 3
ELSE 4 END,
CASE WHEN MM.S='ㄅ' THEN 1
WHEN MM.S='ㄆ' THEN 2
WHEN MM.S='ㄇ' THEN 3
WHEN MM.S='ㄉ' THEN 4
ELSE 5 END

Demo

shenglee iT邦新手 5 級 ‧ 2020-12-09 20:37:48 檢舉

謝謝,厲害!請問要如何才能像你們這樣游刃有餘呢?有什麼推薦的書或網站可看嗎?

rogeryao iT邦大師 3 級 ‧ 2020-12-09 20:45:44 檢舉

問題一 :
多看,多聽,多學
問題二 :
1.網站 : 本站搜尋問答"SQL"
2.Google 搜尋關鍵字

2
純真的人
iT邦高手 1 級 ‧ 2020-12-02 16:20:50

那你參考看看~這是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
)

https://ithelp.ithome.com.tw/upload/images/20201202/200613691vtufAeBQ9.png

0
leeihsing0127
iT邦新手 5 級 ‧ 2020-12-02 16:37:29

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

0

以下請服用,這是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
0
chris1101
iT邦見習生 ‧ 2020-12-02 17:21:27
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
0
通靈亡
iT邦研究生 3 級 ‧ 2020-12-02 17:38:36

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
shenglee iT邦新手 5 級 ‧ 2020-12-02 20:04:29 檢舉

iT邦的神人真多,真的太佩服各位大大,好想選每一個為最佳解答,怎麼辦才好呢?

通靈亡 iT邦研究生 3 級 ‧ 2020-12-02 21:42:45 檢舉

不用選我,我只是一個隨處通靈的亡靈
若通靈有成,便幫助別人免於成亡
若通靈不成,便隨問題等待成亡

如真的要選,選擇讓你最能理解的回答
選擇不出來,把所有參與回答的暱稱用SQL寫個亂數產生決定吧。

SQL IN 通靈 ppap.....
好吧,開玩笑的。

如果真有選擇問題
也跳過我吧。讓你少一點選擇。剩下的人好像剩6人。
用骰子賭一下吧。哈哈哈。

1
一級屠豬士
iT邦大師 1 級 ‧ 2020-12-02 21:34:33
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)

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

這次來得有點慢喔!!

0
pojen
iT邦新手 1 級 ‧ 2020-12-03 09:18:57

甲骨文的寫法:

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;

https://ithelp.ithome.com.tw/upload/images/20201203/20033353efUsN6JK9A.png

如果你沒有 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

https://ithelp.ithome.com.tw/upload/images/20201203/20033353gj0tHmte9q.png

shenglee iT邦新手 5 級 ‧ 2020-12-03 15:19:08 檢舉

太感謝了,
如果用PostgreSQL,語法會差別很大嗎?我目前系統是用PostgreSQL
如果再加入每個人員的等級數量如下。沒想到SQL語法這麼強,可以直接列出來

A 甲 2 40%
D 甲 2 40%
X 甲 1 20%

X 乙 1 25%
C 乙 3 75%

A 丙 1 50%
C 丙 1 50%

通靈亡 iT邦研究生 3 級 ‧ 2020-12-03 16:12:26 檢舉

shenglee
PostgreSQL 可以用樓上屠豬大大的CTE JOIN的寫法

也可以用我和pojen的 sum(count(*)) over (partition by egrade) as eratio,Window Function作法,相當於Oracle PL-SQL的ratio_to_report

pojen iT邦新手 1 級 ‧ 2020-12-03 23:02:19 檢舉

PostgreSQL 應該是最接近 Oracle 的. 我的第二個寫法是標準 ANSI. 應該能在大多數的資料庫上執行.

是的 ratio_to_report 與 over (xxx) 是很標準的 Window Function. 根據需求, 各有有用處的地方.

我要發表回答

立即登入回答