4个[1-10]的数字，计算出24，如何用PG实现呢？
总体思路
总体上还是穷举法，枚举出所有可能的算式。在穷举的时候，为了性能考虑，可以直接使用int，并且通过Bitwise的操作符来进行判断，从而来获取到具体的枚举值。
24点计算的时候，需要穷举的有两个因素：一个是4张纸牌的计算顺序，一个是计算纸牌用到的3个操作符。
纸牌顺序
4张牌可以有不同的排列组合，总共应该有4 * 3 * 2 * 1 = 24中组合。因此每张牌分配使用2个bit位来记录这张牌的位置，具体就是00代表第一位，01代表第二位，10代表第三位，11代表第四位。但是需要注意的是，不同位置不能出现相同纸牌。
操作符组合
题目中提及只能用到加减乘除四种操作符，那应该也使用2个bit位就够了。但是细想一下，四种操作符是不够的，因为括号会导致计算顺序并未从左到右，所以需要额外两种操作符：[交换减法]以及[交换除法]，只有这两种操作符交换位置会导致结果不同（比如 a1 – a2 与 a2 – a1 的结果可能不同）。所以总共需要6种操作符，一共3个操作符，且操作符可以重复，所以总共有6 * 6 * 6 = 216种排列组合。
结果集过滤
直接笛卡尔积后记录数还是很多的，其实可以提前过滤掉一些记录，比如
- 举例来说，a-b-c-d或a/b/c/d这种组合肯定无法生成24点。经过仔细的计算，应该可以排除掉很多种不可能的组合。
- 第一个操作符，可以直接将[交换减法]和[交换除法]两种操作符的计算结果设置为null，因为纸牌的顺序会调整，他们的计算结果会在普通的减法和除法中进行计算。
SQL实现
纸牌组合
通过递归查询生成0 ~ 255一共256个数值，每张卡牌占据2个bit位，四张纸牌占据8个bit位
WITH RECURSIVE po(n) AS (
SELECT 0
UNION ALL
SELECT n+1 FROM po where n < 255
)
SELECT n FROM po;
但是不同位置不能出现相同纸牌，因此需要过滤掉无效数据，保证剩下的结果是24个。
postgres=> WITH RECURSIVE po(n) AS (
postgres(> SELECT 0
postgres(> UNION ALL
postgres(> SELECT n+1 FROM po where n < 255
postgres(> ),
postgres-> pos(pos1, pos2, pos3, pos4) AS (
postgres(> SELECT n & 192 as pos1, n & 48 as pos2, n & 12 as pos3, n & 3 as pos4
postgres(> FROM po
postgres(> WHERE (n & 192) >> 2 != (n & 48)
postgres(> AND (n & 192) >> 4 != (n & 12)
postgres(> AND (n & 192) >> 6 != (n & 3)
postgres(> AND (n & 48) >> 2 != (n & 12)
postgres(> AND (n & 48) >> 4 != (n & 3)
postgres(> AND (n & 12) >> 2 != (n & 3)
postgres(> )
postgres-> SELECT COUNT(*) FROM pos;
count
-------
24
(1 row)
操作符组合
通过递归查询生成0 ~ 511一共512个数值，因为操作符占据3个bit位，一共三个操作符9个bit位。但是每个操作符仅需要6种组合（000,001,010,011,100,101），需要过滤掉（110,111），最终剩下216个。
postgres=> WITH RECURSIVE op(n) AS (
postgres(> SELECT 0
postgres(> UNION ALL
postgres(> SELECT n+1 FROM op where n < 511
postgres(> ),
postgres-> ops(op1, op2, op3) AS (
postgres(> SELECT n & 448 as op1, n & 56 as op2, n & 7 as op3
postgres(> FROM op
postgres(> WHERE (n & 448) != 448
postgres(> AND (n & 384) != 384
postgres(> AND (n & 56) != 56
postgres(> AND (n & 48) != 48
postgres(> AND (n & 7) != 7
postgres(> AND (n & 6) != 6
postgres(> )
postgres-> SELECT COUNT(*) FROM ops;
count
-------
216
(1 row)
结果集过滤
排查掉不可能的操作符组合，比如a-b-c-d这种，最终应该只剩下104种可能的组合。
postgres=> WITH RECURSIVE op(n) AS (
postgres(> SELECT 0
postgres(> UNION ALL
postgres(> SELECT n+1 FROM op where n < 511
postgres(> ),
postgres-> ops(op1, op2, op3) AS (
postgres(> SELECT n & 448 as op1, n & 56 as op2, n & 7 as op3
postgres(> FROM op
postgres(> WHERE (n & 448) != 448
postgres(> AND (n & 384) != 384
postgres(> AND (n & 56) != 56
postgres(> AND (n & 48) != 48
postgres(> AND (n & 7) != 7
postgres(> AND (n & 6) != 6
postgres(> AND n not in (4, 5, 9, 11, 12, 13, 20, 21, 25, 27, 28, 32, 33, 35, 37, 40, 41, 43, 44, 65, 67, 68, 69, 72, 73, 75, 77, 85, 88, 89, 91, 92, 97, 99, 100, 101, 104, 105, 107, 108, 132, 133, 140, 141, 148, 149, 156, 160, 161, 163, 165, 168, 169, 171, 172, 193, 195, 196, 197, 200, 201, 203, 204, 212, 216, 217, 219, 220, 224, 225, 227, 228, 236, 257, 259, 260, 261, 264, 265, 267, 269, 277, 280, 281, 283, 284, 289, 291, 292, 293, 296, 297, 299, 300, 321, 323, 324, 325, 328, 329, 331, 332, 340, 344, 345, 347, 348, 352, 353, 355, 356, 364)
postgres(> )
postgres-> SELECT COUNT(*) FROM ops;
count
-------
104
(1 row)
最终查询
WITH RECURSIVE op(n) AS (
SELECT 0
UNION ALL
SELECT n+1 FROM op where n < 511
),
po(n) AS (
SELECT 0
UNION ALL
SELECT n+1 FROM po where n < 255
),
ops(op1, op2, op3) AS (
SELECT n & 448 as op1, n & 56 as op2, n & 7 as op3
FROM op
WHERE (n & 448) != 448
AND (n & 384) != 384
AND (n & 56) != 56
AND (n & 48) != 48
AND (n & 7) != 7
AND (n & 6) != 6
AND n not in (4, 5, 9, 11, 12, 13, 20, 21, 25, 27, 28, 32, 33, 35, 37, 40, 41, 43, 44, 65, 67, 68, 69, 72, 73, 75, 77, 85, 88, 89, 91, 92, 97, 99, 100, 101, 104, 105, 107, 108, 132, 133, 140, 141, 148, 149, 156, 160, 161, 163, 165, 168, 169, 171, 172, 193, 195, 196, 197, 200, 201, 203, 204, 212, 216, 217, 219, 220, 224, 225, 227, 228, 236, 257, 259, 260, 261, 264, 265, 267, 269, 277, 280, 281, 283, 284, 289, 291, 292, 293, 296, 297, 299, 300, 321, 323, 324, 325, 328, 329, 331, 332, 340, 344, 345, 347, 348, 352, 353, 355, 356, 364)
),
pos(pos1, pos2, pos3, pos4) AS (
SELECT n & 192 as pos1, n & 48 as pos2, n & 12 as pos3, n & 3 as pos4
FROM po
WHERE (n & 192) >> 2 != (n & 48)
AND (n & 192) >> 4 != (n & 12)
AND (n & 192) >> 6 != (n & 3)
AND (n & 48) >> 2 != (n & 12)
AND (n & 48) >> 4 != (n & 3)
AND (n & 12) >> 2 != (n & 3)
)
SELECT s.*, t.expression as result
FROM cards s
LEFT JOIN
(
select id, max(expression) as expression
from (
select id, case when op3 = 0 then result2 + card4
when op3 = 1 then result2 - card4
when op3 = 2 then result2 * card4
when op3 = 3 then result2 :: numeric / card4 :: numeric
when op3 = 4 then card4 - result2
when op3 = 5 then case when result2 != 0 then card4 :: numeric / result2 :: numeric end
end as result3,
case when op3 = 0 then concat(expression, '+', card4)
when op3 = 1 then concat(expression, '-', card4)
when op3 = 2 then concat(expression, '*', card4)
when op3 = 3 then concat(expression, '/', card4)
when op3 = 4 then concat(card4, '-', expression)
when op3 = 5 then concat(card4, '/', expression)
end as expression
from (
select id, case when op2 = 0 then result1 + card3
when op2 = 8 then result1 - card3
when op2 = 16 then result1 * card3
when op2 = 24 then result1 :: numeric / card3 :: numeric
when op2 = 32 then card3 - result1
when op2 = 40 then case when result1 != 0 then card3 :: numeric / result1 :: numeric end
end as result2, card4, op3,
case when op2 = 0 then concat('(', expression, '+', card3, ')')
when op2 = 8 then concat('(', expression, '-', card3, ')')
when op2 = 16 then concat('(', expression, '*', card3, ')')
when op2 = 24 then concat('(', expression, '/', card3, ')')
when op2 = 32 then concat('(', card3, '-', expression, ')')
when op2 = 40 then concat('(', card3, '/', expression, ')')
end as expression
from (
select id, case when op1 = 0 then card1 + card2
when op1 = 64 then card1 - card2
when op1 = 128 then card1 * card2
when op1 = 192 then card1 :: numeric / card2::numeric
when op1 = 256 then null
when op1 = 320 then null
end as result1, card3, card4, op2, op3,
case when op1 = 0 then concat('(', card1, '+', card2, ')')
when op1 = 64 then concat('(', card1, '-', card2, ')')
when op1 = 128 then concat('(', card1, '*', card2, ')')
when op1 = 192 then concat('(', card1, '/', card2, ')')
when op1 = 256 then null
when op1 = 320 then null
end as expression
from (
select id, case when pos1 = 0 then c1
when pos1 = 64 then c2
when pos1 = 128 then c3
when pos1 = 192 then c4
end as card1,
case when pos2 = 0 then c1
when pos2 = 16 then c2
when pos2 = 32 then c3
when pos2 = 48 then c4
end as card2,
case when pos3 = 0 then c1
when pos3 = 4 then c2
when pos3 = 8 then c3
when pos3 = 12 then c4
end as card3,
case when pos4 = 0 then c1
when pos4 = 1 then c2
when pos4 = 2 then c3
when pos4 = 3 then c4
end as card4,
op1, op2, op3
from (
SELECT id, c1, c2, c3, c4, pos1, pos2, pos3, pos4, op1, op2, op3
FROM cards, ops, pos
) s1
)s2
)s3
)s4
) s5 where result3 = 24 group by id
) t
ON s.id = t.id;
查询结果
id | c1 | c2 | c3 | c4 | result
-----+----+----+----+----+---------------
1 | 5 | 10 | 9 | 6 | (9/(5/10))+6
2 | 6 | 5 | 7 | 3 | 6/(3/(7+5))
3 | 2 | 1 | 4 | 3 | 3/(1/(4*2))
4 | 7 | 4 | 6 | 6 | 6/((7-6)/4)
5 | 10 | 9 | 1 | 2 |
6 | 4 | 2 | 7 | 9 | (9-(4-7))*2
7 | 6 | 2 | 8 | 8 | (8-(8/2))*6
8 | 4 | 10 | 8 | 9 | (9-(10-4))*8
9 | 9 | 6 | 4 | 5 | ((9+6)+5)+4
10 | 1 | 10 | 7 | 3 | 10-((1-3)*7)
11 | 1 | 1 | 6 | 7 |
12 | 4 | 1 | 3 | 3 | ((4+3)+1)*3
13 | 10 | 3 | 5 | 4 | 4-((3-5)*10)
14 | 4 | 2 | 2 | 9 | ((9+4)*2)-2
15 | 4 | 10 | 7 | 7 |
16 | 10 | 4 | 5 | 9 | (5-(9-10))*4
17 | 10 | 9 | 3 | 1 | ((10+1)*3)-9
18 | 8 | 10 | 3 | 9 | 9-(3-(8+10))
19 | 1 | 8 | 5 | 5 |
20 | 9 | 7 | 8 | 8 | 8-((7-9)*8)