iT邦幫忙

1

[MySQL]以各校各科各學期做分群,要取得實際(不重複)學生數量

續上一篇 https://ithelp.ithome.com.tw/questions/10195019
我寫了一個範例來說明我要的部分,以及已經知道不會成功但不知怎麼改寫的sql
目的:以各校各科各學期做分群,要取得實際(不重複)學生數量

想詢問各位前輩要怎麼改寫比較好??

CREATE TEMPORARY TABLE `class`(
    `class_id` INT NOT NULL,
    `class_schId` INT, -- 學校
    `class_year` INT, -- 學年度
    `class_depId` INT, -- 科系
    `class_grade` char(10), -- 年級
    `class_room` char(10), -- 班級
    `class_studentNbr` INT, -- 學生數

    PRIMARY KEY(`class_id`)
);
insert into `class`values (1,123,108,456,"1",'A',30);
insert into `class`values (2,123,108,456,'2','B',30);

CREATE TEMPORARY TABLE `plan`(
    `plan_id` INT NOT NULL,
    `plan_schId` INT, -- 學校
    `plan_year` INT, -- 學年度
    `plan_depId` INT, -- 科系
    `plan_semester` INT, -- 學期
    `plan_name` text, -- 計畫名稱
    `class_id` INT, -- 班級id
    `plan_fund` INT, -- 經費

    PRIMARY KEY(`plan_id`)
);
insert into `plan`values (1,123,108,456,1,'P1',1,100);
insert into `plan`values (2,123,108,456,1,'P1',2,200);
insert into `plan`values (3,123,108,456,1,'P3',1,300);
insert into `plan`values (4,123,108,456,2,'P2',1,400);
insert into `plan`values (5,123,108,456,2,'P2',2,500);

-- ##############################
-- 以下開始撈取資料(以各校各科各學期做分群,要取得不重複學生數量)
select plan_schId,plan_depId,plan_semester
,group_concat(distinct plan_name) '計畫名稱'
,group_concat(distinct concat(class_grade,class_room)) '班級(不重複)'
,group_concat(distinct c1.class_id) '班級id(不重複)'
,sum(plan_fund) '經費'
,sum(class_studentNbr) '學生人數(會重複計算)'
,sum(distinct class_studentNbr) '學生人數(排除過頭了)'
,(
    -- 有一個想法是先取得不重複班級id後再去抓去這些的學生數量,或是要怎麼實現比較好
    -- (ps.我知道這裡會出錯,in只能用在陣列,這裡只是想表達我的想法)
   select sum(class_studentNbr) from class
   where class_id in (group_concat(distinct c1.class_id))
) '學生人數(不重複)'

from plan
inner join class c1 on plan.class_id = c1.class_id
group by plan_schId,plan_depId,plan_semester

預期結果

學校 學年度 科系 學期 計畫(不重複) 經費 班級(不重複) 學生人數(不重複)
123 108 456 1 P1,P3 600 1A,2B 60
123 108 456 2 P2 900 1A,2B 60

================================

感恩firecold的協助,目前做法先對plan先做預處理,不過這樣有個限制與問題

  1. 內部 b表的group欄位要完全包含外面select的group欄位,簡單說就是集合域的問題,
    • group比較多欄位的要完全包含group比較少的欄位
    • group比較多欄位的先處裡後再join
  2. group_concat distinct 的欄位有可能會多(ex.計畫名稱),不過這是另一個問題了(會另開問題)

預覽:http://sqlfiddle.com/#!9/86d0f4/8/0

select a.class_schId '學校',a.class_year '學年度',b.plan_semester '學期'
,group_concat(distinct plan_name) '計畫名稱'
,sum(plan_fund) '經費'
,group_concat(distinct concat(class_grade,class_room)) '班級(不重複)',
sum(a.class_studentNbr) '學生人數'
from class a, (
  select plan_id, plan_schId, plan_year, plan_depId, plan_semester, class_id
  ,group_concat(distinct plan_name) as plan_name
  ,sum(plan_fund) plan_fund
  from plan 
  group by plan_schId, plan_year, plan_depId, plan_semester,class_id
) as b
where a.class_schId = b.plan_schId
and a.class_year = b.plan_year
and a.class_id = b.class_id
group by a.class_schId, a.class_depId, b.plan_semester;

舜~ iT邦好手 1 級 ‧ 2019-08-27 15:30:21 檢舉
有時在想,有沒有BI工具直接讓我沒這個煩惱,每次都要手刻sql有點費時 XD
firecold iT邦新手 3 級 ‧ 2019-08-27 17:30:56 檢舉
coder的快樂往往就是這麼樸實無華且枯燥

1 個回答

2
firecold
iT邦新手 3 級 ‧ 2019-08-27 15:03:48
最佳解答

先把plan讀出來篩選
唯一的問題是計畫名稱那邊會重複
計畫名稱跟人數其實都可以用程式解決
在看自己怎麼決定吧
不一定說都要在sql處理

http://sqlfiddle.com/#!9/2f558a/65/0

select a.class_schId '學校',a.class_year '學年度',b.plan_semester '學期'
,group_concat(distinct plan_name) '計畫名稱'
,group_concat(distinct concat(class_grade,class_room)) '班級(不重複)',
sum(a.class_studentNbr) '學生人數'
from class a, (
  select plan_id, plan_schId, plan_year, plan_depId, plan_semester, class_id
  ,group_concat(distinct plan_name) as plan_name
  from plan 
  group by class_id,plan_semester
) as b
where a.class_schId = b.plan_schId
and a.class_year = b.plan_year
and a.class_id = b.class_id
group by a.class_schId, a.class_depId, b.plan_semester;

舜~ iT邦好手 1 級 ‧ 2019-08-27 15:24:20 檢舉

感恩,我沒想到還能這麼處裡~~~3Q
不過這樣有個問題,如果plan再多一個經費,要取得經費總額,這樣會有問題,若再多join一次plan來解決?晚點我實驗一下

firecold iT邦新手 3 級 ‧ 2019-08-27 16:29:41 檢舉

select plan_id, plan_schId, plan_year, plan_depId, plan_semester, class_id
,group_concat(distinct plan_name) as plan_name
from plan
group by class_id,plan_semester

這段在多抓經費欄位出來就好
不過我建議統計的部份用程式算比較好

舜~ iT邦好手 1 級 ‧ 2019-08-28 10:12:08 檢舉

臨時表會遇到一個 Can't reopen table 的問題~
經費 如果統計部份改用程式算的話,代表要額外查詢一次,因為group已經把一些資料篩選掉了。不過查詢的欄位越來越多,每個欄位又要客製化的話,或許額外查詢在程式端合併或許是個好方法,不過如何合併就是個問題~

我要發表回答

立即登入回答