iT邦幫忙

0

[MySQL/PHP]請教報表SQL如何下才不會撈到重複資料(已解決)

問題:
想請教有寫過報表的前輩們,在當前結構下, 學生人數 與 業師人數 不知道怎麼下sql才不會統計到重覆的資料?? 或是前輩們通常會怎麼處理相對複雜的報表??

葉師數量 麻煩的是跨欄重複的問題,在該筆資料wt1的業師,在其他筆資料可能是wt3,但只能算1個(同一個人)
==> 感謝小魚與其他大大們集思廣益,最後我另外寫一個方法來處理陣列中不重複數量CountArrayDistinct,然後全部CONCAT起來後傳入該方法即可~~

select CountArrayDistinct( CONCAT(GROUP_CONCAT(DISTINCT wt1),',',GROUP_CONCAT(DISTINCT wt2),','...)
ps.用GROUP_CONCAT DISTINCT是為了減少資料傳入方法的大小

學生人數 麻煩的是要配合學校代號、科別、年級、班級名稱 才能知道是不是同一班,計算班級還好,concat成一個字串取不重複次數就好,但人數就頭疼了...
==> 採用rogeryao大大的方法解決(另外計算好後再join起來)

需求:
要統計各個年度的校數量、班級數量、學生人數、群別數量、科別數量、課程數量、業師人數、總經費,並依照年度在網頁上列出表格(目前只差SQL)~~

資料庫資料見下方,用3間學校的假資料做代表~

資料庫內容:
ps_year:學年度
sch_code:學校代號
grpname:群別全名
depname:科別全名
coursename:課程名稱
grade:年級
classname:班級名稱
stuno:學生人數
ttlmoney:經費
wt1:業師1
wt2:業師2
wt3:業師3
wt4:業師4
wt5:業師5
wt6:業師6
wt7:業師7

ps_year sch_code grpname depname coursename grade classname stuno ttlmoney wt1 wt2 wt3 wt4 wt5 wt6 wt7
106 593401 動力機械群 汽車科 噴射引擎實習 3 40 22400 丁信燦 郭明界 蔡崑宗
106 593401 動力機械群 汽車科 噴射引擎實習 3 42 22400 丁信燦 郭明界 蔡崑宗
106 593401 動力機械群 汽車科 底盤實習 2 36 22400 江懿庭
106 593401 動力機械群 汽車科 底盤實習 2 37 22400 江懿庭
106 593401 動力機械群 汽車科 汽車綜合實習2 3 40 16800 余坤榮
106 593401 動力機械群 汽車科 汽車綜合實習2 3 42 135436 余坤榮
106 593401 土木與建築群 建築科 建築製圖實習 3 37 16800 李東隆
106 593401 土木與建築群 建築科 建築製圖實習 3 37 16800 李東隆
106 593401 設計群 金屬工藝科 產品設計實習II 3 40 33600 林秀娟 林彥銘 里歐斯
106 593401 設計群 金屬工藝科 產品設計實習I 3 40 33600 洪明輝
106 593401 動力機械群 汽車科 電系實習 2 36 16800 黃士源
106 593401 動力機械群 汽車科 電系實習 2 37 136725 黃士源
106 593401 動力機械群 汽車科 車輛空調檢修實習 2 36 16800 蔡榮昌
106 593401 動力機械群 汽車科 車輛空調檢修實習 2 37 16800 蔡榮昌
107 033408 家政群 流行服飾科 立體裁剪 2 35 18046 何佩珊
107 033408 家政群 流行服飾科 立體裁剪 2 36 18046 何佩珊
107 033408 家政群 流行服飾科 美容實務 2 35 12446 吳憶萌
107 033408 家政群 流行服飾科 美容實務 2 36 12446 吳憶萌
107 033408 家政群 家政科 美容實務 2 40 11522 李芊慧
107 033408 家政群 家政科 美顏 2 40 11522 李芊慧
107 033408 家政群 家政科 美容實務 2 40 11522 李芊慧
107 033408 家政群 家政科 美顏 2 40 11522 李芊慧
107 033408 商業與管理群 資料處理科 資料庫網站設計Ⅰ 2 41 139732 孫鳴遠
107 033408 商業與管理群 資料處理科 資料庫網站設計Ⅱ 2 41 136125 孫鳴遠
107 033408 家政群 家政科 服飾造型Ⅱ 2 40 18326 潘美伶
107 033408 家政群 家政科 服飾造型I 2 40 18326 潘美伶
107 033408 家政群 家政科 服飾造型Ⅱ 2 40 18326 潘美伶
107 033408 家政群 家政科 服飾造型I 2 40 18326 潘美伶
107 033408 家政群 流行服飾科 成衣製作 3 35 18046 謝雯玲
107 013434 餐旅群 餐飲管理科 餐飲實務I 3 40 8000 陳煥堂 張增鵬
107 013434 餐旅群 餐飲管理科 餐飲實務I 3 40 8000 陳煥堂 張增鵬
107 013434 餐旅群 餐飲管理科 西餐烹調I II 2 40 6400 蔡斌翰 葉承欽
107 013434 餐旅群 餐飲管理科 西餐烹調I II 2 40 6400 蔡斌翰 葉承欽
107 013434 餐旅群 餐飲管理科 西餐烹調I II 2 40 44950 蔡斌翰 葉承欽
107 013434 餐旅群 餐飲管理科 西餐烹調I II 2 40 34644 蔡斌翰 葉承欽

SQL:

CREATE TEMPORARY TABLE `mytable`(
`ps_year` VARCHAR(50)
,`sch_code` VARCHAR(50)
,`grpname` VARCHAR(50)
,`depname` VARCHAR(50)
,`coursename` VARCHAR(50)
,`grade` VARCHAR(50)
,`classname` VARCHAR(50)
,`stuno` int
,`ttlmoney` int
,`wt1` VARCHAR(50)
,`wt2` VARCHAR(50)
,`wt3` VARCHAR(50)
,`wt4` VARCHAR(50)
,`wt5` VARCHAR(50)
,`wt6` VARCHAR(50)
,`wt7` VARCHAR(50)
);
insert into mytable
values
(106,'593401','動力機械群','汽車科','噴射引擎實習','3','孝','40','22400','丁信燦','郭明界','蔡崑宗','','','','')
,(106,'593401','動力機械群','汽車科','噴射引擎實習','3','忠','42','22400','丁信燦','郭明界','蔡崑宗','','','','')
,(106,'593401','動力機械群','汽車科','底盤實習','2','孝','36','22400','江懿庭','','','','','','')
,(106,'593401','動力機械群','汽車科','底盤實習','2','忠','37','22400','江懿庭','','','','','','')
,(106,'593401','動力機械群','汽車科','汽車綜合實習2','3','孝','40','16800','余坤榮','','','','','','')
,(106,'593401','動力機械群','汽車科','汽車綜合實習2','3','忠','42','135436','余坤榮','','','','','','')
,(106,'593401','土木與建築群','建築科','建築製圖實習','3','孝','37','16800','李東隆','','','','','','')
,(106,'593401','土木與建築群','建築科','建築製圖實習','3','忠','37','16800','李東隆','','','','','','')
,(106,'593401','設計群','金屬工藝科','產品設計實習II','3','忠','40','33600','林秀娟','林彥銘','里歐斯','','','','')
,(106,'593401','設計群','金屬工藝科','產品設計實習I','3','忠','40','33600','洪明輝','','','','','','')
,(106,'593401','動力機械群','汽車科','電系實習','2','孝','36','16800','黃士源','','','','','','')
,(106,'593401','動力機械群','汽車科','電系實習','2','忠','37','136725','黃士源','','','','','','')
,(106,'593401','動力機械群','汽車科','車輛空調檢修實習','2','孝','36','16800','蔡榮昌','','','','','','')
,(106,'593401','動力機械群','汽車科','車輛空調檢修實習','2','忠','37','16800','蔡榮昌','','','','','','')
,(107,'033408','家政群','流行服飾科','立體裁剪','2','乙','35','18046','何佩珊','','','','','','')
,(107,'033408','家政群','流行服飾科','立體裁剪','2','甲','36','18046','何佩珊','','','','','','')
,(107,'033408','家政群','流行服飾科','美容實務','2','乙','35','12446','吳憶萌','','','','','','')
,(107,'033408','家政群','流行服飾科','美容實務','2','甲','36','12446','吳憶萌','','','','','','')
,(107,'033408','家政群','家政科','美容實務','2','乙','40','11522','李芊慧','','','','','','')
,(107,'033408','家政群','家政科','美顏','2','乙','40','11522','李芊慧','','','','','','')
,(107,'033408','家政群','家政科','美容實務','2','甲','40','11522','李芊慧','','','','','','')
,(107,'033408','家政群','家政科','美顏','2','甲','40','11522','李芊慧','','','','','','')
,(107,'033408','商業與管理群','資料處理科','資料庫網站設計Ⅰ','2','甲','41','139732','孫鳴遠','','','','','','')
,(107,'033408','商業與管理群','資料處理科','資料庫網站設計Ⅱ','2','甲','41','136125','孫鳴遠','','','','','','')
,(107,'033408','家政群','家政科','服飾造型Ⅱ','2','乙','40','18326','潘美伶','','','','','','')
,(107,'033408','家政群','家政科','服飾造型I','2','乙','40','18326','潘美伶','','','','','','')
,(107,'033408','家政群','家政科','服飾造型Ⅱ','2','甲','40','18326','潘美伶','','','','','','')
,(107,'033408','家政群','家政科','服飾造型I','2','甲','40','18326','潘美伶','','','','','','')
,(107,'033408','家政群','流行服飾科','成衣製作','3','甲','35','18046','謝雯玲','','','','','','')
,(107,'013434','餐旅群','餐飲管理科','餐飲實務I','3','乙','40','8000','陳煥堂','張增鵬','','','','','')
,(107,'013434','餐旅群','餐飲管理科','餐飲實務I','3','甲','40','8000','陳煥堂','張增鵬','','','','','')
,(107,'013434','餐旅群','餐飲管理科','西餐烹調I II','2','乙','40','6400','蔡斌翰','葉承欽','','','','','')
,(107,'013434','餐旅群','餐飲管理科','西餐烹調I II','2','乙','40','6400','蔡斌翰','葉承欽','','','','','')
,(107,'013434','餐旅群','餐飲管理科','西餐烹調I II','2','甲','40','44950','蔡斌翰','葉承欽','','','','','')
,(107,'013434','餐旅群','餐飲管理科','西餐烹調I II','2','甲','40','34644','蔡斌翰','葉承欽','','','','','')
;


-- =========================
-- 如果單只有學生數的話是可以這麼做,但目前不知道怎麼合併再一起
-- =========================
select sum(stuno)
from(
  select ps_year,stuno from mytable group by ps_year,sch_code,depname,grade,classname
) t
group by ps_year
;


-- =========================
-- 產生報表 (半成品)
-- =========================
select ps_year'年度',count(distinct sch_code)'校數'
,count(distinct concat(sch_code,depname,grade,classname))'班級數'
,'學生數'
,count(distinct grpname)'群別數'
,count(distinct depname)'科別數'
,count(distinct coursename)'課程數'
,'業師人數'
,sum(ttlmoney)'經費'
from mytable
group by ps_year
;

預期結果

年度 校數 班級數 學生數 群別數 科別數 課程數 業師人數 經費
106 1 7 269 3 3 8 13 529761
107 2 10 387 3 4 10 10 582673
看更多先前的討論...收起先前的討論...
你想要的報表
是要能呈現各種總和嗎?
ex 107年度所有經費,107年度所有學生數
其實你應該明說你要的報表樣子
目前你的問題範圍太廣,依照目前資料能做出非常多的報表
SQL語法一定都不同


另外學生人數及經費 型態都不是用int
在MySQL中是可以SUM起來的嗎?
(MSSQL無法)
舜~ iT邦研究生 2 級 ‧ 2019-10-30 17:22:29 檢舉
沒留意到型態,執行會過...@@,可能是臨時表的關系?,已調整,感謝提醒
不知道是不是我看錯...106年不是12位老師嗎?
舜~ iT邦研究生 2 級 ‧ 2019-10-31 13:18:27 檢舉
您沒看錯,是我看錯,已調整
能告訴我們這是誰設計的嗎??
1
rogeryao
iT邦高手 7 級 ‧ 2019-10-31 13:27:10
最佳解答
-- 合併 SQL
SELECT distinct X1.ps_year as '年度',
X2.sch_codeCount as '校數',
X3.ClassCount as '班級數',
X4.stuno as '學生數',
X5.grpCount as '群別數',
X6.depCount as '科別數',
X7.courseCount as '課程數',
X8.wtCount as '業師人數',
X9.ttlmoney_A as '經費(去除重覆經費)',
X10.ttlmoney_B as '經費'

FROM mytable X1
left join (
-- 校數
SELECT ps_year,
COUNT(DISTINCT(sch_code)) as sch_codeCount
FROM mytable
GROUP BY ps_year
) as X2 on X2.ps_year=X1.ps_year
--
left join (
-- 班級數
SELECT ps_year,sum(ClassCount) as ClassCount
from (
SELECT ps_year,count(distinct(ps_year+'-'+
sch_code+'-'+
grpname+'-'+
depname+'-'+
grade+'-'+
classname)) AS ClassCount
FROM mytable
GROUP BY ps_year,sch_code,grpname,depname,grade,classname
) AS TableB
group by ps_year
) as X3 on X3.ps_year=X1.ps_year
left join (
-- 學生數
SELECT 
ps_year,sum(stuno) as stuno
FROM (
SELECT
distinct ps_year,sch_code,grpname,depname,grade,classname,stuno
FROM mytable
) AS Tablec
group by ps_year
) as X4 on X4.ps_year=X1.ps_year
left join (
-- 群別數
SELECT 
ps_year,sum(grpCount) as grpCount
FROM (
SELECT ps_year, COUNT(DISTINCT(ps_year+'-'+sch_code+'-'+grpname)) as grpCount
FROM mytable
GROUP BY ps_year,sch_code,grpname
) AS TableD
group by ps_year
) as X5 on X5.ps_year=X1.ps_year
left join (
-- 科別數
SELECT 
ps_year,sum(depCount) as depCount
FROM (
SELECT ps_year, COUNT(DISTINCT(ps_year+'-'+sch_code+'-'+grpname+'-'+depname)) as depCount
FROM mytable
GROUP BY ps_year,sch_code,grpname,depname
) AS TableE
group by ps_year
) as X6 on X6.ps_year=X1.ps_year
left join (
-- 課程數
SELECT 
ps_year,sum(courseCount) as courseCount
FROM (
SELECT ps_year, COUNT(DISTINCT(ps_year+'-'+sch_code+'-'+grpname+'-'+coursename)) as courseCount
FROM mytable
GROUP BY ps_year,sch_code,grpname,coursename
) AS TableF
group by ps_year
) as X7 on X7.ps_year=X1.ps_year
left join (
-- 業師人數
select distinct ps_year,count(distinct wt) as wtCount
from 
(
SELECT 
ps_year,wt1 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt2 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt3 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt4 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt5 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt6 as wt
from mytable
where 1=1
--
union
SELECT 
ps_year,wt7 as wt
from mytable
where 1=1
) as TableG
where 1=1
and wt<>''
group by ps_year
) as X8 on X8.ps_year=X1.ps_year
left join (
-- 經費: 去除重覆經費
SELECT 
ps_year,sum(ttlmoney) as ttlmoney_A
FROM (
SELECT
distinct ps_year,sch_code,grpname,depname,coursename,grade,classname,ttlmoney
FROM mytable
) AS Tablec
group by ps_year
) as X9 on X9.ps_year=X1.ps_year
left join (
-- 經費 
SELECT 
ps_year,sum(ttlmoney) as ttlmoney_B
FROM mytable
group by ps_year
) as X10 on X10.ps_year=X1.ps_year
where 1=1
order by X1.ps_year

Demo

舜~ iT邦研究生 2 級 ‧ 2019-10-31 18:45:16 檢舉

各個欄位各自處理後再join起來!!,多了個思考方式,感恩rogeryao大大~~
Can't reopen table: 'X1'...晚點再研究

rogeryao iT邦高手 7 級 ‧ 2019-11-01 08:27:52 檢舉

Can't reopen table :
CREATE TEMPORARY TABLE mytable(....
改為
CREATE TABLE mytable(....
即可

rogeryao iT邦高手 7 級 ‧ 2019-11-01 09:14:52 檢舉
1
ckp6250
iT邦新手 3 級 ‧ 2019-10-30 17:06:17

您要的【完成品】長什麼樣子?
應該要完整秀出來(列出正確答案),才好幫您。

看更多先前的回應...收起先前的回應...
舜~ iT邦研究生 2 級 ‧ 2019-10-30 17:15:58 檢舉

依照年度在網頁上列出表格就可以了,不用樣式、查詢

ckp6250 iT邦新手 3 級 ‧ 2019-10-30 17:22:27 檢舉

Anthony_Yang 大大的回答就是我的回答,
【其實你應該明說你要的報表樣子
目前你的問題範圍太廣,依照目前資料能做出非常多的報表
SQL語法一定都不同】

我想完整問題應該會有

  1. 如果我要查 '某種報表' SQL語法要如何下
  2. 查詢後如何使用PHP讓結果出現在網頁畫面上

1.1 必須先知道何種報表ex107年度所有學生數量,107年度所有經費,107年度學校數量
SQL語法皆不同

又或是已經準備好資料
想要在網頁上呈現像是Excel一樣可以去做排序,2次條件
可以隨時調整成想要的報表樣子?

舜~ iT邦研究生 2 級 ‧ 2019-10-30 17:39:45 檢舉

已調整了,感恩提醒

3
小魚
iT邦大師 1 級 ‧ 2019-10-30 19:27:22

如果你業師沒有就空白的話,
業師人數就用 COUNT(wt1 <> '') + COUNT(wt2 <> '') + ...
學生人數就 SUM(stuno)
這樣試試看吧.

看更多先前的回應...收起先前的回應...
小魚 iT邦大師 1 級 ‧ 2019-10-30 20:38:10 檢舉

我仔細看了一下,
這個資料表沒有正規化,
所以會有這種困擾...

ckp6250 iT邦新手 3 級 ‧ 2019-10-30 20:55:33 檢舉

一個老師教二個課程,如潘美伶,
那是算二個業師,還是一個業師?

舜~ iT邦研究生 2 級 ‧ 2019-10-30 21:41:29 檢舉

是的一個葉師可以教多個課程,葉師數量最麻煩的是跨欄重複的問題,在該筆資料wt1的業師,在其他筆資料可能是wt3,但只能算1個(同一個人)

ckp6250 iT邦新手 3 級 ‧ 2019-10-30 21:47:30 檢舉

那麼,您的資料庫規劃有問題了,
老師應該用代碼,不該用人名,
不然,一師多課或同名同姓,要怎麼辧?

小魚 iT邦大師 1 級 ‧ 2019-10-30 22:07:40 檢舉

資料庫規劃有問題 + 1

舜~ iT邦研究生 2 級 ‧ 2019-10-30 22:15:05 檢舉

有問題 + 1 ... 但想改不能改><

小魚 iT邦大師 1 級 ‧ 2019-10-30 22:34:12 檢舉

解決提出問題的人

有個解法是你另外建一個資料表,
然後分幾次更新那個資料表的資料.

業師那欄就一欄一欄Union,
暴力破解.

是的一個葉師可以教多個課程,葉師數量最麻煩的是跨欄重複的問題,在該筆資料wt1的業師,在其他筆資料可能是wt3,但只能算1個(同一個人)

你給的資料好像剛好沒那問題說,有的話就han麻煩了.../images/emoticon/emoticon06.gif

不然SQL語法
把資料分別塞到暫存TABLE
再做SELECT?

這樣就不必擔心動到原始資料
也可以方便後續處理
(雖然這樣好像有點麻煩又占資源)

把資料分別塞到暫存TABLE
再做SELECT?

好主意!但...這樣算改資料庫規劃嗎?XD/images/emoticon/emoticon37.gif

小魚 iT邦大師 1 級 ‧ 2019-10-31 10:15:05 檢舉

不算,
這是另外產生的.

也可以wt1~wt7做union跟group by存成view

舜~ iT邦研究生 2 級 ‧ 2019-10-31 12:55:16 檢舉

我有一個想法是把全部wt1~wt7並成一個字串後在方法內對文字分析(看可以分割幾次)~~
concat(group_concat(distinct wt1),',',group_concat(distinct wt2)....)
這樣只要在方法內弄一個臨時表就可以解決~~
不過這樣效能感覺會慘兮兮 ><

小魚 iT邦大師 1 級 ‧ 2019-10-31 12:58:05 檢舉

設計這麼爛就不要在意效能了.

CREATE VIEW test
AS
select ps_year,wt1 as wt
from mytable
group by ps_year,wt1
union
select ps_year,wt2 as wt
from mytable
where wt2 <> ''
group by ps_year,wt2
(底下以此類推)
查詢時,再把這張VIEW拉進去就可以

我要發表回答

立即登入回答