- spring:
- datasource:
- driverClassName: com.MySQL.jdbc.Driver
- url: jdbc:MySQL://192.168.8.253:3306/App?useSSL=false&serverTimezone=UTC&allowMultiQueries=true
- username: root
- password:
select 语句:
需求背景
在一个这样的业务中, 需要查询一个表 A 的记录, 其中 A 表的主要字段有 id , createtime ,amount , 现在要求给定一个数字 N, 查询按照创建时间顺序排列之后的前 x 条记录, 这 x 条记录需要满足 sum(amount)>=N 并且尽可能的小的条件. 其中传入的参数只有 N. 在这种情况下, 笔者第一个想到的方案就是在 sql 中使用变量来记录这个 sum(amount) 的值来达到目的. 其中 sql 如下:
- SET @sum =0;
- SET @flag =0;
- SELECT *
- FROM (
- SELECT
- (CASE WHEN @sum <100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
- restpoints END) AS sum,
- (CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
- (CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
- (CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
- t.*
- FROM (select * from A) a
- order by date(useexpiretime) asc,restpoints desc
- )t
- )AS TMP
- WHERE TMP.xxid IS NOT NULL
问题解决第一步: 不报错了, 但查询结果与期望的不一致
遇到的问题就是 mybatis 似乎不支持上面的这种写法, 会报语法错误, 在确定这个 sql 在 xml 中没有书写错误的情况下, 开始进行 sql 的一些变形, 来尝试看是否可以.
第一个尝试就是把 SET @sum =0;SET @flag =0; 去掉了, 放在了 select 里面去定义, 因为我发现, 即使是只有 SET 语句也是会报错的, 推测 mybatis 可能不支持 SET. 即变化如下:
- SELECT @sum:= 0,@flag := 0,TMP.*
- FROM (
- SELECT
- (CASE WHEN @sum < 100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
- restpoints END) AS sum,
- (CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
- (CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
- (CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
- t.*
- FROM (select * from A) a
- order by date(useexpiretime) asc,restpoints desc
- )t
- )AS TMP
- WHERE TMP.xxid IS NOT NULL
但是紧接着就发现查询出来的数据都是空的, 我使用日志打印出来的 sql 进行查询发现也是空的, 但是使用之前未变形的那种是正常的, 这时候可能就是 flag 和 sum 的值 有问题了, 我是用 select @flag 进行查看, 发现它的值果然不是 0, 而是 19, 这让我感到很奇怪, 就是说外层的赋值似乎不等同于 SET 这种赋值方式, 仔细想想, 嵌套查询的情况都是从子查询开始执行的, 所以可能在里面的子查询开始的时候, 外层的赋值并没有起到作用, 所以, 我把这个赋值作为一个单独的子查询放在了最里面就可以了,
如下:
- SELECT @sum:= 0,@flag := 0,TMP.*
- FROM (
- SELECT
- (CASE WHEN @sum < 100 THEN @sum := @sum + restpoints ELSE @sum := @sum +
- restpoints END) AS sum,
- (CASE WHEN @sum <100 THEN @flag :=0 ELSE @flag := @flag + 1 END) AS flag,
- (CASE WHEN @flag < 2 THEN id ELSE NULL END) AS xxid,
- (CASE WHEN @flag < 2 THEN restpoints ELSE NULL END) AS xxrestpoints,
- t.*
- FROM (select * from A) a
- order by date(useexpiretime) asc,restpoints desc
- )t,(SELECT @sum:= 0,@flag := 0) notuse
- )AS TMP
- WHERE TMP.xxid IS NOT NULL
- ----------------
- <update id="updateCompyBalance" parameterType="java.util.HashMap">
- select @money := CARRIAGE + SERVICE_FEE from receipt_order_info where
- id=#{ORDER_ID}; update receipt_send_company set ACCOUNT_BALANCE=ACCOUNT_BALANCE
- - @money where id=#{COMP_ID};
- </update>
- ----------------
- <update id="updatePrice">
- /*1. 变量定义 */
- select @isEnd:=0,@isFinish:=1;
- /*2. 使用变量 */
- update Sc_Stock a set a.cost_price = 10 where @isFinish= 1 and @isEnd = 0;
- </update>
- <update id="updatePrice">
- /*1. 变量定义 */
- select @newPrice:=0;
- /*2. 变量赋值 */
- update Sc_Stock a set a.price = @newPrice := 11 where a.id = 2222;
- /*3. 变量使用 */
- update Sc_Stock a set a.num = 2 where @newPrice = 11;
- </update>
- <update id="updatePrice">
- /*1. 变量定义 */
- select @isFinish:=0;
- /*2. 变量使用 */
- update Sc_Stock set num = 2 where newPrice = 11 and if(@isFinish = 0, 0, @isFinish := a.is_finish);
- </update>
来源: http://www.bubuko.com/infodetail-3375139.html