说的不清不楚:order by type desc,level desc,vip.createtime,notvip.createtime desc
额,就是一张表有 type,level,creatTime这些字段,然后我想将查询结果排序 type为vip的排在最前 其次vip按照级别level为“1”排前面,“0”排后面,如果level相同的话按创建时间creatTime升序排列 非vip就一个排序,按创建时间creatTime倒序排列 比如: ----------------------------- type level creatTime vip 0 10 vip 1 40 vip 0 30 vip 1 8 no 0 22 no 0 50 no 1 33查询结果如下: vip 1 8 vip 1 40 vip 0 10 vip 0 30 no 0 50 no 1 33 no 0 22 求大神教我飞
额,就是一张表有 type,level,creatTime这些字段,然后我想将查询结果排序 type为vip的排在最前 其次vip按照级别level为“1”排前面,“0”排后面,如果level相同的话按创建时间creatTime升序排列 非vip就一个排序,按创建时间creatTime倒序排列 比如: ----------------------------- type level creatTime vip 0 10 vip 1 40 vip 0 30 vip 1 8 no 0 22 no 0 50 no 1 33查询结果如下: vip 1 8 vip 1 40 vip 0 10 vip 0 30 no 0 50 no 1 33 no 0 22 求大神教我飞 那sql基本上满足你的需求了。自己好好试试看
这个问题我已经解决了,用的是这个 select * from(select * from person_info t where t.type='vip' order by (case when t.level='1' then 1 when t.level='0' then 2 else 0 end),t.creatTime) UNION ALL select * from( select * from person_info t where t.type='no' order by t.creatTime DESC) 不清楚有没有更好的方法
不能再代码中直接拼接order by 后面的东西么?
字符串也可以比较大小的! order by t.type desc,t.creatTime desc
select type,level,createTime from ( select type, level, createTime, case when type=='no' then -1 else level end level_forOrder, case when type=='no' then createTime*-1 else createTime end createTime_forOrder from person_info ) order by type desc,level_forOrder asc,createTime desc 试试
type为vip的排在最前
其次vip按照级别level为“1”排前面,“0”排后面,如果level相同的话按创建时间creatTime升序排列
非vip就一个排序,按创建时间creatTime倒序排列
比如:
-----------------------------
type level creatTime
vip 0 10
vip 1 40
vip 0 30
vip 1 8
no 0 22
no 0 50
no 1 33查询结果如下:
vip 1 8
vip 1 40
vip 0 10
vip 0 30
no 0 50
no 1 33
no 0 22
求大神教我飞
type为vip的排在最前
其次vip按照级别level为“1”排前面,“0”排后面,如果level相同的话按创建时间creatTime升序排列
非vip就一个排序,按创建时间creatTime倒序排列
比如:
-----------------------------
type level creatTime
vip 0 10
vip 1 40
vip 0 30
vip 1 8
no 0 22
no 0 50
no 1 33查询结果如下:
vip 1 8
vip 1 40
vip 0 10
vip 0 30
no 0 50
no 1 33
no 0 22
求大神教我飞
那sql基本上满足你的需求了。自己好好试试看
select * from(select * from person_info t where t.type='vip' order by (case when t.level='1' then 1 when t.level='0' then 2 else 0 end),t.creatTime)
UNION ALL
select * from(
select * from person_info t where t.type='no' order by t.creatTime DESC)
不清楚有没有更好的方法
order by t.type desc,t.creatTime desc
(
select
type,
level,
createTime,
case when type=='no' then -1 else level end level_forOrder,
case when type=='no' then createTime*-1 else createTime end createTime_forOrder
from person_info
) order by type desc,level_forOrder asc,createTime desc 试试