寻找凑单算法的最优算法【持续更新】

luguang 2019-07-30 08:58:59
如果从一个表A中抽取n条数据，要让n条数据中FIELD0012这个字段之和无限接近传入的参数Needmoney，返回结果集n条数据的id.
样例数据如下：
如果输入参数Needmoney是1500000条，返回最合适的n条数据的ID，目标数据库是Oracle，请各位集思广益，共同提高？
 INTO ""."" ("ID", "FIELD0012") VALUES ('7542376801649573989', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('792095545251480041', '1200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2799663732232009732', '1800000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-449194716311762442', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3533576885435844060', '5600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('8707956464688320387', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-620822516575089278', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6080894920566947104', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6131225749207402270', '6024000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5085860620889078035', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2104824443380284344', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('5840880044205818786', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8581203654882213411', '1238838.52');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3216031198374022991', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3528057672845771572', '1100000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3120141390712030982', '1300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1337800312943588204', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1355797397165751504', '2300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6960648997476188872', '2700000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3071244672871449914', '1242091.59');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-993240273935905496', '3891419.20');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2727685077045757166', '2312857.55');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6348573605343834500', '31930697.46');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1390937958039833346', '1400000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2396230476064141434', '3720000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5476939412138409703', '1130000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6194586432220579777', '1800000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1120145901341291854', '6083077.15');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5721616447059573204', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-953087760185603307', '2600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5439862695511111741', '8011444.99');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8751456220923147723', '2500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('8158231624255096309', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('4579370252973067206', '2500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6762083006682516289', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1118268023764175594', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1014712553161116909', '3500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-217762634095050090', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('5153859667171822269', '2130000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('591217050601996458', '1400000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('9033504512052380304', '4000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6018321344333987175', '1302410');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8018128991563919623', '1600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('607962477845892893', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('7590182249313095618', '4700000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5394345230388541734', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-491674065428069854', '1528590');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-284580741776915616', '10200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1436670486868806655', '4000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2931545342578702003', '1800000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1051547950735183172', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-661087578544192340', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6573862570419403731', '1570000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6035721865043136315', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1691892348235461558', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1076375769943066401', '4000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8419557841596560039', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-7035702197097905080', '1030000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6409884159929529084', '2600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('4051406510848296550', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2150640211959132497', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2803917967932869387', '7258586.84');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2817128296738503401', '3430000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2216485444515482012', '10000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3591381700484353860', '1042525.32');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('5463872902374114668', '1600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-4485521706605850946', '1300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1694918386400969834', '1358368');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8968079724683115461', '8000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-982048866967796455', '2488975.29');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('7310683544661151633', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('4744120853074167654', '1400000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2134458335904219988', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1111343055900815491', '2600000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-6967066455525995005', '1900000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('560998513580393128', '2740018.22');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('7140522706633181435', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('5513357930501328212', '1700000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('5320786183424135136', '4450000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6866879789430688043', '1550016');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2306117223530449853', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5624112565102426255', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('4291225247768592446', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-520111713847981204', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2309296893420062974', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1995288118020751876', '8000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-3764906646744185026', '1200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-1812077472112492426', '4900000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3520021723629571854', '1359398.41');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('8845922465217661529', '2696148');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2786557034795454071', '1300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2172170171995668667', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-7703510866663880743', '1200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3257083287835787416', '2370000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-3037914648756993116', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1186245741487540501', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2995038129833751506', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('1278097253980654393', '1200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('8314049178738050407', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5228904754379208861', '1700000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('722442458457720147', '3350000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2245053027713594314', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-5688146207274416033', '8920000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-9195492285649894073', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2849923063556126587', '1520000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-8703818674250497942', '4900000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3287924674665990942', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3290043329616954100', '1683078.08');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-4761370205993090337', '2300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6978376789901460260', '2837493');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('3319850160904228237', '3000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-9031493766930019453', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-896424660346254359', '5200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('275616691725682097', '5000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-3095678015150535930', '2000000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('6032456457589768504', '1400000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('2110397234853069333', '1200000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-3716806173050321326', '1300000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('-2415231205191630557', '1500000');

INSERT INTO ""."" ("ID", "FIELD0012") VALUES ('125863414842868434', '1524263.46');
...全文
501 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
maradona1984 2019-08-06
  • 打赏
  • 举报
 回复
我觉得实际项目中,最优解只能穷举,但效率肯定不能接受,近似即可,权衡一下
luguang 2019-08-06
  • 打赏
  • 举报
 回复
引用 12 楼 Ys8888N 的回复:
能把表数据发我不
贴子啥会上有部分表数据，加个表名就可以执行的
Ys8888N 2019-08-05
  • 打赏
  • 举报
 回复
1. 尝试在使用字段上建立索引, 2. 将数据库导入到本地操作; 3. 如果数据量实在庞大,拆分数据库,拆分表, 只留必要字段, 分担查询压力
luguang 2019-08-05
  • 打赏
  • 举报
 回复
引用 7 楼 Ys8888N 的回复:
整理下的话一条sql基本够了
前面也尝试过SQL，如果数据稍微多点的话，效率跟不上，直接卡死!
Ys8888N 2019-08-05
  • 打赏
  • 举报
 回复
整理下的话一条sql基本够了
Ys8888N 2019-08-05
  • 打赏
  • 举报
 回复
引用 5 楼 luguang 的回复:
[quote=引用 4 楼 Ys8888N 的回复:]
取到最小差值后[一般理想情况是0,没有负数,取差值的绝对值],查询最小差值的数据id就行了

感谢你的回复，是取最下差值，但是最小差值如何取呢？取几个数，n是不确定哦[/quote]
查询sql >新增差值列>按照差值列顺序排序 > 第一行的id 就是最小差值 或者用min()函数;至于取几个数, 差值等于最小差值的都取了
luguang 2019-08-05
  • 打赏
  • 举报
 回复
引用 4 楼 Ys8888N 的回复:
取到最小差值后[一般理想情况是0,没有负数,取差值的绝对值],查询最小差值的数据id就行了
感谢你的回复，是取最下差值，但是最小差值如何取呢？取几个数，n是不确定哦
Ys8888N 2019-08-05
  • 打赏
  • 举报
 回复
能把表数据发我不
luguang 2019-08-05
  • 打赏
  • 举报
 回复
行不通，希望能帮忙看看
luguang 2019-08-05
  • 打赏
  • 举报
 回复
引用 9 楼 Ys8888N 的回复:
1. 尝试在使用字段上建立索引, 2. 将数据库导入到本地操作; 3. 如果数据量实在庞大,拆分数据库,拆分表, 只留必要字段, 分担查询压力
https://bbs.csdn.net/topics/392743447,这里有通过数据库来解决的方法案例！
Ys8888N 2019-07-31
  • 打赏
  • 举报
 回复
取到最小差值后[一般理想情况是0,没有负数,取差值的绝对值],查询最小差值的数据id就行了
Ys8888N 2019-07-31
  • 打赏
  • 举报
 回复
差值排序,取差值最小的数据id,
luguang 2019-07-31
  • 打赏
  • 举报
 回复
有人做过类似的项目没有？期待你的发言
java数据结构算法
做一门精致,全面详细的 java数据结构与算法!!!让天下没有难学的数据结构,让天下没有难学的算法,不吹不黑,我们的讲师及其敬业,可以看到课程视频,课件,代码的录制撰写,都是在深夜,如此用心,其心可鉴,他不掉头发,谁掉头发???总之你知道的,不知道的,我们都讲,并且持续更新,走过路过,不要错过,不敢说是史上最全的课程,怕违反广告法,总而言之,言而总之,这门课你值得拥有,好吃不贵,对于你知识的渴求,我们管够管饱话不多说,牛不多吹,我们要讲的本门课程内容:稀疏数组、单向队列、环形队列、单向链表、双向链表、环形链表、约瑟夫问题、栈、前缀、中缀、后缀表达式、中缀表达式转换为后缀表达式、递归与回溯、迷宫问题、八皇后问题、算法的时间复杂度、冒泡排序、选择排序、插入排序、快速排序、归并排序、希尔排序、基数排序(桶排序)、堆排序、排序速度分析、二分查找、插值查找、斐波那契查找、散列、哈希表、二叉树、二叉树与数组转换、二叉排序树(BST)、AVL树、线索二叉树、赫夫曼树、赫夫曼编码、多路查找树(B树B+树和B*树)、图、图的DFS算法和BFS、程序员常用10大算法、二分查找算法(非递归)、分治算法、动态规划算法、KMP算法、贪心算法、普里姆算法、克鲁斯卡尔算法、迪杰斯特拉算法、弗洛伊德算法马踏棋盘算法。
贪心算法：以局部最优追求整体最优
贪心算法以局部最优为基础，通过逐步选择最优解来追求整体最优解。它在解决某些问题上具有出色的效果，并且运行速度快。然而，我们也应该清楚贪心算法的局限性，在实际应用中需要权衡利弊，结合问题的特点进行选择。...
618省心凑背后的新算法——个性化凑单商品打包购推荐
作为购物导购链路的一个重要环节，凑单旨在快速帮助用户找到达成某个满减门槛（比如满300减50）的商品，完成性价比最高的跨店组合结算。前言▐背景凑单是一个历史悠久的场景，伴随着长期优化并不断升级，为用户决策...
【混合蛙跳算法】基于混合蛙跳算法实现最优求解matlab源码
这种算法模拟青蛙群体寻找食物时，按族群分类进行思想传递的过程，将全局信息交换和局部深度搜索相结合，局部搜索使得思想在局部个体间传递，混合策略使得局部间的思想得到交换。在混合蛙跳算法中，群体( 解.
如何快速找到最优路线？深入理解游戏中寻路算法
这种看似寻常的寻路在程序实现起来就需要一定的寻路算法来解决，如何在最短时间内找到一条路径最短的路线，这是寻路算法首先要考虑的问题。在这篇文章中我们会循序渐进来讲解寻路算法是如何演进的，你会看到一种算法...
Java EE

67,514

社区成员

225,878

社区内容

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器，保护我们的业务核心组件（中间件），以延续它的生命力，而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章