{"id":1224,"date":"2019-06-20T08:35:55","date_gmt":"2019-06-20T00:35:55","guid":{"rendered":"http:\/\/www.hefeiyu.com\/?p=1224"},"modified":"2024-03-19T17:27:36","modified_gmt":"2024-03-19T09:27:36","slug":"tiptop-bom%e5%b1%95%e5%b0%be%e9%98%b6sql","status":"publish","type":"post","link":"https:\/\/www.hefeiyu.com\/?p=1224","title":{"rendered":"TIPTOP BOM\u5c55\u5f00\u5c3e\u9636SQL\uff0c\u9636\u5c42\u5c55\u5f00\uff0c\u5c42\u6b21\u5c55\u5f00"},"content":{"rendered":"

\u4e3b\u6599\u5f80\u4e0b\u5c55\u5f00<\/span><\/strong><\/span>
\nSELECT bmb01,bmb02,bmb03,bmb06,ima18,numnn
\nFROM
\n(
\nSELECT bmb01,bmb02,bmb03,(bmb06\/NVL(bmb07,1)) bmb06,LEVEL as numnn
\nFROM bmb_file t
\nSTART WITH t.bmb01 = ‘VHT-HT74012’
\nAND (bmb04 <=SYSDATE OR bmb04 IS NULL)
\nAND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nCONNECT BY PRIOR bmb03 = bmb01 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nORDER BY LEVEL,bmb01
\n) a ,ima_file c
\nWHERE a.bmb03 = c.ima01<\/p>\n

\u539f\u6599\u5f80\u4e0a\u5c55<\/span><\/strong><\/span>
\nSELECT bmb01,bmb02,bmb03,bmb06,ima18,numnn
\nFROM
\n(SELECT bmb01,bmb02,bmb03,(bmb06\/NVL(bmb07,1)) bmb06,LEVEL as numnn
\nFROM bmb_file t
\nSTART WITH t.bmb03 = ‘BLX5C99A46-54O’
\nAND (bmb04 <=SYSDATE OR bmb04 IS NULL)
\nAND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nCONNECT BY PRIOR bmb01=bmb03 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nORDER BY LEVEL,bmb01
\n) a ,ima_file c
\nWHERE a.bmb03 = c.ima01<\/p>\n

\u53e6\u4e00\u79cd\u5199\u6cd5\u3002<\/span><\/strong><\/span><\/p>\n

select ‘\u7b2c ‘||a.L||’ \u5c42’ \u5c42\u7ea7,(case (L)
\nwhen 0 then ‘0’
\nwhen 1 then ‘.1’
\nwhen 2 then ‘..2’
\nwhen 3 then ‘…3’
\nwhen 4 then ‘….4’
\nwhen 5 then ‘…..5’
\nwhen 6 then ‘……6’
\nwhen 7 then ‘…….7′
\nend) Lev,
\na.bmb03 \u6599\u53f7,ima02 \u54c1\u540d,ima021 \u89c4\u683c,a.bmb06 \u7ec4\u6210\u7528\u91cf,a.bmb07 \u5e95\u6570,
\na.bmb08 \u635f\u8017\u7387,a.bmb10 \u53d1\u6599\u5355\u4f4d,ima08 \u6765\u6e90\u7801,ima18 \u4ea7\u54c1\u5355\u91cd
\nfrom (
\nselect 0 L,bma01 bmb03,0 bmb06,0 bmb07,0 bmb08,” bmb10
\nfrom bma_file
\nwhere bma01=’0055-JG01-000J’
\nUNION
\nselect level L,bmb03,bmb06,bmb07,bmb08,bmb10
\nfrom bmb_file
\nwhere bmb05 is null
\nstart with bmb01= ‘0055-JG01-000J’
\nconnect by prior bmb03=bmb01) a,ima_file
\nwhere a.bmb03=ima01<\/p>\n

 <\/p>\n

\u60f3\u8981\u56fa\u5b9a\u4e00\u5217\u7528\u6765\u663e\u793a\u6700\u9876\u5c42\u6599\u53f7<\/span><\/strong><\/span><\/p>\n

SELECT LAG(bma01 ignore nulls,1,bma01) OVER (ORDER BY numnn) AS topitem,bmb01,bmb02,bmb03,ima02,ima021,ima25,bmb06,ima18,numnn FROM (
\nSELECT (SELECT MAX(bma01) FROM bma_file WHERE bma01=bmb01 AND numnn=1) bma01,bmb01,bmb02,bmb03,ima02,ima021,ima25,bmb06,ima18,numnn
\nFROM (
\nSELECT bmb01,bmb02,bmb03,(bmb06\/NVL(bmb07,1)) bmb06,LEVEL as numnn
\nFROM bmb_file t
\nSTART WITH t.bmb01 =’1CCWF4497B00000′
\nAND (bmb04 <=SYSDATE OR bmb04 IS NULL)
\nAND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nCONNECT BY PRIOR bmb03 = bmb01 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
\nORDER BY LEVEL,bmb01
\n) a ,ima_file c
\nWHERE a.bmb03 = c.ima01)<\/p>\n

 <\/p>\n

\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a\u8d6b\u975e\u57df<\/a> » TIPTOP BOM\u5c55\u5f00\u5c3e\u9636SQL\uff0c\u9636\u5c42\u5c55\u5f00\uff0c\u5c42\u6b21\u5c55\u5f00<\/a><\/p>","protected":false},"excerpt":{"rendered":"

\u4e3b\u6599\u5f80\u4e0b\u5c55\u5f00 SELECT bmb01,bmb02,bmb03,bmb06,ima18,numnn FROM ( SELECT bmb01,bmb02,bmb03,(bmb06\/NVL(bmb07,1)) bmb06,LEVEL as numnn F […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[437],"class_list":["post-1224","post","type-post","status-publish","format-standard","hentry","category-unix","tag-bmb"],"_links":{"self":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1224"}],"version-history":[{"count":4,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1224\/revisions"}],"predecessor-version":[{"id":9511,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/1224\/revisions\/9511"}],"wp:attachment":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}