一、 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' THEN6
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.DE
最低0.47元/天 解锁文章
4871
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
