mybatis中mysql，sql能查到，但是返回结果为0的问题教程

问题起因，多数据库支持的问题。

ORCALE下，selectFuzzyByParam方法中的sql，单独执行能找到之，方法返回值也有对应的对象。

<pre class="has">

<if test="queryValue != null">
  <bind name="pattern1" value="'%' + queryValue + '%'" />
</if>
SELECT * FROM
(
select ID, CODE, NVL(TABLE1.TEXT,TABLE2.NAME) as NAME , STATUS, CREATOR, CREATE_TIME,
UPDATE_TIME,PROJECT_DESC AS "desc",PROJECT_TYPE AS "type" from TABLE2
LEFT JOIN TABLE1 on TABLE2.ID = TABLE1.DATA_ID
<if test="languageCode != null">
  and TABLE1.LANGUAGE_CODE = #{languageCode}
</if>
)
<where>
  <if test="queryValue != null">
    and NAME like #{pattern1}
  </if>
  <if test="status != null">
    and STATUS = #{status}
  </if>
  <if test="type != null">
    and "type" = #{type}
  </if>
</where>

  
  
  
本以为将sql中的NVL函数，换成mysql的IFNULL就可以。但是，替换后，出现很怪异的问题。单独执行sql，能查到数据。但是selectFuzzyByParam返回的集合却是空的。这种原因，应该是其中的别名设置有问题，主要是type的问题，改成如下格式后，问题解决。原因，应该是别名的处理方式不同导致的问题。oracle采用的是""的方式，而mysql采用的是``的方式。

  
  
  
  
  

```

    
      
    
    SELECT * FROM
    (
    SELECT ID as id, CODE AS code, IFNULL(TABLE1.TEXT, TABLE2.NAME) AS name,
    STATUS as status, CREATOR as creator, CREATE_TIME as createTime, UPDATE_TIME as updateTime,
    PROJECT_DESC AS `desc`, PROJECT_TYPE AS `type` from TABLE2
    LEFT JOIN TABLE1 on TABLE2.ID = TABLE1.DATA_ID
    
      and TABLE1.LANGUAGE_CODE = #{languageCode}
    
    ) as tmpTable
    
      
        and NAME like #{pattern1}
      
      
        and STATUS = #{status}
      
      
        and `type` = #{type}
      
    
  
```