用sql实现背包问题

背包问题是一道经典的组合优化问题，给定一组物品，每种物品都有自己的重量和价格，在限定的总重量内，如何选择使得物品的总价格最高，背包问题又可以分成01背包，完全背包和多重背包等，网上关于这个问题的解答有很多，但是目前发现没有sql版本的，接下来我就为大家提供一下sql版本的解法，如果大家有更好的方法，欢迎评论。

一.填满背包

首先从最简单背包问题入手，给定背包的体积，每件物品只有一个，如何拿取物品可以填满背包
创建测试数据，vol代表物品的体积

CREATE TABLE `bag0`  (
  `vol`   int(0) NULL DEFAULT NULL
);

INSERT INTO `bag0` VALUES (17);
INSERT INTO `bag0` VALUES (9);
INSERT INTO `bag0` VALUES (14);
INSERT INTO `bag0` VALUES (4);
INSERT INTO `bag0` VALUES (16);
INSERT INTO `bag0` VALUES (7);
INSERT INTO `bag0` VALUES (18);
INSERT INTO `bag0` VALUES (19);
INSERT INTO `bag0` VALUES (5);
INSERT INTO `bag0` VALUES (12);

解决这个问题的思路比较简单，由于每件物品只有一个，只需要从体积小的物品不断向体积大的物品累加，最后取满足背包体积组合即可，使用mysql中的with表达式即可实现，假设背包的体积为50，sql如下

with recursive bag_fill as
 (select vol, vol as sum_vol,cast(vol as char(500)) as compose
    from bag0
  union
  select t.vol,t.vol+s.sum_vol, concat(t.vol,'-',s.compose)
    from bag0 t, bag_fill s
   where t.vol < s.vol)
select sum_vol,compose
  from bag_fill
 where sum_vol = 50;

二.01背包

第二个背包问题是01背包，给定一组物品，每种物品都有自己的重量和价格，并且每种物品只有一个，在限定的总重量内，如何选择使得物品的总价格最高
同样的，创建测试数据，这次多了价格列

CREATE TABLE `bag1`  (
  `vol`   int(0) NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL
);

INSERT INTO `bag1` VALUES (17, 50);
INSERT INTO `bag1` VALUES (9, 31);
INSERT INTO `bag1` VALUES (14, 94);
INSERT INTO `bag1` VALUES (4, 11);
INSERT INTO `bag1` VALUES (16, 68);
INSERT INTO `bag1` VALUES (7, 50);
INSERT INTO `bag1` VALUES (18, 14);
INSERT INTO `bag1` VALUES (19, 43);
INSERT INTO `bag1` VALUES (5, 37);
INSERT INTO `bag1` VALUES (12, 51);

解决这个问题的思路跟上一题差不多，同样是从体积小的物品不断向体积大的物品累加体积和价格，取总体积小于等于背包体积，最后按照总价格倒序排列即可
假设背包的体积是50，mysql中写法如下

with recursive bag_10 as
 (select vol,
         vol as sum_vol,
         price as sum_price,
         cast(vol as char(500)) as compose
    from bag1
  union
  select t.vol,
         s.sum_vol + t.vol,
         s.sum_price + t.price,
         concat(t.vol, '-', s.compose)
    from bag1 t, bag_10 s
   where t.vol < s.vol
     and s.sum_vol + t.vol <= 50)
select sum_vol,sum_price,compose from bag_10 order by sum_price desc;

三.完全背包

下一个问题是完全背包，与01背包唯一不同的地方就是每种物品的个数不限，这次用01背包的数据就可以
其实实现的思路差不多，只不过在递归关联的过程中，由体积小的物品向体积大的物品累加变成了体积小的物品向体积大或者体积相同的物品累加，假设背包的体积为50，mysql的实现方法如下

with recursive bag_complete as
 (select vol,
         vol as sum_vol,
         price as sum_price,
         cast(vol as char(500)) as compose
    from bag1
  union
  select t.vol,
         s.sum_vol + t.vol,
         s.sum_price + t.price,
         concat(t.vol,'-', s.compose)
    from bag1 t, bag_complete s
   where t.vol <= s.vol
     and s.sum_vol + t.vol <= 50)
select sum_vol, sum_price,compose from bag_complete order by sum_price desc;

四.多重背包

最后一个问题是多重背包，与完全背包不同的地方是每种物品的个数有限，这次再创建一张数据表，增加一列代表每种物品的数量

CREATE TABLE `bag2`  (
  `vol` int(0) NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `num` int(0) NULL DEFAULT NULL
);

INSERT INTO `bag2` VALUES (17, 50, 1);
INSERT INTO `bag2` VALUES (9, 31, 1);
INSERT INTO `bag2` VALUES (14, 94, 2);
INSERT INTO `bag2` VALUES (4, 11, 3);
INSERT INTO `bag2` VALUES (16, 68, 2);
INSERT INTO `bag2` VALUES (7, 50, 2);
INSERT INTO `bag2` VALUES (18, 14, 3);
INSERT INTO `bag2` VALUES (19, 43, 2);
INSERT INTO `bag2` VALUES (5, 37, 3);
INSERT INTO `bag2` VALUES (12, 51, 3);

多重背包相对麻烦一些，直接按照上面的方法递归关联是无法取到所有物品的组合的，我的思路是这样，首先取出每个物品的自己与自己关联的所有组合，例如体积为4的物品有3个，那么这个物品的所有组合就是4，4-4，4-4-4，然后在用这个组合的结果按照之前的办法由体积小的物品向体积大的物品累加
第一个步骤的sql如下,sum_vol,sum_price分别代表物品与自己组合后的总体积和总价格

with recursive bag_compose as
 (select vol as raw_vol,
         price,
         num,
         vol * num as sum_vol,
         price * num as sum_price,
         reverse(substr(reverse(repeat(concat(vol, '-'), num)), 2)) as compose
    from bag2
  union
  select raw_vol,
         price,
         num - 1,
         raw_vol * (num - 1),
         price * (num - 1),
         reverse(substr(reverse(repeat(concat(raw_vol, '-'), num - 1)), 2)) as compose
    from bag_compose
   where num > 1)
 select * from bag_compose order by compose;

然后按照原来的方法，从体积小的物品向体积大的物品累加，就可以求出多重背包的结果了

with recursive bag_compose as
 (select vol as raw_vol,
         price,
         num,
         vol * num as sum_vol,
         price * num as sum_price,
         reverse(substr(reverse(repeat(concat(vol, '-'), num)), 2)) as compose
    from bag2
  union
  select raw_vol,
         price,
         num - 1,
         raw_vol * (num - 1),
         price * (num - 1),
         reverse(substr(reverse(repeat(concat(raw_vol, '-'), num - 1)), 2)) as compose
    from bag_compose
   where num > 1),
bag_multiple as
 (select raw_vol,
         sum_vol,
         sum_price,
         cast(compose as char(500)) as compose
    from bag_compose as t
   where sum_vol <= 50
  union
  select t.raw_vol,
         s.sum_vol + t.sum_vol,
         s.sum_price + t.sum_price,
         concat(t.compose, '-', s.compose)
    from bag_compose t, bag_multiple s
   where t.raw_vol < s.raw_vol
     and s.sum_vol + t.sum_vol <= 50)
select compose,
       sum_vol,
       sum_price
  from bag_multiple
 order by sum_price desc;

以上就是背包问题mysql版本的解法，大家有更好的方法，欢迎评论。