iT邦幫忙

0

BOM 產品結構連接符號

bom
  • 分享至 

  • xImage

圖一:
https://ithelp.ithome.com.tw/upload/images/20230705/20119662W7virrUrGp.png
圖二:
https://ithelp.ithome.com.tw/upload/images/20230705/20119662f2YrOEbOOW.png

這是產品多階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指令遞迴就出來了,劃連接線,畫了好幾天,出不來

rogeryao iT邦超人 7 級 ‧ 2023-07-05 16:24:57 檢舉
請先建好料號資料
ckp6250 iT邦好手 1 級 ‧ 2023-07-05 16:35:29 檢舉
有提供了,就是最後那個連結,裡面是 MYSQL TABLE
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

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

看更多先前的回應...收起先前的回應...
尼克 iT邦大師 1 級 ‧ 2023-07-06 09:36:40 檢舉

好心人

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

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

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

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

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

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

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

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

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

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
rogeryao iT邦超人 7 級 ‧ 2023-07-06 16:44:43 檢舉

您有修改過程式嗎?和我之前看的不太一樣, <== Yes
【│】線條不見了,我印象中第一次看時,是有的 <== Yes
兩種方法排列順序是一樣的,都有個瑕疵 => 排列順序不對

我是用你原本的 SQL 改的,因為你說只差了畫線

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

rogeryao
是我的錯,我沒有把程式碼完整貼出來,十分對不起。
您能幫忙把【├、│、└】這三個線條都放上去,就像圖一那樣嗎?包括排列順序。

rogeryao iT邦超人 7 級 ‧ 2023-07-07 11:43:11 檢舉

https://ithelp.ithome.com.tw/upload/images/20230718/20085021cJRsUXcNp3.png

0
brainpeople
iT邦見習生 ‧ 2024-07-24 10:11:49

實際上,透過您分享的訊息,您可以從不同的資訊來源中學習。這真是太神奇了。享受程式的創造力和開發。充分利用可靠的新聞來源 pizza tower

我要發表回答

立即登入回答