BOM 產品結構連接符號

bom

ckp6250 2023-07-05 14:57:47 ‧ 1409 瀏覽

分享至

圖一：

圖二：

這是產品多階ＢＯＭ展開結構表，
客戶希望像圖一那樣，有連接線，圖二是我自己產出的，我只能做到縮排當做不同階層之表示，我做不出連接線，因為每一階的結尾和開頭和過程，線條都不一樣，不知道該怎麼做？
連接線主要是由【├、│、└】這三個符號組成，我希望在 sql 中就能達成，不要拉到前端跑迴圈。（若真的sql做不到，那麼用PHP也無妨了），
各位先進有什麼好點子嗎？

底下是我的ＳＱＬ語法

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指令遞迴就出來了，劃連接線，畫了好幾天，出不來

rogeryao iT邦超人 7 級 ‧ 2023-07-05 16:24:57 檢舉

請先建好料號資料

ckp6250 iT邦好手 1 級 ‧ 2023-07-05 16:35:29 檢舉

有提供了，就是最後那個連結，裡面是 MYSQL TABLE

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2023-07-05 22:49:33

最佳解答

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
套到你的程式後在自行調整吧

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

尼克 iT邦大師 1 級 ‧ 2023-07-06 09:36:40 檢舉

好心人

海綿寶寶 iT邦大神 1 級 ‧ 2023-07-06 10:48:46 檢舉

既然 pdf 都做得出來了...
承上題
請提供 BOM 表查詢網頁(階層式+可展開收合)

ckp6250 iT邦好手 1 級 ‧ 2023-07-06 11:31:49 檢舉

請提供 BOM 表查詢網頁(階層式+可展開收合)

大人饒命啊，pdf + 連接線條，已經去掉半條命了，
(可展開收合) 就沒本事了。

ckp6250 iT邦好手 1 級 ‧ 2023-07-06 16:28:12 檢舉

rogeryao
十分感恩，我和我們工程師研究了幾天，想破頭都想不出來，
您有修改過程式嗎？和我之前看的不太一樣，
目前修改後的，有二個小問題，

排列順序不對，不像圖一之順序。例如L608M應該在第9列。
圖一中的【│】線條不見了，我印象中第一次看時，是有的。

請再幫忙調整，感激不盡。

補上我的程式碼，排序應該是父階底下的子階都跑完了，才跑下一個父階，如圖一。

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