sql中为一个字段名的多种情况指定排序

SELECT
*
FROM
(
SELECT
p.bidId,
p.state,
p.prepareSellTime,
p.bidProName,
p.bidProNumber,
p.bidMoney,
p.proType,
project.payaccrualType AS payMoneyTimeType,
p.keepCreditlevelName,
p.proKeepType,
CASE
WHEN (
project.payaccrualType = 'dayPay'
OR project.payaccrualType = 'owerPay'
)
AND (
p.proType = 'B_Dir'
OR p.proType = 'P_Dir'
) THEN
project.payintentPeriod
WHEN (
project.payaccrualType = 'dayPay'
OR project.payaccrualType = 'owerPay'
)
AND (
p.proType = 'B_Or'
OR p.proType = 'P_Or'
) THEN
IFNULL(
datediff(bor.loanEndTime, now()),
datediff(por.loanEndTime, now())
)
WHEN project.payaccrualType = 'monthPay'
AND (
p.proType = 'B_Dir'
OR p.proType = 'P_Dir'
) THEN
project.payintentPeriod * 30
WHEN project.payaccrualType = 'monthPay'
AND (
p.proType = 'B_Or'
OR p.proType = 'P_Or'
) THEN
IFNULL(
datediff(bor.loanEndTime, now()),
datediff(por.loanEndTime, now())
)
WHEN project.payaccrualType = 'seasonPay'
AND (
p.proType = 'B_Dir'
OR p.proType = 'P_Dir'
) THEN
project.payintentPeriod * 120
WHEN project.payaccrualType = 'seasonPay'
AND (
p.proType = 'B_Or'
OR p.proType = 'P_Or'
) THEN
IFNULL(
datediff(bor.loanEndTime, now()),
datediff(por.loanEndTime, now())
)
WHEN project.payaccrualType = 'yearPay'
AND (
p.proType = 'B_Dir'
OR p.proType = 'P_Dir'
) THEN
project.payintentPeriod * 360
WHEN project.payaccrualType = 'yearPay'
AND (
p.proType = 'B_Or'
OR p.proType = 'P_Or'
) THEN
IFNULL(
datediff(bor.loanEndTime, now()),
datediff(por.loanEndTime, now())
)
END AS loanLifeQuery,
CASE
WHEN project.payaccrualType = 'dayPay' THEN
CONCAT(
project.payintentPeriod,
'天'
)
WHEN project.payaccrualType = 'monthPay' THEN
CONCAT(
project.payintentPeriod,
'个月'
)
WHEN project.payaccrualType = 'seasonPay' THEN
CONCAT(
project.payintentPeriod,
'个季度'
)
WHEN project.payaccrualType = 'yearPay' THEN
CONCAT(
project.payintentPeriod,
'年'
)
WHEN project.payaccrualType = 'owerPay' THEN
CONCAT(
project.payintentPeriod,
'天'
)
END AS loanLife,
IFNULL(
IFNULL(
bdir.yearInterestRate,
pdir.yearInterestRate
),
IFNULL(
bor.yearInterestRate,
por.yearInterestRate
)
) AS yearInterestRate
FROM
pl_bid_plan p
LEFT JOIN bp_business_dir_pro AS bdir ON (
p.proType = 'B_Dir'
AND p.bDirProId = bdir.bdirProId
)
LEFT JOIN bp_persion_dir_pro AS pdir ON (
p.proType = 'P_Dir'
AND p.pDirProId = pdir.pdirProId
)
LEFT JOIN bp_business_or_pro AS bor ON (
p.proType = 'P_Or'
AND p.borProId = bor.borProId
)
LEFT JOIN bp_persion_or_pro AS por ON (
p.proType = 'P_Or'
AND p.pOrProId = por.porProId
)
LEFT JOIN bp_fund_project AS project ON (
project.id = bdir.moneyPlanId
OR project.id = pdir.moneyPlanId
OR project.id = bor.moneyPlanId
OR project.id = por.moneyPlanId
)
) AS plan
WHERE

plan.state > 0 

ORDER BY FIELD(plan.state, 1, 2, 6, 7, 10)

输出如下