續上一篇 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;