mysql 語法的應用2

語法

ektrontek 2020-06-10 21:18:51 ‧ 950 瀏覽

分享至

請教大家
1.ORDER BY排序可以一次兩個欄位組合嗎? 想要依照兩個欄位同時由大到小來排序
2.可以加上group by來分組嗎? 感謝

SELECT MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_month, YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_year, mdl_role.name AS user_role, COUNT( DISTINCT mdl_stats_user_monthly.userid ) AS total_users
FROM mdl_stats_user_monthly
INNER JOIN mdl_role_assignments ON mdl_stats_user_monthly.userid = mdl_role_assignments.userid
INNER JOIN mdl_context ON mdl_role_assignments.contextid = mdl_context.id
INNER JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id
WHERE mdl_context.contextlevel =50
AND `mdl_stats_user_monthly`.`stattype` = 'activity'
AND mdl_stats_user_monthly.courseid <>1
GROUP BY MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_stats_user_monthly.stattype, mdl_role.name
ORDER BY YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_role.name
LIMIT 0 , 30

登入發表討論

直播研討會

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

直播中

1 個回答

舜~

iT邦高手 1 級 ‧ 2020-06-10 22:12:50

最佳解答

1.ORDER BY排序可以一次兩個欄位組合嗎?要依照兩個欄位同時由大到小來排序

order by a desc, b desc
由大到小來排序請加上desc

2.可以加上group by來分組嗎?

您不是已經用group by 來分組了嗎~
還是希望美化或簡化或改善效能? 或許可透過子查詢

select calendar_month,calendar_year,user_role,total_users
from(
	SELECT
	 MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_month,
	 YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_year,
	 mdl_role.name AS user_role,
	 COUNT( DISTINCT mdl_stats_user_monthly.userid ) AS total_users,
	 mdl_stats_user_monthly.stattype
	FROM mdl_stats_user_monthly
	INNER JOIN mdl_role_assignments ON 
    		mdl_stats_user_monthly.userid = mdl_role_assignments.userid
	INNER JOIN mdl_context ON 
    		mdl_role_assignments.contextid = mdl_context.id
	INNER JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id
	WHERE mdl_context.contextlevel =50
	 AND `mdl_stats_user_monthly`.`stattype` = 'activity'
	 AND mdl_stats_user_monthly.courseid <>1
) t1
GROUP BY
 calendar_month,
 calendar_year,
 stattype,
 user_role
ORDER BY
 calendar_year desc,
 calendar_month desc,
 user_role
LIMIT 0 , 30