MySQL排列 [英] MySQL permutation
问题描述
我有两个桌子.一个拥有产品，另一个拥有捆绑产品.我需要弄清楚允许我找到所有可以出售附加产品的组合的SQL.
I have two tables. One has products and the other has bundles that go with it. I need to figure out the SQL that allows me to find all the combinations in which I can sell the product with extras.
Products
Name ID
Bench 1
Extra
Name ID Parent ID QTY
undershelf 1 1 1
overshelf 2 1 1
wheels 3 1 1
我需要一个输出表，该表显示了我可以销售产品的所有组合:
I need and output table that shows all the combination in which I can sell the product:
Bench
Bench + undershelf
Bench + undershelf + overshelf
Bench + overshelf
Bench + wheels
bench + wheels + overshelf and so one.
推荐答案
每个附加项都可以包含在捆绑包中，也可以不包含在捆绑包中，从而使其成为二进制属性.
可视化组合的一种方法是为每个附加项创建一个带有一点的单词，1表示附加项在列表中，0表示不存在.
例如，Bench + undershelf + overshelf是110(如果二进制字符串以相反的顺序读取，则为011)
Every extras can be in the bundle or not, making that a binary property.
A way to visualize the combination is to create a word with a bit for every extra, 1 mean that the extra is in the list, 0 mean the that it is not.
For example Bench + undershelf + overshelf is 110 (or 011 if the binary string is read in the opposite order)
生成n位的每个组合将给出n个附加项的每个组合，还将给出从0到2^n - 1的每个数字.
Generating every combination of n bit will give every combination of n extras, it will also give every number from 0 to 2^n - 1.
我们可以从这里退回:
1.生成从0到2^n - 1的编号列表；
2.将数字转换为二进制，以列出Extras的组合
3.每一点都匹配一个额外的
4.在捆绑包描述中串联附加项的名称.
We can work back from here:
1. generate the list of number from 0 to 2^n - 1;
2. convert the number to binary, to list the combination of extras
3. match every bit with an extra
4. concatenate the names of the extras in the bundle description.
SELECT CONCAT(b.Name
, COALESCE(CONCAT(' + '
, GROUP_CONCAT(x.Name SEPARATOR ' + '))
, '')) Combination
FROM (SELECT p.Name, p.id
, LPAD(BIN(u.N + t.N * 10), e.Dim, '0') bitmap
FROM Products p
CROSS JOIN (SELECT 0 N UNION ALL SELECT 1
UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) u
CROSS JOIN (SELECT 0 N UNION ALL SELECT 1
UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) t
INNER JOIN (SELECT COUNT(1) Dim
, `Parent ID` pID
FROM Extra) E ON e.pID = p.ID
WHERE u.N + t.N * 10 < Pow(2, e.Dim)
) B
LEFT JOIN (SELECT @rownum := @rownum + 1 ID
, `Parent ID` pID
, Name
FROM Extra
, (Select @rownum := 0) r) X
ON x.pID = b.ID
AND SUBSTRING(b.bitmap, x.ID, 1) = '1'
GROUP BY b.Name, b.bitmap
此查询最多可以使用六个附加功能，然后需要另一个数字表(三个附加功能一个数字).
this query will work up to six extras, then it'll need another digit table (one digit every three extras).
工作方式
子查询 E 计算附加项的数量，用于 C 来限制数字表u和t(单位和十位数)为2 ^ dim.
The subquery E count the number of the extras, this is used in C to limit the elements generated by the digit tables u and t (unit and tens) to 2^dim.
该数字由BIN(u.N + t.N * 10)转换为二进制，然后在元素数量上保留'0'，从而生成组合位图.
The number is converted to binary by BIN(u.N + t.N * 10), then left padded with '0' to the number of elements, generating a combination bitmap.
要使用生成的位图，每个附加项都需要一个伪造的ID，它将与其中的位置匹配，这就是子查询 X 的目的.
To use the generated bitmap each extras need a fake id that will match a position in it, that's what the subquery X is meant for.
这两个子查询由位图的第n个字符JOIN组成:如果字符为1，则捆绑中包含多余字符，LEFT被加入以不丢失没有多余内容的产品.
The two subqueries are JOINed by the nth char of the bitmap: if the char is 1 the extra is in the bundle, LEFT joined to not loose the product without extras.
这篇关于MySQL排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
