mysql之如何将MySql查询结果存储在MyBatis中的变量中

我有一个很大的疑问。它看起来像这样:

select * from 
( 
    select [custom columns] 
    from table 
    where id in 
    ( 
      select id  
      from table2 
      where pr_id in 
      (  
        select id  
        from table3 
        where id = #{id} 
      ) and ac_id != #{acId} 
    ) and [some_column1] like #{pattern} 
 
    union 
 
    select [custom columns2] 
    from table 
    where id in 
    ( 
      select id  
      from table2 
      where pr_id in 
      (  
        select id  
        from table3 
        where id = #{id} 
     ) and ac_id != #{acId} 
    ) and [some_column2] like #{pattern} 
 
    union 
 
    ..... 
) 

...还有另外两个工会

我想要做的就是查询两个内部查询，这两个内部查询首先从 table2 中选择 id 到某个变量中，然后在联合查询中使用此查询结果。

我尝试过这样的事情

SET @var1 = ( 
  select id  
      from table2 
      where pr_id in 
      (  
        select id  
        from table3 
        where id = #{id} 
      ) and ac_id != #{acId} 
) 
 
select * from 
( 
    select [custom columns] 
    from table 
    where id in 
    (select @var1) 
    and [some_column1] like #{pattern} 
 
    union 
 
    .... 
) 

但是 mybatis 给我提供了一个错误。有办法做我需要的事情吗？

错误如下:

Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from 
    ( 
      select firstname, lastname, organization, email, null ' at line 11 

选择名字、姓氏、组织、电子邮件、第 11 行的 null 是 [自定义列]

完整的[自定义列]如下所示:

select firstname, lastname, organization, email, null 'applicationId', null 'applicationName', (locate(_ascii #{value}, convert(email using ascii)) - 1) 'index', length(#{value}) 'length', 'EMAIL' as 'type' 

请您参考如下方法:

比处理变量更有效的方法是在查询中包含 SQL 片段。在您的映射文件中:

<sql id="var1"> 
    select id from table2  
    where pr_id in (...) and ac_id != #{ac_id} 
</sql> 

现在您可以将此片段包含在 SQL 中的任何位置:

<select id="big_select"> 
    select * from ( 
        select [cols] from table where id in ( 
        <include refid="var1"/> 
        ) and [col] like #{pattern} 
   union 
   ...etc... 

您可能还想查看 SQL WITH 子句，您也可以使用它来简化查询。