SELECT
pf1002.partId,
partCode,
partName,
(case pf1002.scopeType
when '04' THEN CONCAT('产品-',(select productName from pf1003 where productId=pf1002.scopeId))
WHEN '03' THEN CONCAT('产品线-',(select productLineName from pf1004 where productLineId=pf1002.scopeId))
WHEN '02' THEN CONCAT('模板-',(select templateName from pf1005 where templateId=pf1002.scopeId))
ELSE '公共'
END) scopeName,
(select seqno from pf1007 where pf1007.partId = pf1002.partId AND pf1007.useid = #{productId, jdbcType=VARCHAR}) seqNo,
(CASE WHEN EXISTS(select * from pf1007 WHERE pf1007.partId = pf1002.partId and pf1007.useId = #{productId, jdbcType=VARCHAR}) THEN "1" ELSE "0" END ) isSelected
FROM
pf1002
WHERE
(
pf1002.scopeType = "04"
AND pf1002.scopeId = #{productId, jdbcType=VARCHAR}
)
OR
(
pf1002.scopeType = "03"
AND pf1002.scopeId = #{productLineId, jdbcType=VARCHAR}
)
OR pf1002.scopeType = "01"
ORDER BY
CASE
WHEN seqno IS NULL or "" THEN
1
ELSE
0
END ASC,
seqno ASC
1.排序sql
这是一个整体
(CASE
WHEN seqno IS NULL or "" THEN
1
ELSE
0
END)
相当于一个新的字段,不是给seqno赋值了
你可以这样想,表中新增一列
就是CASE
WHEN seqno IS NULL or "" THEN
1
ELSE
0
END
然后按照这个新的字段,和seqno排序
2.排序顺序
从左到右
3.多个字段排序逗号隔开
多个字段排序,用逗号,隔开 而不是and
错误:order by time and id asc这种方式两个字段都是降序的(and隔开时,第二个参数不起作用)
正确: order by time desc,id asc
4.每个字段后都要加desc
多个字段都降序排序,每个字段后边都要加desc
如图1,正确 如图 2, 错误