iT邦幫忙

0

PHP MYSQL 做成績單

請教先進:
用以下兩個資料表,輸出一份學生成績單
我只是業餘,老闆以為我是專業,真痛苦..

CREATE TABLE `student` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` varchar(20) collate utf8_unicode_ci NOT NULL,
  `student_name` varchar(10) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `student` VALUES (1, '104001', '張三');
INSERT INTO `student` VALUES (2, '104001', '李四');
INSERT INTO `student` VALUES (3, '104002', '王五');
INSERT INTO `student` VALUES (4, '104003', '趙六');
INSERT INTO `student` VALUES (5, '104004', '劉七');
感謝hitomitanaka 大大的熱心
怎麼一按下《最佳解答》就無法討論了???

我是補習班的老員工,從來也不是寫程式出身的,
老闆呢.....他只會電腦開機,EXCEL不太會用....
就單純的想法,要成績單上線給家長看,簡單的表格???要我們自己做,不用外包了
苦了我們幾個常常摸櫃台電腦的數學輔導老師......

hitomitanaka大的解答,我還在努力吸收中....
感恩啦......

2 個回答

10
一級屠豬士
iT邦高手 1 級 ‧ 2015-05-24 23:47:58
最佳解答
<pre class="c" name="code">建立一個輔助Table
CREATE TABLE ithelp150524c(
exam_name CHAR(4),
row1 TINYINT UNSIGNED NOT NULL,
row2 TINYINT UNSIGNED NOT NULL,
row3 TINYINT UNSIGNED NOT NULL,
row4 TINYINT UNSIGNED NOT NULL,
row5 TINYINT UNSIGNED NOT NULL
);

INSERT INTO ithelp150524c(exam_name, row1, row2, row3, row4, row5) VALUES
('國文', 1, 0, 0, 0, 0),
('英文', 0, 1, 0, 0, 0),
('數學', 0, 0, 1, 0, 0),
('自然', 0, 0, 0, 1, 0),
('社會', 0, 0, 0, 0, 1);

SELECT a.student_name AS '姓名'
     , a.國文
     , a.英文
     , a.數學
     , a.自然
     , a.社會
     , a.tot AS '總分'
     , CASE
         WHEN @prev = tot THEN @rank
         WHEN @prev := tot THEN @rank := @rank + 1
       END AS '名次'
  FROM (SELECT t.student_name
             , SUM(score * row1) AS '國文'
             , SUM(score * row2) AS '英文'
             , SUM(score * row3) AS '數學'
             , SUM(score * row4) AS '自然'
             , SUM(score * row5) AS '社會'
             , SUM(score * row1) + SUM(score * row2) + SUM(score * row3) +
               SUM(score * row4) + SUM(score * row5) AS tot
          FROM score s
          JOIN ithelp150524c c
         USING (exam_name)
          JOIN student t
            ON s.stu_id = t.id
         GROUP BY s.stu_id) a
     , (SELECT @rank := 0, @prev := NULL) b
 ORDER BY tot DESC;
+--------+--------+--------+--------+--------+--------+--------+--------+
| 姓名   | 國文   | 英文   | 數學   | 自然   | 社會   | 總分   | 名次   |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 趙六   |    100 |     70 |     80 |     90 |    100 |    440 |      1 |
| 李四   |     80 |     90 |     60 |     90 |     80 |    400 |      2 |
| 王五   |     90 |     80 |     70 |     70 |     90 |    400 |      2 |
| 張三   |     70 |    100 |     50 |     70 |     70 |    360 |      3 |
| 劉七   |     60 |     60 |     90 |     70 |     60 |    340 |      4 |
+--------+--------+--------+--------+--------+--------+--------+--------+

名次應為 1 2 2 3 4,  而非 1 2 2 4 5
看更多先前的回應...收起先前的回應...
wiseguy iT邦超人 1 級 ‧ 2015-05-24 23:53:23 檢舉

ON s.stu_id = t.id

小雨大真體貼,自動修正。讚
其實原 po 的分數資料中,學號欄變個位數,根本對不起來,此題應該無解的。偷笑

wiseguy提到:
自動修正

莫非是江湖中人聞風喪膽的
「人工智慧」

fillano iT邦超人 1 級 ‧ 2015-05-25 09:45:06 檢舉

我怎麼覺得樓主的老闆少生了一個entity...應該有個「科目」的table比較好XD

有這樣把一些Table的CREATE, INSERT, 測試資料準備好.
已經算是不錯的了.

俗話說的好
「挨踢無難題,只怕好心人」
簽名

0
Albert
iT邦高手 1 級 ‧ 2015-05-24 20:01:37

albert0168提到:
請教先進:
用以下兩個資料表,輸出一份學生成績單
我只是業餘,老闆以為我是專業,真痛苦..

真爽與假痛
真的自己也很想到處問!!自己來...確保老闆心目中的地位!!!|
不然一堆顧問
等著你

我要發表回答

立即登入回答