多条件排序及时间对比查询

 知识点：

1.多个条件排序，直接用order by 后面接几个排序规则，优先1则放在第一个

2.结束时间大于等于当前时间 用结束时间字段与 now() 对比

3.名称正序排序使用gbk格式，即首字母排序  convert(substr(字段名,1,1) using 'GBK') 

 

业务场景：

 

SELECT c.ClassName,c.StartEnrollTime,c.EndEnrollTime FROM class c
left join classclassify cc on cc.ClassId=c.Id
left join classify cf on cf.id = cc.ClassifyId
WHERE c.TYPE=2 AND c.SchoolId=61 and c.IsDeleted=0
and c.IsOpenEnroll=1 and c.IsOver=0 and c.IsPublish=1 and c.IsEnrollHide=0
and c.EndEnrollTime >= now()
ORDER BY c.StartEnrollTime desc,c.EndEnrollTime ASC
,convert(substr(c.ClassName,1,1) using 'GBK') ASC ;

 

 

转载于:https://www.cnblogs.com/dieyaxianju/p/7602548.html