PHP MYSQL 做成績單

php mysql

albert0168 2015-05-24 14:08:42 ‧ 5219 瀏覽

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

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', '劉七');

albert0168 iT邦新手 5 級 ‧ 2015-05-26 00:25:37 檢舉

感謝hitomitanaka 大大的熱心
怎麼一按下《最佳解答》就無法討論了???

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

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

登入發表討論

直播研討會

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

直播中

2 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2015-05-24 23:47:58

最佳解答

&lt;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