Mysql-Cross join 用户自定义变量根据输入值，查询总和小于等于输入值的记录

最新推荐文章于 2023-07-10 17:15:37 发布

少华不加班

最新推荐文章于 2023-07-10 17:15:37 发布

阅读量1.4k

点赞数 1

分类专栏： Mysql实战

本文链接：https://blog.csdn.net/chenshaohua12345/article/details/114648777

版权

Mysql实战专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql-Cross join 用户自定义变量根据输入值，查询总和小于等于输入值的记录

在开发中遇到的一个需求，来记录一下方案和遇到的问题

需求场景

因为公司资金问题，需要在公司的后台管理中，输入具体的打款金额，来根据用户的提现优先级进行打款，也就是说有 A>B>C>D…等级，在页面上输入能够打款的金额，来按照优先级进行打款，打款金额不能超过具体输入的金额，然后查出匹配的数据调用第三方平台进行打款。

		假设表中有4条提现数据
		4条数据对应的金额和等级
		amount：level
		12、  A；
		43、  S；
		54、  B；
		75、  C；
		例1：输入值：60   结果值：43，12
		例2：输入值：100  结果值：43，12
		例3：输入值：200  结果值：43，12，53，73

SQL实现

SELECT a.id, a.level , a.amount
        FROM (select ar.id, ar.level, ar.amount, (@sum := @sum + amount) as totalSum
              from table1 ar
              ORDER BY level ) a
                 cross join (select @sum := 0) params
        WHERE totalSum < 100;

实现思路

@sum 自定义一个mysql 变量。
然后是用 cross join 来进行一个乘积连接。
根据输入金额做为where条件。

cross join（笛卡尔连接）

cross join是mysql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。在MySQL中，当CROSS JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。通常，如果每个表分别具有n和m行，则结果集将具有n*m行

引用https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php的图片，如图演示了cross join的过程，这个过程其实就是笛卡尔连接查询

在这里插入图片描述

测试时出现的问题：

当执行sql时，偶尔会查询不出数据来（数据是没有问题的，就是查询不出来)

经排查，最终怀疑是 @sum := 0 的问题，怀疑是Mysql的优化器对执行的sql优化时，把变量给优化掉了，导致sql不按照原有的方式执行了

解决方案

后来调整了一下cross join 连接的顺序，将自定义变量的select 调整在前面，问题就可以解决了。

SELECT a.id, a.level , a.amount
        FROM (select @sum := 0) params
              cross join (select ar.id, ar.level, ar.amount, (@sum := @sum + amount) as totalSum
              from table1 ar
              ORDER BY level ) a 
        WHERE totalSum < 100;