PHP顯示收藏課程的人數排名

php phpmyadmin

sharonlin891116 2020-05-23 14:03:52 ‧ 1726 瀏覽

分享至

想請問如果我想顯示出每個課程收藏人數的1~4名請問要怎麼修改....

Mysql語法 (沒有完整列出大概顯示每個Table的功用而已):
CREATE TABLE course (
CId char(10) COLLATE utf8mb4_unicode_ci NOT NULL,
Category char(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Cname varchar(10) 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);
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);
PHP語法:
$link = mysqli_connect("localhost", "mis", "416")
or die("無法建立資料連接: " . mysqli_connect_error());
mysqli_query($link, "SET NAMES utf8");

//選擇收藏數最高的課程
$sql = "select *
		from course 
		order by(select count(*) 
			from savecourse 
			group by CId) DESC LIMIT 4";
$result = mysqli_query($link, $sql);
$rank = 1;
if(!$result){
	echo 'SQL Query Failed';
}else{
	while($row = mysqli_fetch_array($result)){
		echo $row['Cname'];
	}
}

rogeryao iT邦超人 7 級 ‧ 2020-05-23 15:56:26 檢舉

"每個課程收藏人數的1~4名" ?
請將結果貼圖示意

登入發表討論

直播研討會

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

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-05-23 16:35:37

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;

DEMO