首先说下order by
order by asc是升序
一般默认是升序
降序的话是order by desc
如果我们想按照指定的字段顺序来排列怎么做呢
有一个表t
以下是select * from t
id Name Country
1 A TW
2 B JP
3 C US
4 D JP
5 E US
6 F TW
7 G JP
如果select * from t order by Country
id Name Country
2 B JP
4 D JP
7 G JP
1 A TW
6 F TW
5 E US
3 C US
如果我想以 TW->US->JP顺序来的话
SELECT * FROM t
ORDER BY (CASE Country
WHEN 'TW' THEN 1
WHEN 'US' THEN 2
WHEN 'JP' THEN 3
END)
结果就是
1 A TW
6 F TW
5 E US
3 C US
4 D JP
2 B JP
7 G JP
接下来如果在分组中如何排序呢
select area_code,substr(order_time,0,8),count(*) from wangbh_temp
group by area_code,substr(order_time,0,8)
order by substr(order_time,0,8),(
case area_code when 'QZ' then 1
when 'HZ' then 2
when 'HU' then 3
when 'JX' then 4
when 'NB' then 5
when 'SX' then 6
when 'TZ' then 7
when 'WZ' then 8
when 'LS' then 9
when 'JH' then 10
when 'ZS' then 11
end)
AREA_CODE SUBSTR(ORDER_TIME,0,8) COUNT(*)
QZ 20100901 3
HZ 20100901 5
HU 20100901 4
JX 20100901 1
NB 20100901 11
SX 20100901 1
TZ 20100901 6
WZ 20100901 4
LS 20100901 1
JH 20100901 8
HZ 20100902 3
JX 20100902 2
NB 20100902 5
TZ 20100902 1
WZ 20100902 7
LS 20100902 1
JH 20100902 2
以上查询中先以AREA_CODE SUBSTR(ORDER_TIME,0,8)进行分组,
然后以SUBSTR(ORDER_TIME,0,8)默认升序排序,
再以 'QZ'->'HZ'->'HU'-> 'JX'->'NB'->'SX'->'TZ'->'WZ'->'LS'->'JH'->'ZS' 的顺序排序
order by asc是升序
一般默认是升序
降序的话是order by desc
如果我们想按照指定的字段顺序来排列怎么做呢
有一个表t
以下是select * from t
id Name Country
1 A TW
2 B JP
3 C US
4 D JP
5 E US
6 F TW
7 G JP
如果select * from t order by Country
id Name Country
2 B JP
4 D JP
7 G JP
1 A TW
6 F TW
5 E US
3 C US
如果我想以 TW->US->JP顺序来的话
SELECT * FROM t
ORDER BY (CASE Country
WHEN 'TW' THEN 1
WHEN 'US' THEN 2
WHEN 'JP' THEN 3
END)
结果就是
1 A TW
6 F TW
5 E US
3 C US
4 D JP
2 B JP
7 G JP
接下来如果在分组中如何排序呢
select area_code,substr(order_time,0,8),count(*) from wangbh_temp
group by area_code,substr(order_time,0,8)
order by substr(order_time,0,8),(
case area_code when 'QZ' then 1
when 'HZ' then 2
when 'HU' then 3
when 'JX' then 4
when 'NB' then 5
when 'SX' then 6
when 'TZ' then 7
when 'WZ' then 8
when 'LS' then 9
when 'JH' then 10
when 'ZS' then 11
end)
AREA_CODE SUBSTR(ORDER_TIME,0,8) COUNT(*)
QZ 20100901 3
HZ 20100901 5
HU 20100901 4
JX 20100901 1
NB 20100901 11
SX 20100901 1
TZ 20100901 6
WZ 20100901 4
LS 20100901 1
JH 20100901 8
HZ 20100902 3
JX 20100902 2
NB 20100902 5
TZ 20100902 1
WZ 20100902 7
LS 20100902 1
JH 20100902 2
以上查询中先以AREA_CODE SUBSTR(ORDER_TIME,0,8)进行分组,
然后以SUBSTR(ORDER_TIME,0,8)默认升序排序,
再以 'QZ'->'HZ'->'HU'-> 'JX'->'NB'->'SX'->'TZ'->'WZ'->'LS'->'JH'->'ZS' 的顺序排序