Analysis of how to specify the sorting of a certain row or column in mysql (picture)-Mysql Tutorial-php.cn

Analysis of how to specify the sorting of a certain row or column in mysql (picture)

黄舟

Release： 2017-05-21 09:17:03

Original

1613 people have browsed it

This article mainly introduces mysqlSpecify the sorting of a certain row or column. Friends who need it can refer to the

method:

Cannot be achieved through desc:

Method 1:

select sp.productid,sp.productname,ss.sku from sp_product sp inner join sku_skus ss on sp.productid=ss.productid 
where sp.productname=&#39;力士恒久嫩肤娇肤香皂115g&#39;
or sp.productname=&#39;佳洁士（Crest） 绿茶薄荷牙膏180克&#39;
or sp.productname=&#39;心味果园 兰花豆 190g/袋 1袋 炒货豆制品休闲零食&#39; order by (
case when sp.productid=11042723 then 1 ELSE 2 END)

Copy after login

Method 2:

Core sql

select sp.productid,sp.productname,ss.sku from sp_product sp inner join sku_skus ss on sp.productid=ss.productid 
where sp.productname=&#39;力士恒久嫩肤娇肤香皂115g&#39;
or sp.productname=&#39;佳洁士（Crest） 绿茶薄荷牙膏180克&#39;
or sp.productname=&#39;心味果园 兰花豆 190g/袋 1袋 炒货豆制品休闲零食&#39; ORDER BY sp.productid not in(11042723)

Copy after login

Mysql: Put the specified data row in front

If there is only one data row, it can be like this:

SELECT * FROM user ORDER BY user_id<>7,score DESC;

Copy after login

Mainly "user_id<>7", the user id of 7 will be ranked first.

If there are multiple data rows:

SELECT * FROM user ORDER BY user_id NOT IN(7,8,9),score DESC;

Copy after login

mysql Sort the specified value of a certain column first

Sort a single column first:
mysql Sort the specified value of a certain column first Sorting order by case

SELECT * FROM `jcxsw`.`t_company_product` order by (
case when id=263 then 1 ELSE 4 END),category_id desc;

Copy after login

This sql code will first sort the data with id =263 and then look back according to category_id

Multiple columns are sorted first:

SELECT * FROM `web_membersfastsort_women` m order by 
m.province<>&#39;10106000&#39; , m.city<>&#39;10106001&#39; ,m.city desc,m.province 
desc,m.s_cid asc, m.images_ischeck desc,m.pic_num desc limit 2000,30

Copy after login

province = 10106000 is in the front row, in province = 10106000 city=10106001 is in the front row

The above is the detailed content of Analysis of how to specify the sorting of a certain row or column in mysql (picture). For more information, please follow other related articles on the PHP Chinese website!