圖一:
圖二:
這是產品多階BOM展開結構表,
客戶希望像圖一那樣,有連接線,圖二是我自己產出的,我只能做到縮排當做不同階層之表示,我做不出連接線,因為每一階的結尾和開頭和過程,線條都不一樣,不知道該怎麼做?
連接線主要是由【├、│、└】這三個符號組成,我希望在 sql 中就能達成,不要拉到前端跑迴圈。(若真的sql做不到,那麼用PHP也無妨了),
各位先進有什麼好點子嗎?
底下是我的SQL語法
WITH RECURSIVE tmp AS (
SELECT
bom_tmp.item,
bom_tmp.part,
cast(
concat( bom_tmp.item, ',', bom_tmp.part ) AS CHAR ( 256 )) AS Label
FROM
bom_tmp
WHERE
bom_tmp.item = '11433-2000-D' UNION ALL
SELECT
f.item,
f.part,
cast(
concat( a.label, ',', f.part ) AS CHAR ( 256 )) AS label
FROM
bom_tmp AS f,
tmp AS a
WHERE
f.item = a.part
) SELECT
Row_Number() OVER ( ORDER BY label ) AS Id,
tmp.*
FROM
tmp
ORDER BY
label
底下是BOM檔
https://drive.google.com/file/d/1L2ucgzJEEjQx6WiKUDWjjMctUnx6i-MB/view?usp=drive_link
做出bom只花卅秒,幾行SQL指令遞迴就出來了,劃連接線,畫了好幾天,出不來
CREATE TABLE `bom_tmp` (
`item` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '父階件號',
`part` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '子階件號',
PRIMARY KEY (`item`, `part`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bom_tmp
-- ----------------------------
INSERT INTO `bom_tmp` VALUES ('11401-3412-1', '90514-20299O');
INSERT INTO `bom_tmp` VALUES ('11409-3320', '11409-3321-A');
INSERT INTO `bom_tmp` VALUES ('11409-3320', '11409-3322');
INSERT INTO `bom_tmp` VALUES ('11409-3416-1', 'L608M');
INSERT INTO `bom_tmp` VALUES ('11413-2400-1', '11413-3400-1');
INSERT INTO `bom_tmp` VALUES ('11413-2400-1', '11502-3502-A');
INSERT INTO `bom_tmp` VALUES ('11413-3400-1', '11409-3416-1');
INSERT INTO `bom_tmp` VALUES ('11413-3400-1', '11413-3410-1');
INSERT INTO `bom_tmp` VALUES ('11413-3410-1', '11401-3412-1');
INSERT INTO `bom_tmp` VALUES ('11413-3410-1', '11413-3411-1');
INSERT INTO `bom_tmp` VALUES ('11413-3411-1', '90505-45238O');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3320');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3332-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11409-3333-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '11426-2311-2');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '14402-3335-B');
INSERT INTO `bom_tmp` VALUES ('11426-2300-A', '41101-3334');
INSERT INTO `bom_tmp` VALUES ('11426-2311-2', '11426-3313');
INSERT INTO `bom_tmp` VALUES ('11426-3207', '90512-16348E');
INSERT INTO `bom_tmp` VALUES ('11433-2000-D', '11426-2950-A');
INSERT INTO `bom_tmp` VALUES ('11433-2000-D', '11433-2100-C');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11101-3503');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11101-3504');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11103-3702');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-2400-1');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-2501-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11413-3400-1');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11426-2300-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11433-2200-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-2520-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-2531-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3611');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3613');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '11502-3615');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3621');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3901');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '12501-3902-A');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', '14301-3701');
INSERT INTO `bom_tmp` VALUES ('11433-2100-C', 'CF241249-G01');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11401-3202-A');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11426-3207');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11433-2201');
INSERT INTO `bom_tmp` VALUES ('11433-2200-A', '11433-2P');
INSERT INTO `bom_tmp` VALUES ('11433-2201', '90504-29330E');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11401-3203');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11401-3204');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11433-3209');
INSERT INTO `bom_tmp` VALUES ('11433-2P', '11502-2205');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-2521-1');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-2522-1');
INSERT INTO `bom_tmp` VALUES ('11502-2520-A', '11502-3523-A');
SET FOREIGN_KEY_CHECKS = 1;
WITH RECURSIVE tmp AS (
SELECT 1 AS 'No1',
bom_tmp.item,bom_tmp.part
FROM bom_tmp
WHERE bom_tmp.item = '11433-2000-D'
UNION ALL
SELECT No1 + 1 AS 'No1',
f.item,f.part
FROM bom_tmp AS f,tmp AS a
WHERE f.item = a.part),
tmp2 AS (
SELECT *,
Row_Number() OVER (ORDER BY No1,item,part) AS Id,
Max(No1) OVER () AS No1_Max,
Row_Number() OVER (PARTITION BY No1,item ORDER BY No1,item,part) AS No2
FROM tmp),
tmp3 AS (
SELECT *,
Max(No2) OVER (PARTITION BY No1,item ORDER BY No1,item) AS No2_Max
FROM tmp2)
SELECT Id,item,part,No1,No1_Max,No2,No2_Max,
CASE WHEN No2 = No2_Max THEN '└'
WHEN No2 <> No2_Max THEN '├' END AS Mark
FROM tmp3
ORDER BY No1,item,part
Demo
套到你的程式後在自行調整吧
好心人
既然 pdf 都做得出來了...
承上題
請提供 BOM 表查詢網頁(階層式+可展開收合)
請提供 BOM 表查詢網頁(階層式+可展開收合)
大人饒命啊,pdf + 連接線條,已經去掉半條命了,
(可展開收合) 就沒本事了。
rogeryao
十分感恩,我和我們工程師研究了幾天,想破頭都想不出來,
您有修改過程式嗎?和我之前看的不太一樣,
目前修改後的,有二個小問題,
請再幫忙調整,感激不盡。
補上我的程式碼,排序應該是父階底下的子階都跑完了,才跑下一個父階,如圖一。
WITH RECURSIVE tmp AS (
SELECT
bom_tmp.item,
bom_tmp.part,
cast(
concat( bom_tmp.item, ',', bom_tmp.part ) AS CHAR ( 256 )) AS Label
FROM
bom_tmp
WHERE
bom_tmp.item = '11433-2000-D' UNION ALL
SELECT
f.item,
f.part,
cast(
concat( a.label, ',', f.part ) AS CHAR ( 256 )) AS label
FROM
bom_tmp AS f,
tmp AS a
WHERE
f.item = a.part
) SELECT
Row_Number() OVER ( ORDER BY label ) AS Id,
tmp.*
FROM
tmp
ORDER BY
label
您有修改過程式嗎?和我之前看的不太一樣, <== Yes
【│】線條不見了,我印象中第一次看時,是有的 <== Yes
兩種方法排列順序是一樣的,都有個瑕疵 => 排列順序不對
我是用你原本的 SQL 改的,因為你說只差了畫線
rogeryao
是我的錯,我沒有把程式碼完整貼出來,十分對不起。
您能幫忙把【├、│、└】這三個線條都放上去,就像圖一那樣嗎?包括排列順序。
實際上,透過您分享的訊息,您可以從不同的資訊來源中學習。這真是太神奇了。享受程式的創造力和開發。充分利用可靠的新聞來源 pizza tower