SELECT   pf1001.attrId,	attrCode,	attrName,
(case pf1001.scopeType
when '04' THEN CONCAT('product-',(select productName from pf1003 where productId= pf1001.scopeId))
WHEN '03' THEN CONCAT('Product line-',(select productLineName from pf1004 where productLineId= pf1001.scopeId))
WHEN '02' THEN CONCAT('Template-',(select templateName from pf1005 where templateId= pf1001.scopeId))
ELSE 'public'
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('parts-',(select partName from pf1002 where partId= sourceId))
when '02' THEN CONCAT('Basic products-',(select productName from pf1003 where productId= sourceId))
ELSE 'custom'
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()

The special usage of instr() is used in this sql

2.instr() advanced

Equivalent to, for example, where the input parameter partIdStr is [1,2]

select * from pf1008 where pf1008.attrId=pf1001.attrId and (pf1008.useId="1" or pf1008.useId="2")

instr is efficient in replacing like

SELECT  *
from pf1002
where 1=1
<if test="partName != null" >
    and instr(partName,#{partName, jdbcType=VARCHAR})>0
</if>

Parent string in front

3.sql interpretation

Or the upper sql

This is a result set. Note the case when exists. If the result set is not empty, it is true

then 1 else 0 end at the back is the same as the triangle operator, which is true at the front and then at the back. Otherwise, else else else

The overall meaning of the statement is to take the pf1001.attrId of the current record as the condition according to the query result set, query the pf1008 table, and use the functions of use and instr. If the data can be found in the pf1008 table, then assign a value of 1 to the subsequent isInclude. If the data cannot be found, assign a value of 0 to the subsequent isInclude

4.case when then else end and switch case default

case when then else end in mysql is equivalent to switch case default in java

5. Subqueries can also be written in case, as follows

Just make sure that the result set returns a value

(case (select source from pf1008 where pf1008.attrId = pf1001.attrId AND pf1008.useId =#{useId, jdbcType=VARCHAR})

6. Subquery

This is a single row sub query

About subquery, next blog

7. Use of subquery query results

The result of the sub query in the front can also be used in the sub query in the bottom

8.CONCAT

concat can also use subquery

CONCAT('product-',(select productName from pf1003 where productId= pf1001.scopeId))

9.instr added

Use instr instead of like to query the parent string at the front and the child string at the back

For example, you want to query the data of useId field in the input parameter string

At this time, the input parameter string is the parent string

instr(#{partIdAndProductId, jdbcType=VARCHAR},pf1008.useId) > 0 

For example, you want to fuzzy query the data with the property name of the input parameter string

At this time, the input parameter string is a substring

instr(attrName,#{attrName, jdbcType=VARCHAR})>0