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

php mysql 報表不重複

舜~ 2019-10-30 16:22:43 ‧ 2770 瀏覽

分享至

問題：
想請教有寫過報表的前輩們，在當前結構下，學生人數與業師人數不知道怎麼下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

看更多先前的討論...收起先前的討論...

Zed_Yang iT邦新手 3 級 ‧ 2019-10-30 17:11:29 檢舉

你想要的報表
是要能呈現各種總和嗎?
ex 107年度所有經費,107年度所有學生數
其實你應該明說你要的報表樣子
目前你的問題範圍太廣,依照目前資料能做出非常多的報表
SQL語法一定都不同

另外學生人數及經費型態都不是用int
在MySQL中是可以SUM起來的嗎?
(MSSQL無法)

舜~ iT邦高手 1 級 ‧ 2019-10-30 17:22:29 檢舉

沒留意到型態，執行會過...@@，可能是臨時表的關系?，已調整，感謝提醒

mlck970677 iT邦新手 5 級 ‧ 2019-10-31 08:45:09 檢舉

不知道是不是我看錯...106年不是12位老師嗎?

舜~ iT邦高手 1 級 ‧ 2019-10-31 13:18:27 檢舉

您沒看錯，是我看錯，已調整

一級屠豬士 iT邦大師 1 級 ‧ 2019-11-01 17:12:01 檢舉

能告訴我們這是誰設計的嗎??

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

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

回應 3
分享
檢舉

舜~ iT邦高手 1 級 ‧ 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 檢舉

MySQL 臨時表的限制

登入發表回應

ckp6250

iT邦好手 1 級 ‧ 2019-10-30 17:06:17

您要的【完成品】長什麼樣子？
應該要完整秀出來（列出正確答案），才好幫您。

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

舜~ iT邦高手 1 級 ‧ 2019-10-30 17:15:58 檢舉

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

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

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

Zed_Yang iT邦新手 3 級 ‧ 2019-10-30 17:31:21 檢舉

我想完整問題應該會有

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

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

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

舜~ iT邦高手 1 級 ‧ 2019-10-30 17:39:45 檢舉

已調整了，感恩提醒

登入發表回應

小魚

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

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

回應 15
分享
檢舉

看更多先前的回應...收起先前的回應...

小魚 iT邦大師 1 級 ‧ 2019-10-30 20:38:10 檢舉

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

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

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

舜~ iT邦高手 1 級 ‧ 2019-10-30 21:41:29 檢舉

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

ckp6250 iT邦好手 1 級 ‧ 2019-10-30 21:47:30 檢舉

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

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

資料庫規劃有問題 + 1

舜~ iT邦高手 1 級 ‧ 2019-10-30 22:15:05 檢舉

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

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

~~解決提出問題的人~~

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

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

mlck970677 iT邦新手 5 級 ‧ 2019-10-31 08:37:58 檢舉

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

你給的資料好像剛好沒那問題說，有的話就han麻煩了...

Zed_Yang iT邦新手 3 級 ‧ 2019-10-31 09:44:31 檢舉

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

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

mlck970677 iT邦新手 5 級 ‧ 2019-10-31 10:08:46 檢舉

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

好主意!但...這樣算改資料庫規劃嗎?XD

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

不算,
這是另外產生的.

不要問我充電要多久 iT邦新手 2 級 ‧ 2019-10-31 11:36:56 檢舉

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

舜~ iT邦高手 1 級 ‧ 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 檢舉

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

不要問我充電要多久 iT邦新手 2 級 ‧ 2019-10-31 13:07:48 檢舉

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拉進去就可以