MYSQL用法(十六) MySQL按指定字段自定义列表排序

一、 ORDER BY 排序

要求:按照字段IS_NEW_PROJ的降序、字段LOAN_APR的降序、字段GMT_BUY_START的升序排序

sql语句如下:

SELECT
	b.PROJECT_ID AS project_id,
	b.PROJECT_TITLE AS project_title,
	b.PROJECT_STATUS AS project_status,
	b.LOAN_FUNDS AS loan_funds,
	b.REPAY_ORDER AS repay_order,
	b.LOAN_APR AS loan_apr,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN 0
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN 1
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN 2
		WHEN b.PROJECT_STATUS = 'FULL_AUDIT' THEN 3
		WHEN b.PROJECT_STATUS = 'BIDS_AUDIT' THEN 4
		WHEN b.PROJECT_STATUS = 'BIDS_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'DELAY_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'RUN' THEN 5
		WHEN b.PROJECT_STATUS = 'CANCEL_AUDIT' THEN	6
		WHEN b.PROJECT_STATUS = 'CANCEL_PAYMENT' THEN 6
		WHEN b.PROJECT_STATUS = 'FINISH' THEN 7
		WHEN b.PROJECT_STATUS = 'AUDIT' THEN 8
		WHEN b.PROJECT_STATUS = 'EDIT' THEN 8
		END
	) AS status_order,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN
			timediff(p.GMT_BUY_END, now())
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN
			timediff(p.GMT_BUY_START, now())
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN
			timediff(p.GMT_REG_START, now())
		END
	) AS time_order,
	p.PUBLISH_TIME AS publishTime,
	p.GMT_BUY_START AS gmt_buy_start,
	p.IS_NEW_PROJ AS isNewProj
FROM
	PJ_BASE_PROJ AS b
LEFT JOIN PJ_MAIN_PROJ AS p ON b.PROJECT_ID = p.PROJECT_ID
WHERE
	b.DELETE_TAG = 'NO'
AND b.PROJECT_STATUS IN (
	'BUY',
	'FULL_AUDIT',
	'RUN',
	'FINISH'
)
ORDER BY
	IS_NEW_PROJ DESC,
	LOAN_APR DESC,
	GMT_BUY_START ASC

结果显示:

二、 ORDER BY FIELD排序(自定义排序规则)

要求:按照字段PROJECT_STATUS指定的顺序排序

sql语句如下:

SELECT
	b.PROJECT_ID AS project_id,
	b.PROJECT_TITLE AS project_title,
	b.PROJECT_STATUS AS project_status,
	b.LOAN_FUNDS AS loan_funds,
	b.REPAY_ORDER AS repay_order,
	b.LOAN_APR AS loan_apr,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN 0
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN 1
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN 2
		WHEN b.PROJECT_STATUS = 'FULL_AUDIT' THEN 3
		WHEN b.PROJECT_STATUS = 'BIDS_AUDIT' THEN 4
		WHEN b.PROJECT_STATUS = 'BIDS_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'DELAY_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'RUN' THEN 5
		WHEN b.PROJECT_STATUS = 'CANCEL_AUDIT' THEN	6
		WHEN b.PROJECT_STATUS = 'CANCEL_PAYMENT' THEN 6
		WHEN b.PROJECT_STATUS = 'FINISH' THEN 7
		WHEN b.PROJECT_STATUS = 'AUDIT' THEN 8
		WHEN b.PROJECT_STATUS = 'EDIT' THEN 8
		END
	) AS status_order,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN
			timediff(p.GMT_BUY_END, now())
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN
			timediff(p.GMT_BUY_START, now())
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN
			timediff(p.GMT_REG_START, now())
		END
	) AS time_order,
	p.PUBLISH_TIME AS publishTime,
	p.GMT_BUY_START AS gmt_buy_start,
	p.IS_NEW_PROJ AS isNewProj
FROM
	PJ_BASE_PROJ AS b
LEFT JOIN PJ_MAIN_PROJ AS p ON b.PROJECT_ID = p.PROJECT_ID
WHERE
	b.DELETE_TAG = 'NO'
AND b.PROJECT_STATUS IN (
	'BUY',
	'FULL_AUDIT',
	'RUN',
	'FINISH'
)
ORDER BY FIELD(PROJECT_STATUS,'BUY','FULL_AUDIT','RUN','FINISH')

结果显示:

三、 ORDER BY FIELD排序升级(多条件组合排序)

要求:按照字段IS_NEW_PROJ降序、字段PROJECT_STATUS指定的顺序排、字段LOAN_APR降序、字段GMT_BUY_START升序

sql语句如下:

