MYSQL自定义排序,文章置顶等

在MYSQL数据库查询时,排序是经常用到的一个功能。单纯按照某字段排序查询较为简单,无非就是order by `field` (DESC/ASC)。如:


SELECT * FROM `table`
ORDER BY `field` DESC

注:上面的table换成要查询的表名,field换成要排序的字段名,DESC表示从大到小,去掉则从小到大。

下面介绍几个复杂一点的应用:

自定义排序

需求:查询table表,取出id为1,5,9,7的记录,并按1597的顺序显示.

解决


SELECT * FROM `table`
WHERE id in(1,5,7,9)
ORDER BY FIELD(`id`,1,5,9,7) //注意FIELD后面无空格

文章置顶

需求一:查询文章表table,取出所有文章,但字段zj为1的记录置顶(均以time字段排序,time大的放在前面)。

解决一


SELECT * FROM `table`
ORDER BY `zj` DESC,`time` DESC

解决二


SELECT * FROM `table`
WHERE zj=1
ORDER BY `time` DESC
UNION
SELECT * FROM `table`
WHERE zj!=1
ORDER BY `time` DESC

需求二:查询文章表table,取出所有文章,置顶ID为1、5、12、2的文章,并按上述顺序显示。

解决


SELECT * FROM `table`
ORDER BY FIELD(`zj`,1,5,12,2) DESC,`time` DESC


( ! ) Warning: Missing argument 1 for cwppos_show_review(), called in /data/www/enjoyphp/wp-content/themes/flat/content-single.php on line 29 and defined in /data/www/enjoyphp/wp-content/plugins/wp-product-review/includes/legacy.php on line 18
Call Stack
#TimeMemoryFunctionLocation
10.0000354968{main}( ).../index.php:0
20.0001355256require( '/data/www/enjoyphp/wp-blog-header.php' ).../index.php:17
30.185010169112require_once( '/data/www/enjoyphp/wp-includes/template-loader.php' ).../wp-blog-header.php:19
40.186310240256include( '/data/www/enjoyphp/wp-content/themes/flat/single.php' ).../template-loader.php:74
50.269810799144get_template_part( ).../single.php:5
60.269910799664locate_template( ).../general-template.php:167
70.269910799760load_template( ).../template.php:643
80.269910800144require( '/data/www/enjoyphp/wp-content/themes/flat/content-single.php' ).../template.php:686
90.275710785256cwppos_show_review( ).../content-single.php:29

1 评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注