每天一个比较有文化的sql语句2

这是上一篇博客的改良版，用到了内联视图 

其中  a 就是内联视图的表名

  SELECT attrId, attrCode, attrName,scopeName,sourceIdPart, sourceIdProduct,source,
            (case source
            when '01' THEN CONCAT('部件-',(select partName from pf1002 where partId= sourceIdPart))
            when '02' THEN CONCAT('基础产品-',(select productName from pf1003 where productId= sourceIdProduct))
            ELSE '自定义'
            END) sourceName,
            (CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = a.attrId AND pf1008.useId =#{useId, jdbcType=VARCHAR} and pf1008.source = source) THEN 1 ELSE 0 END ) isSelected,
            (CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = a.attrId and instr(#{productStr, jdbcType=VARCHAR},pf1008.useId) > 0 and pf1008.source = source) then 1 else 0 end) isExtend,
            (CASE WHEN EXISTS(select * from pf1008 WHERE pf1008.attrId = a.attrId and instr(#{partIdStr, jdbcType=VARCHAR},pf1008.useId) > 0 and pf1008.source = source) then 1 else 0 end) isInclude,
            (select seqNo from pf1008 where pf1008.useType = "02" AND pf1008.attrId = a.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR}) seqNo

        FROM (

                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} and pf1008.source="01") sourceIdPart,
                (select sourceId from pf1008 where  pf1008.attrId = pf1001.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR} and pf1008.source="02") sourceIdProduct,
                 (CASE WHEN EXISTS
                 (select sourceId from pf1008 where  pf1008.attrId = pf1001.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR} and pf1008.source="01")
                 THEN "01" ELSE
                 (case when EXISTS
                 (select sourceId from pf1008 where  pf1008.attrId = pf1001.attrId AND pf1008.useId = #{useId, jdbcType=VARCHAR} and pf1008.source="02")
                  THEN "02" else "03" END )
                 END ) source
                FROM	pf1001
                WHERE instr(#{scopeIds, jdbcType=VARCHAR},pf1001.scopeId) > 0 OR pf1001.scopeType = "01"
        ) a
        ORDER BY seqNo ASC

1.关于子查询和内联视图介绍的博客

https://www.cnblogs.com/shengulong/p/10217146.html

2.单行子查询

子查询分为单行子查询和多行子查询，但是不管单行多行，都是一个字段，当我们需要多行多个字段，像一个表一样的真正结果集时，就需要内联视图

下图  红框的就是子查询  黄框的就是内联视图

3.多行子查询

返回多行记录，但还是一个字段

需要用in （当然还有其他两种方式  但是目前我没用到）

4.内联视图