{"id":2188,"date":"2021-08-02T15:18:05","date_gmt":"2021-08-02T13:18:05","guid":{"rendered":"https:\/\/magschnee.com\/?page_id=2188"},"modified":"2021-08-02T15:18:05","modified_gmt":"2021-08-02T13:18:05","slug":"sql-%e8%af%ad%e5%8f%a5%e8%ae%b0%e5%bd%95","status":"publish","type":"page","link":"https:\/\/wuyujing.com\/index.php\/sql-%e8%af%ad%e5%8f%a5%e8%ae%b0%e5%bd%95\/","title":{"rendered":"sql \u8bed\u53e5\u8bb0\u5f55"},"content":{"rendered":"\n

Excel\u8f6cJson \uff1ahttps:\/\/www.bejson.com\/json\/col2json\/<\/a><\/p>\n\n\n\n

PDF\u5206\u5272\u5de5\u5177\uff1ahttps:\/\/deftpdf.com\/zh\/split-pdf-down-the-middle<\/a><\/p>\n\n\n\n

\n\n\n\n

\u4e00\u4e2a\u65f6\u95f4\u6bb5\u5185\u7684\uff0c\u5df2\u53d1\u8d27\u8ba2\u5355\u6570\u636e\uff0c\u5305\u542b\u4e0a\u67b6\u65f6\u95f4<\/p>\n\n\n\n

SELECT \no.customer_code as '\u8d27\u4e3b\u4ee3\u7801',\no.warehouse_code as '\u4ed3\u5e93\u4ee3\u7801',\no.type_name as '\u51fa\u5e93\u7c7b\u578b',\no.business_type_name as '\u51fa\u5e93\u4e1a\u52a1\u7c7b\u578b',\no.order_no1 as '\u51fa\u5e93\u5355\u53f7',\no.created_time as '\u8ba2\u5355\u521b\u5efa\u65f6\u95f4',\no.status_name as '\u51fa\u5e93\u72b6\u6001',\no.skus as '\u5546\u54c1sku',\na.barcode as '\u5546\u54c1\u6761\u7801',\na.quantity as '\u6570\u91cf',\na.product_name as '\u5546\u54c1\u540d\u79f0',\no.shipped_time as '\u53d1\u8d27\u65f6\u95f4',\ns.confirm_time as '\u4e0a\u67b6\u786e\u8ba4\u65f6\u95f4' \nfrom out_order o \nLEFT JOIN out_inventory_allocation a ON a.out_order_id=o.id \nLEFT JOIN in_shelf s ON s.in_order_id=a.in_order_id \nWHERE \n(o.customer_code = 'ouhua-lowe' or o.customer_code = 'GTI') and \n(o.shipped_time BETWEEN '2021-08-01 00:00:00' and '2021-09-01 00:00:00') and \na.batch_no = s.batch_no \nGROUP BY o.order_no1 \nORDER BY o.customer_code<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u4e00\u4e2a\u65f6\u95f4\u6bb5\u5185\u7684\uff0c\u5c1a\u672a\u53d1\u8d27\u7684\u5728\u5e93\u4fe1\u606f\u5217\u8868\uff0c\u5305\u542b\u4e0a\u67b6\u65f6\u95f4<\/p>\n\n\n\n
SELECT \ni.customer_code,\ni.status_name,\ni.in_order_no1,\ni.sku,\ni.barcode,\ni.product_name,\ni.batch_no,\ns.created_time as '\u4e0a\u67b6\u65f6\u95f4',\ni.quality_name,\ni.qty_total as '\u603b\u6570',\ni.qty_avail as '\u53ef\u7528\u6570\u91cf',\ni.qty_lock1 as '\u5165\u5e93\u9501\u5b9a',\ni.qty_lock4 as '\u51fa\u5e93\u9501\u5b9a',\ni.location_code,\ni.remark\nfrom i_inventory_snapshot i \nLEFT JOIN in_shelf s ON s.customer_id = i.customer_id\nWHERE\ni.customer_code = 'GTI'\nand i.snapshot_date = '2021-08-01'\nand i.in_order_id = s.in_order_id\nand i.sku = s.sku\nand i.batch_no = s.batch_no\nand i.qty_total > 0\nand i.qty_lock1 = 0<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u6570\u636e\u5e93\u5bfc\u51fa\u5907\u4efd<\/p>\n\n\n\n
mysqldump -uroot -p hallonomie> \/hallonomie_0725.sql<\/code><\/pre>\n\n\n\n
\n\n\n\n
postman\u4e0b\u67b6  status_combination_code =20 \u5df2\u5206\u914d\uff0c location_code \u5e93\u4f4d<\/p>\n\n\n\n
SELECT \na.location_code,\na.id as \"allocationId\",\na.barcode,\na.out_order_id,\na.out_order_no,\na.quantity\nfrom out_order o \nLEFT JOIN out_inventory_allocation a on o.id = a.out_order_id \nWHERE \no.status_combination_code = '20' and \na.location_code = 'K79'<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u4e00\u4ef6\u4ee3\u53d1 \u6ce2\u6b21\u88c5\u7bb1\u590d\u6838 index\u5bf9\u5e94\u51fa\u5e93\u5355\u53f7\u7801<\/p>\n\n\n\n
SELECT \no.order_no1,\ne.barcode,\ni.inner_index \nfrom out_wave_item i \nLEFT JOIN out_wave w on i.out_wave_id = w.id  \nLEFT JOIN out_order o on o.id = i.out_order_id \nLEFT JOIN out_order_item e on e.out_order_id = i.out_order_id \nWHERE \nw.wave_no='1636447245901'<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u4e0a\u67b6\u8bb0\u5f55\uff08\u5305\u542b\u4e1a\u52a1\u7c7b\u578b\uff09<\/p>\n\n\n\n
SELECT \ns.warehouse_code as '\u4ed3\u5e93\u4ee3\u7801',\ns.customer_code as '\u8d27\u4e3b\u4ee3\u7801',\ns.order_no1 as '\u5165\u5e93\u5355\u53f7',\no.in_order_business_type_name as '\u4e1a\u52a1\u7c7b\u578b',\ns.created_time as '\u4e0a\u67b6\u65f6\u95f4',\ns.is_confirm as '\u786e\u8ba4\u5e93\u5b58',\ns.sku as '\u5546\u54c1SKU',\ns.barcode as '\u5546\u54c1\u6761\u7801',\ns.quantity as '\u4e0a\u67b6\u6570\u91cf',\ns.location_code as '\u4e0a\u67b6\u5e93\u4f4d',\ns.box_no as '\u7bb1\u53f7',\ns.box_qty as '\u7bb1\u6570',\ns.product_name as '\u5546\u54c1\u540d\u79f0',\ns.quality_name as '\u5546\u54c1\u54c1\u8d28',\ns.batch_no as '\u6279\u6b21\u53f7\u7801',\ns.created_by_user_code as '\u4e0a\u67b6\u4eba',\ns.confirm_by_user_code as '\u786e\u8ba4\u4eba',\ns.confirm_time as '\u786e\u8ba4\u65f6\u95f4',\ns.update_by_user_code as '\u66f4\u65b0\u4eba',\ns.update_time as '\u66f4\u65b0\u65f6\u95f4'\nfrom in_shelf s \nLEFT JOIN in_order o on o.id = s.in_order_id \nWHERE \ns.created_time > '2021-12-13 00:00:00' and \ns.created_time < '2021-12-15 00:00:00' and\ns.warehouse_code = 'de05'\nORDER BY s.created_time<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u9000\u8d27\u7b7e\u6536sku\u53ca\u518d\u5165\u5e93\u5e93\u4f4d<\/p>\n\n\n\n
SELECT r.customer_code,r.out_order_no,i.sku,i.quantity,r.created_time,r.in_order_no,o.status_name,y.location_code\nfrom out_return_order r \nLEFT JOIN out_order_item i on i.out_order_id=r.out_order_id \nLEFT JOIN in_order o on r.in_order_id=o.id\nLEFT JOIN i_inventory y on y.in_order_id=r.in_order_id and i.sku=y.sku\nWHERE r.customer_code = 'leqi'<\/code><\/pre>\n\n\n\n
\n\n\n\n
word\u6279\u91cf\u5904\u7406\u56fe\u7247\u5927\u5c0f<\/p>\n\n\n\n
Sub FormatPics()\n\nDim Shap As InlineShape\n\nFor Each Shap In ActiveDocument.InlineShapes\n\nIf Shap.Type = wdInlineShapePicture Then\n\nShap.LockAspectRatio = msoFalse '\u4e0d\u9501\u5b9a\u7eb5\u6a2a\u6bd4\n\nShap.Width = CentimetersToPoints(25) '\u5bbd10CM\n\nShap.Height = CentimetersToPoints(15) '\u9ad87CM\n\nEnd If\n\nNext\n\nEnd Sub\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u65f6\u95f4\u6bb5\u5185\u51fa\u5e93\u7269\u54c1\u4f53\u79ef<\/p>\n\n\n\n
SELECT \no.customer_code as '\u8d27\u4e3b',\no.order_no1 as '\u51fa\u5e93\u5355\u53f7',\no.status_name as '\u51fa\u5e93\u5355\u72b6\u6001',\no.shipped_time as '\u53d1\u8d27\u65f6\u95f4',\ni.sku as 'sku',\nb.barcode as '\u6761\u7801',\ni.quantity as '\u6570\u91cf',\np.length as '\u957fcm',\np.width as '\u5bbdcm',\np.height as '\u9ad8cm',\np.length*p.width*p.height\/1000000 as '\u5355\u4ef6\u4f53\u79efm\u00b3',\ni.quantity*p.length*p.width*p.height\/1000000 as '\u603b\u4f53\u79efm\u00b3',\np.product_name as '\u5546\u54c1\u540d\u79f0'\nfrom out_order o\nLEFT JOIN out_order_item i on i.out_order_id = o.id\nLEFT JOIN b_product p on i.sku=p.sku and i.customer_id=p.customer_id\nLEFT JOIN b_product_barcode b on b.sku = p.sku and b.customer_id = p.customer_id\nWHERE o.customer_code='bjealing' \nand o.status_code>=110\nand o.shipped_time>'2021-08-01 00:00:00' and o.shipped_time<'2022-01-01 00:00:00'\nORDER BY shipped_time<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u6708\u521d\u5728\u5e93\u5546\u54c1\u4f53\u79ef<\/p>\n\n\n\n
SELECT s.customer_code as '\u8d27\u4e3b\u4ee3\u7801',\ns.snapshot_date as '\u5feb\u7167\u65e5\u671f',\ns.in_order_no1 as '\u5165\u5e93\u5355\u53f7',\ns.batch_no as '\u6279\u6b21\u53f7',\ns.sku as 'sku',\ns.barcode as '\u5546\u54c1\u6761\u7801',\ns.qty_total as '\u5546\u54c1\u603b\u6570',\np.length as '\u957fcm',\np.width as '\u5bbdcm',\np.height as '\u9ad8cm',\np.length*p.width*p.height\/1000000 as '\u5355\u4ef6\u4f53\u79efm\u00b3',\ns.qty_total*p.length*p.width*p.height\/1000000 as '\u603b\u4f53\u79efm\u00b3',\np.product_name as '\u4ea7\u54c1\u540d\u79f0',\ns.location_code as '\u5e93\u4f4d'\nFROM i_inventory_snapshot s \nLEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id\nWHERE (snapshot_date  ='2021-08-01' or snapshot_date  ='2021-09-01' or snapshot_date  ='2021-10-01' or snapshot_date  ='2021-11-01' or snapshot_date  ='2021-12-01') and qty_total>0 and s.customer_code='bjealing' ORDER BY snapshot_date<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u5f53\u524d\u5e93\u5b58\u5546\u54c1\u4f53\u79ef<\/p>\n\n\n\n
SELECT s.customer_code as '\u8d27\u4e3b\u4ee3\u7801',\ns.in_order_no1 as '\u5165\u5e93\u5355\u53f7',\ns.batch_no as '\u6279\u6b21\u53f7',\ns.sku as 'sku',\ns.barcode as '\u5546\u54c1\u6761\u7801',\ns.qty_total as '\u5546\u54c1\u603b\u6570',\np.length as '\u957fcm',\np.width as '\u5bbdcm',\np.height as '\u9ad8cm',\np.length*p.width*p.height\/1000000 as '\u5355\u4ef6\u4f53\u79efm\u00b3',\ns.qty_total*p.length*p.width*p.height\/1000000 as '\u603b\u4f53\u79efm\u00b3',\np.product_name as '\u4ea7\u54c1\u540d\u79f0',\ns.location_code as '\u5e93\u4f4d'\nFROM i_inventory s \nLEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id\nWHERE qty_total>0 and s.customer_code='bjealing'<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u5165\u5e93\u4f53\u79ef<\/p>\n\n\n\n
SELECT o.order_no1,r.sku,r.quantity,p.length*p.width*p.height\/1000000,r.quantity*p.length*p.width*p.height\/1000000,r.created_time,p.product_name from in_receipt r\nLEFT JOIN b_product p on r.sku=p.sku and r.customer_id=p.customer_id\nLEFT JOIN in_order o on o.id=r.in_order_id\nWHERE r.created_time>'2021-11-01 00:00:00' and r.created_time<'2021-12-01 00:00:00' and r.customer_code='bjealing'<\/code><\/pre>\n\n\n\n
\n\n\n\n
0-30\u5929\u514d\u4ed3\u79df\u8d39\u7528<\/p>\n\n\n\n
SELECT customer_code,in_order_id,sku,barcode,batch_no,instock_time,created_time,instock_days,qty_total,volume_m,qty_total*0.6*volume_m as '\u4ed3\u79df' from f_warehouse_rent_detail WHERE customer_code='bjealing' and created_time>='2021-12-01 00:00:00' and created_time<='2021-12-31 23:59:59'and instock_days<=30 ORDER BY in_order_id,sku,batch_no,created_time,instock_days<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u9a6c\u54e5\u7199\u5b87 \u9762\u5355\u6c47\u603b\u67e5\u8be2(\u6279\u91cf\u7533\u8bf7)<\/p>\n\n\n\n
SELECT o.warehouse_code,o.customer_code,o.order_no1,o.status_name,o.created_time,\nb.sku,i.gross_weight,b.product_quantity,o.shipway_code,b.waybill_quantity,(i.gross_weight*b.product_quantity\/b.waybill_quantity) as '\u8fd0\u5355\u91cd\u91cf',\nREPLACE (\n        JSON_EXTRACT (b.apply_result, '$[*].trackingNo'),\n        '\"',\n        ''\n    ) AS '\u5feb\u9012\u5355\u53f7',\nb.created_time\nfrom out_order_apply_waybill_batches b \nLEFT JOIN out_order o on o.id=b.out_order_id \nLEFT JOIN out_order_item i on i.out_order_id=o.id and i.sku=b.sku\nLEFT JOIN b_product p on p.sku=b.sku and p.customer_code=o.customer_code\nleft join f_out_order_fee f on f.out_order_number = o.order_no1\nWHERE b.created_time>'2022-01-01 00:00:00' and o.shipway_code='DHL'\nORDER BY o.warehouse_code,o.customer_code,o.order_no1<\/code><\/pre>\n\n\n\n
Excel \u6bcf\u4e2a\u9762\u5355\u4e00\u884c VBA<\/p>\n\n\n\n
Sub aa()\nDim shp As Shape\nDim iCount As Long\nDim arr As Variant\n\niCount = Sheet1.[A65536].End(xlUp).Row\nFor i = 2 To iCount\nSheet1.Range(\"J\" & i).Select\narr = Split(Sheet1.Range(\"L\" & i).Value, \", \")\n\nFor r = 1 To Sheet1.Range(\"J\" & i).Value\niCount2 = Sheet2.[A65536].End(xlUp).Row\nRows(i).Copy Sheet2.Rows(iCount2 + 1)\nSheet2.Range(\"L\" & (iCount2 + 1)).NumberFormatLocal = \"@\"\nSheet2.Range(\"L\" & (iCount2 + 1)).Value = Replace(Replace(arr(r - 1), \"[\", \"\"), \"]\", \"\")\n\n\nNext\n\nNext\n\nEnd Sub\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u9a6c\u54e5\u7199\u5b87 \u9762\u5355\u6c47\u603b\u67e5\u8be2(\u5355\u4e2a\u7533\u8bf7)<\/p>\n\n\n\n
SELECT * from out_order_apply_tracking_no n \n\nLEFT JOIN out_order o on o.id=n.out_order_id \n\nWHERE n.out_order_id NOT IN (SELECT out_order_id from out_order_apply_waybill_batches)\n\nAND n.created_time BETWEEN '2021-04-01 00:00:00' AND '2022-01-01 00:00:00'<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u5305\u88f9\u8f6c\u8fd0\u5165\u5e93\u8d39\u7528\uff0c\u52a0\u4e0asku<\/p>\n\n\n\n
SELECT f.order_no1,f.receipt_time,expression, group_concat(i.sku Separator ';') \nfrom f_in_order_fee f LEFT JOIN in_order_item i on f.in_order_id=i.in_order_id\nWHERE \nf.customer_code='GTI' \nand in_order_business_type_name='\u5305\u88f9\u8f6c\u8fd0' \nand receipt_time BETWEEN '2022-03-01 00:00:00' and '2022-04-01 00:00:00'  \nGROUP BY f.order_no1,expression\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
excel\u8ba1\u7b97\u6708\u5de5\u65f6<\/p>\n\n\n\n
\n'\u8ba1\u7b97\u5c0f\u65f6\u6570\n\nSub subline(i)\n\nRange(\"A\" & i).Select\n\nDim datum, kommen, gehen\n\ndatum = Range(\"A\" & i).Text\ndatum = Replace(datum, \".\", \"\/\")\nkommen = datum & \" \" & Range(\"B\" & i).Text & \":00\"\n\ngehen = datum & \" \" & Range(\"C\" & i).Text & \":00\"\n\n'If IsDate(datum) And IsDate(kommen) And IsDate(gehen) And IsDate(Range(\"B\" & i).Text) And IsDate(Range(\"C\" & i).Text) Then\nIf IsDate(datum) And IsDate(Range(\"B\" & i).Text) And IsDate(Range(\"C\" & i).Text) Then\n\nElse\n\nGoTo LastLine\n\nEnd If\n\nDim k, g, kh, gh As Date\n\nk = CDate(kommen)\n\ng = CDate(gehen)\n\nkh = Range(\"B\" & i).Text\n\ngh = Range(\"C\" & i).Text\n\nIf IsDate(k) And IsDate(g) And IsDate(kh) And IsDate(gh) Then\n\nElse\n\nGoTo LastLine\n\nEnd If\n\n\n\nIf k > g Then\n\ng = DateAdd(\"D\", 1, g)\n\nEnd If\n\nDim hourDiff\n\nhourDiff = DateDiff(\"n\", k, g) \/ 60\n\nRange(\"H\" & i).Value = hourDiff\n\n'\u8ba1\u7b97\u4f11\u606f\u65f6\u95f4\n\nDim restTime\n\nrestTime = 0\n\nIf kh > gh Then\n\nrestTime = 1\n\nGoTo FullRest\n\nEnd If\n\n'\u5230\u7684\u65f6\u95f4\u572810\uff1a30\u4e4b\u524d\n\nIf kh < CDate(\"10:30:00\") Then\n\n    If gh >= CDate(\"11:00:00\") And gh < CDate(\"13:00:00\") Then\n    \n    restTime = 0.25\n    \n    ElseIf gh >= CDate(\"13:00:00\") And gh < CDate(\"16:00:00\") Then\n    \n    restTime = 0.75\n    \n    ElseIf gh >= CDate(\"16:00:00\") Then\n    \n    restTime = 1\n    \n    End If\n        \nElseIf kh >= CDate(\"10:30:00\") And kh < CDate(\"13:00:00\") Then\n\n    If gh >= CDate(\"13:00:00\") And gh < CDate(\"16:00:00\") Then\n    \n    restTime = 0.5\n    \n    ElseIf gh >= CDate(\"16:00:00\") Then\n    \n    restTime = 0.75\n    \n    End If\n    \nElseIf kh >= CDate(\"13:00:00\") And kh < CDate(\"15:00:00\") Then\n\n    If gh >= CDate(\"16:00:00\") Then\n    \n    restTime = 0.25\n    \n    End If\n\nEnd If\n\nFullRest:\n\nRange(\"I\" & i).Value = restTime\n\n'\u8ba1\u7b97\u5b9e\u9645\u5de5\u4f5c\u65f6\u957f\n\nRange(\"E\" & i).Value = hourDiff - restTime\n\nLastLine:\n\nEnd Sub\n\nSub main()\n\nRange(\"H\" & 6).Value = \"\u603b\u5c0f\u65f6\u6570\"\n\nRange(\"I\" & 6).Value = \"\u4f11\u606f\u65f6\u957f\"\n\nFor i = 11 To 42\n\nsubline (i)\n\nNext\n\nEnd Sub\n\n\n\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
Alien DPD Excel\u6587\u4ef6\u5904\u7406 \uff08\u6bcf\u6761\u5feb\u9012\u5355\u53f7\u4e00\u884c\uff09<\/p>\n\n\n\n
Sub aa()\nDim shp As Shape\nDim iCount As Long\nDim arr As Variant\n\niCount = Sheet1.[A1048576].End(xlUp).Row\nFor i = 2 To iCount\n\n    arr = Split(Sheet1.Range(\"E\" & i).Value, \",\")\n    arrlength = UBound(arr) - LBound(arr)\n   \n    If arrlength = -1 Then\n        arrlength = 1\n        arr = Array(\"\")\n    End If\n       \n    For r = 1 To arrlength\n        iCount2 = Sheet2.[A1048576].End(xlUp).Row\n        Sheet1.Rows(i).Copy Sheet2.Rows(iCount2 + 1)\n        Sheet2.Range(\"E\" & (iCount2 + 1)).NumberFormatLocal = \"@\"\n        Sheet2.Range(\"E\" & (iCount2 + 1)).Value = Replace(Replace(arr(r - 1), \"[\", \"\"), \"]\", \"\")\n    Next\n\nNext\n\nEnd Sub<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Excel\u8f6cJson \uff1ahttps:\/\/www.bejson.com\/json\/col2json\/ PDF\u5206\u5272 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2188","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/pages\/2188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/comments?post=2188"}],"version-history":[{"count":0,"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/pages\/2188\/revisions"}],"wp:attachment":[{"href":"https:\/\/wuyujing.com\/index.php\/wp-json\/wp\/v2\/media?parent=2188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}