clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335515 fiddles created (27410 in the last week).

CREATE TABLE course ( CId char(10) COLLATE utf8mb4_unicode_ci NOT NULL, -- Category char(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL, Category char(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- Cname varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, Cname varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, Syllabus varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO course (CId, Category, Cname, Syllabus) VALUES ('CL104', '中文系', '國學導讀(二)', NULL), ('CL106', '中文系', '文學概論(二)', NULL), ('CL125', '中文系', '詩經', NULL), ('CL132', '中文系', '杜甫詩', NULL), ('CL137', '中文系', '現代小說選與寫作(二', NULL); SELECT * FROM course;
CId Category Cname Syllabus
CL104 中文系 國學導讀(二)
CL106 中文系 文學概論(二)
CL125 中文系 詩經
CL132 中文系 杜甫詩
CL137 中文系 現代小說選與寫作(二
 hidden batch(es)


CREATE TABLE savecourse ( SId varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, Save_time varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, CId char(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, Save_amount varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO savecourse (SId, Save_time, CId, Save_amount) VALUES ('B031020001', '2020-05-20 10:38:30', 'CL104', NULL), ('B031020001', '2020-05-20 10:41:17', 'CL156', NULL), ('B031020001', '2020-05-20 10:41:42', 'CL241', NULL), ('B031020001', '2020-05-20 10:42:20', 'CL104', NULL), ('B031020001', '2020-05-20 10:42:26', 'CL204', NULL); SELECT * FROM savecourse;
SId Save_time CId Save_amount
B031020001 2020-05-20 10:38:30 CL104
B031020001 2020-05-20 10:41:17 CL156
B031020001 2020-05-20 10:41:42 CL241
B031020001 2020-05-20 10:42:20 CL104
B031020001 2020-05-20 10:42:26 CL204
 hidden batch(es)


SELECT (@SN:=@SN+1) AS '選課排名',CId,Category,Cname,Counter AS '選課人數' FROM ( SELECT A.CId,A.Category,A.Cname,COUNT(B.SId) AS Counter FROM course AS A LEFT JOIN savecourse AS B ON B.CId=A.CId WHERE 1=1 GROUP BY A.CId,A.Category,A.Cname ORDER BY COUNT(B.SId) DESC,A.CId LIMIT 4 ) AS X,(SELECT @SN:=0) AS Y;
選課排名 CId Category Cname 選課人數
1 CL104 中文系 國學導讀(二) 2
2 CL106 中文系 文學概論(二) 0
3 CL125 中文系 詩經 0
4 CL132 中文系 杜甫詩 0
 hidden batch(es)