SELECT pf1001.attrId, attrCode, attrName,
(case pf1001.scopeType
when '04' THEN CONCAT('产品-',(select productName from pf1003 where productId= pf1001.scopeId))
WHEN '03' THEN CONCAT('产品线-',(select productLineName from pf1004 where productLineId= pf1001.scopeId))
WHEN '02' THEN CONCAT('模板-',(select templateName from pf1005 where templateId= pf1001.scopeId))
ELSE '公共'
END) scopeName,
(select sourceId from pf1008 where pf1008.attrId = pf1001.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR}) sourceId,
(case (select source from pf1008 where pf1008.attrId = pf1001.attrId AND pf1008.useId =#{useId, jdbcType=VARCHAR})
when '01' THEN CONCAT('部件-',(select partName from pf1002 where partId= sourceId))
when '02' THEN CONCAT('基础产品-',(select productName from pf1003 where productId= sourceId))
ELSE '自定义'
END) sourceName,
(CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = pf1001.attrId AND pf1008.useId =#{useId, jdbcType=VARCHAR}) THEN 1 ELSE 0 END ) isSelected,
(CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = pf1001.attrId and instr(#{productStr, jdbcType=VARCHAR},pf1008.useId) > 0) then 1 else 0 end) isExtend,
(CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = pf1001.attrId and instr(#{partIdStr, jdbcType=VARCHAR},pf1008.useId) > 0) then 1 else 0 end) isInclude,
(select seqNo from pf1008 where pf1008.useType = "02" AND pf1008.attrId = pf1001.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR}) seqNo
FROM pf1001
WHERE instr(#{scopeIds, jdbcType=VARCHAR},pf1001.scopeId) > 0
OR pf1001.scopeType = "01"
ORDER BY seqNo ASC
1.instr()
在这个sql中用到的是instr()的特殊用法,之所以用instr不用like是因为检索速度快
2.instr()进阶
相当于 比如那里边的入参partIdStr是[1,2]
select * from pf1008 where pf1008.attrId=pf1001.attrId and (pf1008.useId="1" or pf1008.useId="2")
instr代替like 效率高
SELECT *
from pf1002
where 1=1
<if test="partName != null" >
and instr(partName,#{partName, jdbcType=VARCHAR})>0
</if>
母串在前
3.sql解释
还是上边2中这个sql
红框框起来这个 整个是一个结果集 注意前边的case when exists 如果结果集不为空 那就是true
后边then 1 else 0 end 相当于三目运算符 就是前边是true 后边走then 否则走else
语句整体意思就是 根据查询到的结果集中取出当前这条记录的pf1001.attrId作为条件,查询pf1008这个表,并且使用到了用、instr函数,如果能在pf1008表中查到数据 那就给后边isInclude赋值为1 查不到数据 就给后边isInclude赋值为0
4.case when then else end和switch case default
mysql中的case when then else end这个结构 相当于java中的switch case default
5.case中也可以写子查询,如下
只要保证结果集返回的是一个值就行
(case (select source from pf1008 where pf1008.attrId = pf1001.attrId AND pf1008.useId =#{useId, jdbcType=VARCHAR})
6.子查询
这里用到的是单行子查询
关于子查询,下一个博客记
7.子查询查询结果的使用
在前边子查询查询出来的结果,下边的子查询也可以用
8.CONCAT
concat后边的拼接也可以使用子查询
CONCAT('产品-',(select productName from pf1003 where productId= pf1001.scopeId))
9.instr追加
用instr代替like查询 母串在前边 子串在后边
比如 想查询useId字段在入参字符串中的数据
此时入参字符串是母串
instr(#{partIdAndProductId, jdbcType=VARCHAR},pf1008.useId) > 0
比如 想模糊查询属性名为入参字符串的数据,应用场景为列表查询
此时入参字符串是子串
instr(attrName,#{attrName, jdbcType=VARCHAR})>0