SELECT
	b.PROJECT_ID AS project_id,
	b.PROJECT_TITLE AS project_title,
	b.PROJECT_STATUS AS project_status,
	b.LOAN_FUNDS AS loan_funds,
	b.REPAY_ORDER AS repay_order,
	b.LOAN_APR AS loan_apr,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN 0
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN 1
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN 2
		WHEN b.PROJECT_STATUS = 'FULL_AUDIT' THEN 3
		WHEN b.PROJECT_STATUS = 'BIDS_AUDIT' THEN 4
		WHEN b.PROJECT_STATUS = 'BIDS_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'DELAY_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'RUN' THEN 5
		WHEN b.PROJECT_STATUS = 'CANCEL_AUDIT' THEN	6
		WHEN b.PROJECT_STATUS = 'CANCEL_PAYMENT' THEN 6
		WHEN b.PROJECT_STATUS = 'FINISH' THEN 7
		WHEN b.PROJECT_STATUS = 'AUDIT' THEN 8
		WHEN b.PROJECT_STATUS = 'EDIT' THEN 8
		END
	) AS status_order,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN
			timediff(p.GMT_BUY_END, now())
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN
			timediff(p.GMT_BUY_START, now())
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN
			timediff(p.GMT_REG_START, now())
		END
	) AS time_order,
	p.PUBLISH_TIME AS publishTime,
	p.GMT_BUY_START AS gmt_buy_start,
	p.IS_NEW_PROJ AS isNewProj
FROM
	PJ_BASE_PROJ AS b
LEFT JOIN PJ_MAIN_PROJ AS p ON b.PROJECT_ID = p.PROJECT_ID
WHERE
	b.DELETE_TAG = 'NO'
AND b.PROJECT_STATUS IN (
	'BUY',
	'FULL_AUDIT',
	'RUN',
	'FINISH'
)
ORDER BY FIELD(IS_NEW_PROJ,'DESC',PROJECT_STATUS,'BUY','FULL_AUDIT','RUN','FINISH',LOAN_APR,'DESC',GMT_BUY_START,'ASC')

结果显示: -->此时结果集是混乱的

四、 ORDER BY 排序终极版(多条件组合排序+自定义排序)

要求:按照字段IS_NEW_PROJ降序、字段PROJECT_STATUS指定的顺序排序、字段GMT_BUY_START降序、字段PROJECT_STATUS指定的顺序排序

sql语句如下:

SELECT
	b.PROJECT_ID AS project_id,
	b.PROJECT_TITLE AS project_title,
	b.PROJECT_STATUS AS project_status,
	b.LOAN_FUNDS AS loan_funds,
	b.LOAN_APR AS loan_apr,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN 0
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN 1
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN 2
		WHEN b.PROJECT_STATUS = 'FULL_AUDIT' THEN 3
		WHEN b.PROJECT_STATUS = 'BIDS_AUDIT' THEN 4
		WHEN b.PROJECT_STATUS = 'BIDS_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'DELAY_CONFIRM' THEN 4
		WHEN b.PROJECT_STATUS = 'RUN' THEN 5
		WHEN b.PROJECT_STATUS = 'CANCEL_AUDIT' THEN	6
		WHEN b.PROJECT_STATUS = 'CANCEL_PAYMENT' THEN 6
		WHEN b.PROJECT_STATUS = 'FINISH' THEN 7
		WHEN b.PROJECT_STATUS = 'AUDIT' THEN 8
		WHEN b.PROJECT_STATUS = 'EDIT' THEN 8
		END
	) AS status_order,
	(
		CASE
		WHEN b.PROJECT_STATUS = 'BUY' THEN
			timediff(p.GMT_BUY_END, now())
		WHEN b.PROJECT_STATUS = 'PREBUY' THEN
			timediff(p.GMT_BUY_START, now())
		WHEN b.PROJECT_STATUS = 'REGISTER' THEN
			timediff(p.GMT_REG_START, now())
		END
	) AS time_order,
	p.PUBLISH_TIME AS publishTime,
	p.GMT_BUY_START AS gmt_buy_start,
	p.IS_NEW_PROJ AS isNewProj
FROM
	PJ_BASE_PROJ AS b
LEFT JOIN PJ_MAIN_PROJ AS p ON b.PROJECT_ID = p.PROJECT_ID
WHERE
	b.DELETE_TAG = 'NO'
AND b.PROJECT_STATUS IN (
	'BUY',
	'FULL_AUDIT',
	'RUN',
	'FINISH'
)
ORDER BY
	IS_NEW_PROJ DESC,
	STATUS_ORDER ASC,
	p.GMT_BUY_START DESC,
	TIME_ORDER ASC

结果显示: