果洛藏族自治州网站建设_网站建设公司_服务器维护_seo优化

已知表tab1结构包含iter,op,bs列，其中iter是迭代序号，按行递增，op是操作类型，包括初始化、确定填充、猜测、回溯，bs是数独81字符长字符串盘面，0代表未知数，编写postgresql 语法SQL查询从初始化（第一行）到最终完成（最后一行）的路径。排除不成功的猜测。提示，用0的个数来判断深度

只告诉它需要的结果，结果它输出了3个备选SQL，都不对。

你的SQL1和SQL2都输出了全部路径，包括错误的猜测。SQL3只输出了2行，所有path都是从0、1一直连续到最后的iter。这些都不对，例如，假设正确结果是3142，tab1表记录如下

0 初始化 0100 1 猜测 2100 2 确定填充 2130 3 确定填充 2134 4 回溯 3100 <--比上一个猜测2100只有第1位不同 5 猜测 3120 6 确定填充 3124 7 回溯 3140 <--比上一个猜测3120只有第3位不同 8 确定填充 3142

希望输出

0 初始化 0100 4 回溯 3100 7 回溯 3140 8 确定填充 3142

注意路径上的后继都比前驱多一个非0的数字，其他位完全一致。只要写一个简洁的SQL，不做别的

这里我写错了一个条件，后继都比前驱多一个非0的数字，他严格执行了，结果查询到半途中断了。然后把我的思路具体化告诉它。

还是不对，我的确定填充有的会一次填充多个数字，将逻辑改为从最后一行完成结果往回搜索，每次找第一个 结果 like 模式的行，其中模式是将字符串中的0替换成_的结果。比如select '1234' like replace('1200','0','_');返回true，而select '1234' like replace('1203','0','_');返回false。只要写一个简洁的SQL，不做别的

终于输出了正确的

WITHRECURSIVE correct_pathAS(-- 起始点：最后一行（完成状态）SELECTiter,op,bsFROMtab1WHEREbsNOTLIKE'%0%'UNIONALL-- 递归往回查找：找到前一步，当前bs匹配前一步bs的模式SELECTt.iter,t.op,t.bsFROMcorrect_path cINNERJOINtab1 tONt.iter<c.iterWHEREc.bsLIKEREPLACE(t.bs,'0','_')-- 确保这是最近的前一步（iter最大的）ANDt.iter=(SELECTMAX(iter)FROMtab1WHEREiter<c.iterANDc.bsLIKEREPLACE(bs,'0','_')))SELECT*FROMcorrect_pathORDERBYiter;

用上述SQL来分析不同选点策略的正确路径，正确路径的步骤数非常相近，但找到它们花费的代价不同。

memory D .read 1230/qpath5.sql ┌───────┬──────────┬───────────────────────────────────────────────────────────────────────────────────┐ │ iter │ op │ bs │ │ int32 │ varchar │ varchar │ ├───────┼──────────┼───────────────────────────────────────────────────────────────────────────────────┤ │ 0 │ 初 始 化 │ 000000003001005600090040070000009050700000008050402000080020090003500100600000000 │ │ 1 │ 确定填充 │ 000000003001005600090040070000009050700050008050402000080020090003500100600000000 │ │ 2 │ 猜 测 │ 000000003001005600090040070000009050700050008050402300080020090003500100600000000 │ │ 3 │ 确定填充 │ 000000003001005600090040070000009050700050008050402300080020090003500100600000030 │ │ 132 │ 回 溯 │ 000000003001005600090040070000009050700050008050402360080020090003500100600000030 │ │ 234 │ 回 溯 │ 000000003001005600090040070000009050700050008059402360080020090003500100600000030 │ │ 235 │ 确定填充 │ 000000003001005609090040070000009050700050908059402360080020090903500100600000030 │ │ 236 │ 猜 测 │ 000000003001005609090040070000009050700050908059402361080020090903500100600000030 │ │ 237 │ 确定填充 │ 000000013001005609090040070000009050700050908859472361080020090903500100600000030 │ │ 238 │ 猜 测 │ 000000013001005609090040070000009050700050908859472361080020090903560100600000030 │ │ 239 │ 确定填充 │ 000000013001005609090040070000009050700050908859472361080020096903560100600000030 │ │ 240 │ 猜 测 │ 000000013001035609090040070000009050700050908859472361080020096903560100600000030 │ │ 241 │ 确定填充 │ 000000013001035609390040070000009050700050908859472361080020096903560100600000030 │ │ 255 │ 回 溯 │ 000000013401035609390040070000009050700050908859472361080020096903560100600000030 │ │ 256 │ 确定填充 │ 000000413401035609390040070000009050700050908859472361080020096903560100600000030 │ │ 260 │ 回 溯 │ 000000413471035609390040070000009050700050908859472361080020096903560100600000030 │ │ 261 │ 猜 测 │ 000000413471235609390040070000009050700050908859472361080020096903560100600000030 │ │ 262 │ 确定填充 │ 000000413471235689390040070000009050700050908859472361080020096903560100600000030 │ │ 263 │ 确定填充 │ 000000413471235689390040070000009050700050908859472361080020096903568100600000830 │ │ 264 │ 确定填充 │ 000000413471235689390040070000009050700050908859472361080020096903568107600000830 │ │ 265 │ 确定填充 │ 000000413471235689390040070000009750700050908859472361080020596903568107600000830 │ │ 266 │ 确定填充 │ 500000413471235689390040275000009750700050908859472361180020596903568107605000830 │ │ 267 │ 确定填充 │ 500000413471235689390040275200009750700050908859472361187020596903568107605000830 │ │ 268 │ 确定填充 │ 502000413471235689390040275200009754700050928859472361187324596903568107605000832 │ │ 269 │ 确定填充 │ 562000413471235689398040275236009754704053928859472361187324596923568147645000832 │ │ 270 │ 确定填充 │ 562007413471235689398046275236009754714653928859472361187324596923568147645000832 │ │ 271 │ 确定填充 │ 562007413471235689398146275236009754714653928859472361187324596923568147645701832 │ │ 272 │ 确定填充 │ 562907413471235689398146275236819754714653928859472361187324596923568147645791832 │ │ 273 │ 确定填充 │ 562987413471235689398146275236819754714653928859472361187324596923568147645791832 │ ├───────┴──────────┴───────────────────────────────────────────────────────────────────────────────────┤ │ 29 rows 3 columns │ └──────────────────────────────────────────────────────────────────────────────────────────────────────┘ memory D insert into tab1 from 't0112rev2_l.csv'; memory D .read 1230/qpath5.sql ┌───────┬──────────┬───────────────────────────────────────────────────────────────────────────────────┐ │ iter │ op │ bs │ │ int32 │ varchar │ varchar │ ├───────┼──────────┼───────────────────────────────────────────────────────────────────────────────────┤ │ 0 │ 初始填充 │ 000000003001005600090040070000009050700000008050402000080020090003500100600000000 │ │ 1 │ 确定填充 │ 000000003001005600090040070000009050700050008050402000080020090003500100600000000 │ │ 2548 │ 回溯 │ 000000003001005600090040070000009050700050008050402000087020090003500100600000000 │ │ 2549 │ 猜测填充 │ 000000003001005600090040070000009050700050008050402000087020090023500100600000000 │ │ 2602 │ 回溯 │ 000000003001005600090040070000009050700050008050402000087020090923500100600000000 │ │ 2860 │ 回溯 │ 000000003001005600090040070000009050700050008050402000087020090923500100605000000 │ │ 2861 │ 猜测填充 │ 000000003001005600090040070000009050700050008050402000187020090923500100605000000 │ │ 2862 │ 确定填充 │ 000000003001005600090040070000009050700050008050402000187020090923500100645000000 │ │ 2863 │ 猜测填充 │ 000000003001005600090040070000009050700050008050402000187320090923500100645000000 │ │ 2885 │ 回溯 │ 000000003001005600090040070000009050700050008050402000187320590923500100645000000 │ │ 2886 │ 确定填充 │ 500000003001005600090040075000009050700050008050402000187320590923500100645000000 │ │ 2887 │ 确定填充 │ 500000013001005600090040075000009050700050008050402000187320590923500100645000000 │ │ 2903 │ 回溯 │ 500000013001005600090040075000009050700050008050402000187320596923500100645000000 │ │ 2904 │ 确定填充 │ 500000013001005600090040075000009050700050008050402000187324596923500100645000000 │ │ 2931 │ 回溯 │ 500000013001005600090040075000009050700050008050402000187324596923500107645000000 │ │ 2932 │ 确定填充 │ 500000013001005600090040075000009050700050008050402000187324596923500147645000002 │ │ 2955 │ 回溯 │ 500000013001005600090040075000009050700050008050402000187324596923508147645000002 │ │ 2956 │ 确定填充 │ 500000013001005600090040075000009050700050008050402000187324596923568147645000002 │ │ 2957 │ 猜测填充 │ 500000013001005600090040075000009050700050008050402000187324596923568147645000032 │ │ 2958 │ 确定填充 │ 500000013001005680090040075000009050700050008050402060187324596923568147645000832 │ │ 2959 │ 确定填充 │ 500000013001005680090040275000009050700050028050402060187324596923568147645000832 │ │ 2960 │ 猜测填充 │ 500000013001005680090040275000009050700050028050402060187324596923568147645001832 │ │ 2961 │ 确定填充 │ 500007013001005680090140275000009050700050028050402060187324596923568147645001832 │ │ 2962 │ 确定填充 │ 560007013071005680090140275000009050710650028050402060187324596923568147645001832 │ │ 2963 │ 确定填充 │ 560007013071005680098146275036009050710653028050402060187324596923568147645001832 │ │ 2964 │ 确定填充 │ 562007013071035680398146275236009050710653028859402360187324596923568147645001832 │ │ 2965 │ 确定填充 │ 562007413471235689398146275236009750714653928859472361187324596923568147645001832 │ │ 2966 │ 确定填充 │ 562007413471235689398146275236819754714653928859472361187324596923568147645791832 │ │ 2967 │ 确定填充 │ 562987413471235689398146275236819754714653928859472361187324596923568147645791832 │ ├───────┴──────────┴───────────────────────────────────────────────────────────────────────────────────┤ │ 29 rows 3 columns │ └──────────────────────────────────────────────────────────────────────────────────────────────────────┘