\u69cb\u9020\u5316CSV\u306b\u3088\u308b\u30a4\u30f3\u30dc\u30a4\u30b9\u8868\u73fe\u3068SQL\u5b9a\u7fa9<\/h1>\n\nChatGPT (\u7de8\u96c6 \u4e09\u5206\u4e00\u4fe1\u4e4b\uff09<\/span>
\n2025-05-31<\/span>\n<\/div>\n<\/div>\n\n\n\n\n\n\u3053\u306e\u30d6\u30ed\u30b0\u8a18\u4e8b\u3067\u306f\u3001\u30a4\u30f3\u30dc\u30a4\u30b9\u30c7\u30fc\u30bf\u3092\u69cb\u9020\u5316CSV\u3068\u3057\u3066\u5b9a\u7fa9\u3057\u3001MySQL\u4e0a\u306b\u518d\u73fe\u30fb\u6d3b\u7528\u3059\u308b\u305f\u3081\u306eSQL\u5b9a\u7fa9\u3068\u305d\u306e\u4f7f\u3044\u65b9\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002\u691c\u8a3c\u74b0\u5883\u306f\u3001Windows 11\u306eMySQL Server 8.0.71\u3067\u3059\u3002\n<\/p><\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n
\n1. \u69cb\u9020\u5316CSV\u306e\u7279\u5fb4\u3068\u6d3b\u7528\u65b9\u6cd5<\/h2>\n\n\n\u5f93\u6765\u306e\u30a2\u30d7\u30ea\u958b\u767a\u3067\u306f\u3001\u5b9a\u7fa9\u3057\u3066\u3044\u308b\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c6\u30fc\u30d6\u30eb\u3092\u5358\u7d14\u306a\u30af\u30a8\u30ea\u30fc\u3067\u30c0\u30f3\u30d7\u3057\u305fCSV\u304c\u30a4\u30f3\u30bf\u30fc\u30d5\u30a7\u30a4\u30b9\u3068\u3057\u3066\u4f7f\u7528\u3055\u308c\u3066\u3044\u305f\u305f\u3081\u3001\u91cd\u8907\u30c7\u30fc\u30bf\u3092\u542b\u3080\u30c6\u30fc\u30d6\u30eb\u304b\u91cd\u8907\u3092\u56de\u907f\u3057\u305f\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u51fa\u529b\u3057\u3066\u3044\u307e\u3057\u305f\u3002<\/p>\n<\/div>\n
\n\u69cb\u9020\u5316CSV\u3067\u306f\u3001\u91cd\u8907\u306e\u306a\u3044\u4e00\u3064\u306e\u30d5\u30a1\u30a4\u30eb\u3067\u968e\u5c64\u69cb\u9020\u306e\u3042\u308b\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u307e\u305f\u3001\u51fa\u529b\u3055\u308c\u305f\u69cb\u9020\u5316CSV\u3092\u8aad\u307f\u8fbc\u3093\u3060\u30c6\u30fc\u30d6\u30eb\u3092\u5bfe\u8c61\u306b\u968e\u5c64\u69cb\u9020\uff08\u30c7\u30a3\u30e1\u30f3\u30b7\u30e7\u30f3\uff09\u60c5\u5831\u3092\u9078\u629e\u3059\u308b\u3053\u3068\u3067\u5bb9\u6613\u306b\u30c7\u30fc\u30bf\u3092\u30a2\u30d7\u30ea\u5bfe\u5fdc\u306b\u5fa9\u5143\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<\/div>\n
\n\u3053\u306e\u8a18\u4e8b\u3067\u306f\u3001<\/p>\n<\/div>\n
\n\n- \n
\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3069\u306e\u3088\u3046\u306a\u6761\u4ef6\u3067\u69cb\u9020\u5316CSV\u3092\u51fa\u529b\u3059\u308b\u305f\u3081\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u5b9a\u7fa9\u3059\u308b\u304b<\/p>\n<\/li>\n
- \n
\u69cb\u9020\u5316CSV\u306b\u5bfe\u5fdc\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3069\u306e\u3088\u3046\u306a\u6761\u4ef6\u3092\u6307\u5b9a\u3057\u3066\u5143\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u76f8\u5f53\u3059\u308b\u60c5\u5831\u3092\u62bd\u51fa\u3059\u308b\u304b<\/p>\n<\/li>\n<\/ul>\n<\/div>\n
\n\u3092SQL\u6587\u3092\u4f7f\u7528\u3057\u3066\u89e3\u8aac\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n2. \u30c6\u30b9\u30c8\u74b0\u5883<\/h2>\n\n\n2.1. \u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/h3>\n\n\u4ee5\u4e0b\u306b\u3001Buyer\uff08\u8cb7\u3044\u624b\uff09\u3001Seller\uff08\u58f2\u308a\u624b\uff09\u3001Invoice\uff08\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\uff09\u3001TaxBreakdown\uff08\u7a0e\u533a\u5206\u5185\u8a33\uff09\u3001InvoiceLine\uff08\u660e\u7d30\u884c\uff09\u3001Item\uff08\u5546\u54c1\uff09\u306e6\u3064\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u5b9a\u7fa9\u3057\u307e\u3059\u3002\u65e5\u672c\u306e\u9069\u683c\u8acb\u6c42\u66f8\u767a\u884c\u4e8b\u696d\u8005\u756a\u53f7\uff08”T”\uff0b12\u6841\uff09\u306b\u5bfe\u5fdc\u3059\u308b\u5236\u7d04\u3082\u8ffd\u52a0\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u8cb7\u3044\u624b\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8cb7\u3044\u624b\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Buyer (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n taxID VARCHAR(13),\r\n address TEXT NOT NULL,\r\n CONSTRAINT chk_Buyer_TaxID_Format CHECK (\r\n taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'\r\n )\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u58f2\u308a\u624b\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u58f2\u308a\u624b\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Seller (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n taxID VARCHAR(13),\r\n address TEXT NOT NULL,\r\n CONSTRAINT chk_Seller_TaxID_Format CHECK (\r\n taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'\r\n )\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8acb\u6c42\u66f8\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8acb\u6c42\u66f8\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Invoice (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n issueDate DATE NOT NULL,\r\n typeCode VARCHAR(10) NOT NULL,\r\n dueDate DATE NOT NULL,\r\n buyerID VARCHAR(20) NOT NULL,\r\n sellerID VARCHAR(20) NOT NULL,\r\n sumOfLineNetAmount INT NOT NULL,\r\n totalAmountWithoutTax INT NOT NULL,\r\n totalTaxAmount INT NOT NULL,\r\n totalAmountWithTax INT NOT NULL,\r\n FOREIGN KEY (buyerID) REFERENCES Buyer(ID),\r\n FOREIGN KEY (sellerID) REFERENCES Seller(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u7a0e\u533a\u5206\u5185\u8a33\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u7a0e\u533a\u5206\u5185\u8a33\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE TaxBreakdown (\r\n invoiceID VARCHAR(20) NOT NULL,\r\n taxCategoryCode VARCHAR(10) NOT NULL,\r\n taxCategoryRate DECIMAL(5,2) NOT NULL,\r\n taxCategoryTaxableAmount INT NOT NULL,\r\n taxCategoryTaxAmount INT NOT NULL,\r\n FOREIGN KEY (invoiceID) REFERENCES Invoice(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8acb\u6c42\u66f8\u660e\u7d30\u884c\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8acb\u6c42\u66f8\u660e\u7d30\u884c\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE InvoiceLine (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n invoiceID VARCHAR(20) NOT NULL,\r\n netAmount INT NOT NULL,\r\n itemID VARCHAR(20) NOT NULL,\r\n quantity DECIMAL(10,3) NOT NULL,\r\n uom VARCHAR(10) NOT NULL,\r\n FOREIGN KEY (invoiceID) REFERENCES Invoice(ID),\r\n FOREIGN KEY (itemID) REFERENCES Item(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u5546\u54c1\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u5546\u54c1\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Item (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n price INT NOT NULL,\r\n baseQuantity DECIMAL(10,3) NOT NULL,\r\n uom VARCHAR(10) NOT NULL,\r\n taxCategoryCode VARCHAR(10) NOT NULL,\r\n taxCategoryRate DECIMAL(5,2) NOT NULL\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n\n2.2. \u521d\u671f\u30c7\u30fc\u30bf\u767b\u9332<\/h3>\n\n\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3092\u6b21\u306e\u3088\u3046\u306b\u767b\u9332\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u74b0\u5883\u6e96\u5099<\/div>\n\u5916\u6765\u30ad\u30fc\u53c2\u7167\u306a\u3069\u306e\u30c1\u30a7\u30c3\u30af\u3092\u7121\u52b9\u306b\u3057\u3001\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u30c7\u30fc\u30bf\u524a\u9664\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nDELETE FROM InvoiceLine;\r\nDELETE FROM TaxBreakdown;\r\nDELETE FROM Invoice;\r\nDELETE FROM Buyer;\r\nDELETE FROM Seller;\r\nDELETE FROM Item;<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8cb7\u3044\u624b\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Buyer (\r\n ID, name, taxID, address\r\n)\r\nVALUES\r\n ('B001', '\u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e', 'T012345678901', '\u6771\u4eac\u90fd\u6e2f\u533a');\r\n\r\nINSERT INTO Buyer (\r\n ID, name, address\r\n)\r\nVALUES\r\n ('B002', 'Greenwood High School', '456 School Rd, NY');<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"Buyer\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/Buyer.png\")
<\/span><\/p>\n<\/div>\n\n\u58f2\u308a\u624b\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Seller (\r\n ID, name, taxID, address\r\n)\r\nVALUES\r\n ('S001', '\u4f50\u85e4\u88fd\u4f5c\u6240', 'T098765432109', '\u5927\u962a\u5e02\u4e2d\u592e\u533a'),\r\n ('S002', 'Liberty Office Supply', 'T765432109876', '123 Main St, New York, NY');<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"SellerA\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/SellerA.png\")
<\/span><\/p>\n<\/div>\n\n\u5546\u54c1\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Item (\r\n ID, name, price, baseQuantity, uom, taxCategoryCode, taxCategoryRate\r\n)\r\nVALUES\r\n ('1001', '\u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2', 15000, 1, 'EA', 'S', 0.10),\r\n ('2001', '\u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc', 1200, 1, 'BOX', 'AA', 0.08),\r\n ('A001', 'Notebook', 500, 1, 'EA', 'S', 0.10),\r\n ('A002', 'Ballpoint Pen', 120, 1, 'EA', 'S', 0.10);<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"Item\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/Item.png\")
<\/span><\/p>\n<\/div>\n\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Invoice (\r\n ID, issueDate, typeCode, dueDate,\r\n buyerID, sellerID,\r\n sumOfLineNetAmount, totalAmountWithoutTax, totalTaxAmount, totalAmountWithTax\r\n)\r\nVALUES\r\n ('INV001', '2024-06-30', '380', '2024-07-15', 'B001', 'S001', 16200, 16200, 246, 18446),\r\n ('INV002', '2024-07-01', '380', '2024-07-15', 'B002', 'S002', 620, 620, 62, 682);<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"Invoice\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/Invoice.png\")
<\/span><\/p>\n<\/div>\n\n\u7a0e\u533a\u5206\u5185\u8a33\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO TaxBreakdown (\r\n invoiceID, taxCategoryCode, taxCategoryRate,\r\n taxCategoryTaxableAmount, taxCategoryTaxAmount\r\n)\r\nVALUES\r\n ('INV001', 'S', 0.10, 15000, 150),\r\n ('INV001', 'AA', 0.08, 1200, 96),\r\n ('INV002', 'S', 0.10, 620, 62);<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"TaxBreakdown\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/TaxBreakdown.png\")
<\/span><\/p>\n<\/div>\n\n\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO InvoiceLine (\r\n ID, invoiceID, netAmount, itemID, quantity, uom\r\n)\r\nVALUES\r\n ('L001', 'INV001', 15000, '1001', 1, 'EA'),\r\n ('L002', 'INV001', 1200, '2001', 1, 'BOX'),\r\n ('L003', 'INV002', 500, 'A001', 1, 'EA'),\r\n ('L004', 'INV002', 120, 'A002', 1, 'EA');<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"InvoiceLine\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/InvoiceLine.png\")
<\/span><\/p>\n<\/div>\n\n\u5916\u6765\u30ad\u30fc\u5236\u7d04\u5fa9\u5143<\/div>\n\nSET SQL_SAFE_UPDATES = 1;<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n3. \u8acb\u6c42\u66f8\u756a\u53f7\u3092\u6307\u5b9a\u3057\u305f\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u5f62\u5f0f\u306e\u691c\u7d22<\/h2>\n\n\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\u305d\u308c\u305e\u308c\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u6307\u5b9a\u3055\u308c\u305f\u8acb\u6c42\u66f8\u756a\u53f7\u306e\u884c\u3092\u53d6\u308a\u51fa\u3057\u3066\u7d50\u5408\u3057\u305f\u7d50\u679c\u304c\u8fd4\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\n-- 1. \u8acb\u6c42\u66f8\u30d8\u30c3\u30c0 + \u8cb7\u3044\u624b + \u58f2\u308a\u624b\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\nWHERE i.ID = 'INV001'\r\n-- 2. \u7a0e\u533a\u5206\u5185\u8a33\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n ROW_NUMBER() OVER () AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nWHERE tb.invoiceID = 'INV001'\r\n-- 3. \u660e\u7d30\u884c + \u5546\u54c1\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER () AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN Item it ON il.itemID = it.ID\r\nWHERE il.invoiceID = 'INV001';<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n4. \u69cb\u9020\u5316CSV\u306e\u751f\u6210<\/h2>\n\n\n\u3053\u3053\u3067\u306f\u3001Invoice\uff08\u8acb\u6c42\u66f8\uff09\u306b\u95a2\u3059\u308b\u30c7\u30fc\u30bf\u3092\u7d71\u5408\u3057\u305f\u69cb\u9020\u5316CSV\u3092\u751f\u6210\u3059\u308bSQL\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002CSV\u306f\u3001\u8acb\u6c42\u66f8\u3054\u3068\u306b\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\u30921\u3064\u306e\u8868\u306b\u307e\u3068\u3081\u305f\u5f62\u5f0f\u3067\u51fa\u529b\u3055\u308c\u3001dInvoice<\/code>, dTaxBreakdown<\/code>, `dInvoiceLine`\u306e\u5404\u5217\u306b\u3088\u3063\u3066\u968e\u5c64\u304c\u8868\u73fe\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n\n4.1. \u69cb\u9020\u5316CSV\u751f\u6210SQL<\/h3>\n\n\u4ee5\u4e0b\u306eSQL\u306f\u3001\u8acb\u6c42\u66f8\u3054\u3068\u306b\u9023\u756a\uff08dInvoice<\/code>\uff09\u3092\u632f\u308a\u3064\u3064\u3001\u5404\u30c7\u30fc\u30bf\uff08\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\uff09\u3092\u7d71\u4e00\u3055\u308c\u305f\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u3067UNION\u3057\u30661\u3064\u306e\u30d3\u30e5\u30fc\uff08\u307e\u305f\u306f\u30c6\u30fc\u30d6\u30eb\uff09\u3068\u3057\u3066\u51fa\u529b\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n\n\n-- \u65e2\u306b\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u305f\u3089\u524a\u9664\r\nDROP TABLE IF EXISTS InvoiceStructuredCSV;\r\n\r\nCREATE table InvoiceStructuredCSV AS\r\n-- (1) WITH\u53e5\u3067\u8acb\u6c42\u66f8\u3054\u3068\u306b\u305d\u306e\u9806\u5e8f\u756a\u53f7\u3092dInvoice\u306b\u5272\u308a\u5f53\u3066\u308b\r\nWITH InvoiceIndex AS (\r\n SELECT\r\n ID AS invoiceID,\r\n ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice\r\n FROM Invoice\r\n)\r\n-- (2) \u30d8\u30c3\u30c0\u884c\u3092\u62bd\u51fa\uff08dInvoice \u306e\u307f\u8a2d\u5b9a\uff09\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN InvoiceIndex ii ON i.ID = ii.invoiceID\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\n-- (3) \u7a0e\u5185\u8a33\u3092\u62bd\u51fa\uff08dTaxBreakdown \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nJOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID\r\n-- (4) \u660e\u7d30\uff0b\u5546\u54c1\u60c5\u5831\u3092\u62bd\u51fa\uff08dInvoiceLine \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID\r\nJOIN Item it ON il.itemID = it.ID\r\n-- (5) \u30bd\u30fc\u30c8\uff1a\u30d8\u30c3\u30c0\u2192\u7a0e\u5185\u8a33\u2192\u660e\u7d30\r\nORDER BY\r\n dInvoice,\r\n CASE\r\n WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0\r\n WHEN dTaxBreakdown IS NOT NULL THEN 1\r\n WHEN dInvoiceLine IS NOT NULL THEN 2\r\n ELSE 3\r\n END,\r\n dTaxBreakdown,\r\n dInvoiceLine;<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n\n4.2. \u89e3\u8aac<\/h3>\n\n(1) WITH InvoiceIndex AS (…)<\/code>
\n\u8acb\u6c42\u66f8ID\u9806\u306b\u9023\u756a\uff08dInvoice<\/code>\uff09\u3092\u4ed8\u4e0e\u3057\u3001\u3059\u3079\u3066\u306eUNION\u5bfe\u8c61\u306b\u5171\u901a\u306e\u30ad\u30fc\u3068\u3057\u3066\u5229\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n\n(2) \u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\uff08\u57fa\u672c\u60c5\u5831\uff09\u3092\u58f2\u308a\u624b\u3001\u8cb7\u3044\u624b\u306e\u60c5\u5831\u3068\u5171\u306b1\u884c\u306b\u307e\u3068\u3081\u3066\u51fa\u529b\u3057\u307e\u3059\u3002`dInvoice`\u4ee5\u5916\u306e\u968e\u5c64\u30ad\u30fc\u306fNULL\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<\/div>\n
\n(3) \u8acb\u6c42\u66f8\u3054\u3068\u306e\u7a0e\u533a\u5206\u5185\u8a33\u3092`TaxBreakdown`\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u53d6\u5f97\u3057\u3001`dTaxBreakdown`\u306b\u9023\u756a\u3092\u632f\u3063\u3066\u51fa\u529b\u3057\u307e\u3059\u3002\u30d8\u30c3\u30c0\u60c5\u5831\u306f\u542b\u307f\u307e\u305b\u3093\u3002<\/p>\n<\/div>\n
\n(4) \u8acb\u6c42\u66f8\u660e\u7d30\u884c`InvoiceLine`\u3068\u5546\u54c1\u60c5\u5831`Item`\u3092\u7d50\u5408\u3057\u3001`dInvoiceLine`\u306b\u9023\u756a\u3092\u632f\u3063\u3066\u51fa\u529b\u3057\u307e\u3059\u3002\u3053\u3061\u3089\u3082\u30d8\u30c3\u30c0\u60c5\u5831\u306f\u542b\u307f\u307e\u305b\u3093\u3002<\/p>\n<\/div>\n
\n(5) \u6700\u5f8c\u306b ORDER BY<\/code> \u306b\u3088\u3063\u3066\u3001`dInvoice`\u3054\u3068\u306b\u30d8\u30c3\u30c0\u2192\u7a0e\u533a\u5206\u5185\u8a33\u2192\u660e\u7d30\u884c\u306e\u9806\u306b\u4e26\u3079\u66ff\u3048\u3092\u884c\u3044\u307e\u3059\u3002\u3053\u308c\u306b\u3088\u308a\u3001\u4eba\u9593\u306b\u3082\u6a5f\u68b0\u306b\u3082\u51e6\u7406\u3057\u3084\u3059\u3044\u69cb\u9020\u5316CSV\u304c\u5f97\u3089\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n\n
\n![\"InvoiceStructuredCSV2\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/06\/InvoiceStructuredCSV2.png\")
\n<\/span><\/p>\n<\/div>\n\n\u3053\u306e\u69cb\u9020\u5316CSV\u306f\u3001\u884c\u30ec\u30d9\u30eb\u3067\u610f\u5473\u304c\u7570\u306a\u308b\u30c7\u30fc\u30bf\uff08\u30d8\u30c3\u30c0\u30fb\u7a0e\u533a\u5206\u5185\u8a33\u30fb\u660e\u7d30\u884c\uff09\u30921\u3064\u306eCSV\u30d5\u30a1\u30a4\u30eb\u306b\u307e\u3068\u3081\u3066\u3044\u307e\u3059\u3002
\n\u5f8c\u7d9a\u51e6\u7406\u3067\u306f\u3001dInvoice<\/code>, dTaxBreakdown<\/code>, dInvoiceLine<\/code> \u306e\u3044\u305a\u308c\u304c\u975eNULL\u304b\u3067\u884c\u306e\u7a2e\u985e\u3092\u8b58\u5225\u3067\u304d\u307e\u3059\u3002<\/p>\n<\/div>\n\n\n \n\n\n\u5bfe\u8c61<\/p>\n<\/td>\n
\ndInvoice
\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0<\/p>\n<\/td>\n
\ndTaxBreakdown
\n\u7a0e\u533a\u5206\u5185\u8a33<\/p>\n<\/td>\n
\ndInvoiceLine
\n\u660e\u7d30\u884c<\/p>\n<\/td>\n<\/tr>\n
\n\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0<\/p>\n<\/td>\n
\n\u6570\u5024<\/strong><\/p>\n<\/td>\n\n\u7a7a\u6b04<\/p>\n<\/td>\n
\n\u7a7a\u6b04<\/p>\n<\/td>\n<\/tr>\n
\n\n\u7a0e\u533a\u5206\u5185\u8a33<\/p>\n<\/td>\n
\n\u6570\u5024<\/strong><\/p>\n<\/td>\n\n\u6570\u5024<\/strong><\/p>\n<\/td>\n\n\u7a7a\u6b04<\/p>\n<\/td>\n<\/tr>\n
\n\n\u660e\u7d30\u884c<\/p>\n<\/td>\n
\n\u6570\u5024<\/strong><\/p>\n<\/td>\n\n\u7a7a\u6b04<\/p>\n<\/td>\n
\n\u6570\u5024<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n\n5. \u62bd\u51fa\u30af\u30a8\u30ea<\/h2>\n\n\n\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u60c5\u5831\u3084\u7a0e\u533a\u5206\u5185\u8a33\u60c5\u5831\u306a\u3069\u3092\u53d6\u308a\u51fa\u3059\u306b\u306f\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u3092\u6761\u4ef6\u306b\u3001\u5bfe\u5fdc\u3059\u308b\u30c7\u30a3\u30e1\u30f3\u30b8\u30e7\u30f3\u304c\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u308b\u884c\u3092\u9078\u629e\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u306e\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT DISTINCT\r\n `Invoice.ID` AS ID,\r\n issueDate,\r\n typeCode,\r\n dueDate,\r\n sumOfLineNetAmount,\r\n totalAmountWithoutTax,\r\n totalTaxAmount,\r\n totalAmountWithTax,\r\n `Buyer.name`,\r\n `Seller.name`,\r\n `Seller.taxID`\r\nFROM InvoiceStructuredCSV\r\nWHERE `Invoice.ID` = 'INV001';<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"InvoiceFromStructuredCSV\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/InvoiceFromStructuredCSV.png\")
<\/span><\/p>\n<\/div>\n\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u7a0e\u533a\u5206\u5185\u8a33\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT\r\n d.dInvoice AS invoiceRef,\r\n d.`TaxBreakdown.taxCategoryCode`,\r\n d.`TaxBreakdown.taxCategoryRate`,\r\n d.`TaxBreakdown.taxCategoryTaxAmount`\r\nFROM InvoiceStructuredCSV d\r\nWHERE d.dInvoice = (\r\n SELECT dInvoice\r\n FROM InvoiceStructuredCSV\r\n WHERE `Invoice.ID` = 'INV001'\r\n)\r\nAND d.dTaxBreakdown IS NOT NULL;<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"TaxBreakdownFromStructuredCSV\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/TaxBreakdownFromStructuredCSV.png\")
<\/span><\/p>\n<\/div>\n\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u691c\u7d22<\/div>\n\nSELECT\r\n d.dInvoice AS invoiceRef,\r\n d.`InvoiceLine.ID`,\r\n d.`InvoiceLine.netAmount`,\r\n d.`InvoiceLine.quantity`,\r\n d.`InvoiceLine.uom`,\r\n d.`Item.ID`,\r\n d.`Item.name`,\r\n d.`Item.price`,\r\n d.`Item.baseQuantity`,\r\n d.`Item.uom`,\r\n d.`Item.taxCategoryCode`,\r\n d.`Item.taxCategoryRate`\r\nFROM InvoiceStructuredCSV d\r\nWHERE d.dInvoice = (\r\n SELECT dInvoice\r\n FROM InvoiceStructuredCSV\r\n WHERE `Invoice.ID` = 'INV001'\r\n AND dInvoice IS NOT NULL\r\n)\r\nAND d.dInvoiceLine IS NOT NULL;<\/code><\/pre>\n<\/div>\n<\/div>\n\n![\"InvoiceLineFromStructuredCSV\"](\"https:\/\/www.sambuichi.jp\/wp-content\/uploads\/2025\/05\/InvoiceLineFromStructuredCSV.png\")
<\/span><\/p>\n<\/div>\n<\/div>\n<\/div>\n\n6. \u307e\u3068\u3081<\/h2>\n\n\n\u69cb\u9020\u5316CSV\u306f\u3001\u968e\u5c64\u69cb\u9020\u3092\u30d5\u30e9\u30c3\u30c8\u306b1\u3064\u306eCSV\u306b\u7d71\u5408\u3057\u306a\u304c\u3089\u3001\u5404\u968e\u5c64\u3092dInvoice, dTaxBreakdown, dInvoiceLine\u306a\u3069\u306e\u8b58\u5225\u5b50\u3067\u793a\u3059\u3053\u3068\u306b\u3088\u308a\u3001\u52b9\u7387\u7684\u306a\u96c6\u8a08\u30fb\u62bd\u51fa\u3092\u53ef\u80fd\u306b\u3057\u307e\u3059\u3002\u65e5\u672c\u306e\u9069\u683c\u8acb\u6c42\u66f8\u5236\u5ea6\u306b\u5bfe\u5fdc\u3057\u305f\u8a2d\u8a08\u3082\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<\/div>\n
\nNULL\u306b\u5bfe\u3057\u3066\u3001\u6587\u5b57’NULL’\u3092\u51fa\u529b\u305b\u305a\u3001\u7a7a\u6b04\u3068\u3059\u308b\u305f\u3081\u306b\u6b21\u306e\u3088\u3046\u306bIFNULL\u95a2\u6570\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT\r\n IFNULL(dInvoice, '') AS dInvoice,\r\n IFNULL(dTaxBreakdown, '') AS dTaxBreakdown,\r\n IFNULL(dInvoiceLine, '') AS dInvoiceLine,\r\n IFNULL(`Invoice.ID`, '') AS `Invoice.ID`,\r\n IFNULL(issueDate, '') AS issueDate,\r\n IFNULL(typeCode, '') AS typeCode,\r\n IFNULL(dueDate, '') AS dueDate,\r\n IFNULL(`Buyer.name`, '') AS `Buyer.name`,\r\n IFNULL(`Seller.name`, '') AS `Seller.name`,\r\n IFNULL(`Seller.taxID`, '') AS `Seller.taxID`,\r\n IFNULL(sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(totalAmountWithTax, '') AS totalAmountWithTax,\r\n IFNULL(`TaxBreakdown.taxCategoryCode`, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(`TaxBreakdown.taxCategoryRate`, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(`TaxBreakdown.taxCategoryTaxAmount`, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n IFNULL(`InvoiceLine.ID`, '') AS `InvoiceLine.ID`,\r\n IFNULL(`InvoiceLine.netAmount`, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(`InvoiceLine.quantity`, '') AS `InvoiceLine.quantity`,\r\n IFNULL(`InvoiceLine.uom`, '') AS `InvoiceLine.uom`,\r\n IFNULL(`Item.ID`, '') AS `Item.ID`,\r\n IFNULL(`Item.name`, '') AS `Item.name`,\r\n IFNULL(`Item.price`, '') AS `Item.price`,\r\n IFNULL(`Item.baseQuantity`, '') AS `Item.baseQuantity`,\r\n IFNULL(`Item.uom`, '') AS `Item.uom`,\r\n IFNULL(`Item.taxCategoryCode`, '') AS `Item.taxCategoryCode`,\r\n IFNULL(`Item.taxCategoryRate`, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceStructuredCSV;<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u69cb\u9020\u5316CSV<\/div>\n\ndInvoice,dTaxBreakdown,dInvoiceLine,Invoice.ID,issueDate,typeCode,dueDate,Buyer.name,Seller.name,Seller.taxID,sumOfLineNetAmount,totalAmountWithoutTax,totalTaxAmount,totalAmountWithTax,TaxBreakdown.taxCategoryCode,TaxBreakdown.taxCategoryRate,TaxBreakdown.taxCategoryTaxAmount,InvoiceLine.ID,InvoiceLine.netAmount,InvoiceLine.quantity,InvoiceLine.uom,Item.ID,Item.name,Item.price,Item.baseQuantity,Item.uom,Item.taxCategoryCode,Item.taxCategoryRate\r\n1,,,INV001,2024-06-30,380,2024-07-15,\u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e,\u4f50\u85e4\u88fd\u4f5c\u6240,T098765432109,16200.0,16200.0,246.0,18666.0,,,,,,,,,,,,,,\r\n1,1,,,,,,,,,,,,,AA,0.08,96.0,,,,,,,,,,,\r\n1,2,,,,,,,,,,,,,S,0.1,150.0,,,,,,,,,,,\r\n1,,1,,,,,,,,,,,,,,,L001,15000.0,1.0,EA,1001,\u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2,15000.0,1.0,EA,S,0.1\r\n1,,2,,,,,,,,,,,,,,,L002,1200.0,1.0,BOX,2001,\u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc,1200.0,1.0,BOX,AA,0.08\r\n2,,,INV002,2024-07-01,380,2024-07-15,Greenwood High School,Liberty Office Supply,T765432109876,620.0,620.0,62.0,682.0,,,,,,,,,,,,,,\r\n2,1,,,,,,,,,,,,,S,0.1,62.0,,,,,,,,,,,\r\n2,,1,,,,,,,,,,,,,,,L003,500.0,1.0,EA,A001,Notebook,500.0,1.0,EA,S,0.1\r\n2,,2,,,,,,,,,,,,,,,L004,120.0,1.0,EA,A002,Ballpoint Pen,120.0,1.0,EA,S,0.1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n \n\ndInvoice<\/th>\n dTaxBreakdown<\/th>\n dInvoiceLine<\/th>\n Invoice.ID<\/th>\n issueDate<\/th>\n typeCode<\/th>\n dueDate<\/th>\n Buyer.name<\/th>\n Seller.name<\/th>\n Seller.taxID<\/th>\n sumOfLineNetAmount<\/th>\n totalAmountWithoutTax<\/th>\n totalTaxAmount<\/th>\n totalAmountWithTax<\/th>\n TaxBreakdown.taxCategoryCode<\/th>\n TaxBreakdown.taxCategoryRate<\/th>\n TaxBreakdown.taxCategoryTaxAmount<\/th>\n InvoiceLine.ID<\/th>\n InvoiceLine.netAmount<\/th>\n InvoiceLine.quantity<\/th>\n InvoiceLine.uom<\/th>\n Item.ID<\/th>\n Item.name<\/th>\n Item.price<\/th>\n Item.baseQuantity<\/th>\n Item.uom<\/th>\n Item.taxCategoryCode<\/th>\n Item.taxCategoryRate<\/th>\n<\/tr>\n<\/thead>\n \n\n\n1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n \nINV001<\/p>\n<\/td>\n
\n2024-06-30<\/p>\n<\/td>\n
\n380<\/p>\n<\/td>\n
\n2024-07-15<\/p>\n<\/td>\n
\n\u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e<\/p>\n<\/td>\n
\n\u4f50\u85e4\u88fd\u4f5c\u6240<\/p>\n<\/td>\n
\nT098765432109<\/p>\n<\/td>\n
\n16200<\/p>\n<\/td>\n
\n16200<\/p>\n<\/td>\n
\n246<\/p>\n<\/td>\n
\n18446<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n\n1<\/p>\n<\/td>\n
\n1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \nS<\/p>\n<\/td>\n
\n0.10<\/p>\n<\/td>\n
\n150<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n\n1<\/p>\n<\/td>\n
\n2<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \nAA<\/p>\n<\/td>\n
\n0.08<\/p>\n<\/td>\n
\n96<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n\n1<\/p>\n<\/td>\n
<\/td>\n \n1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \nL001<\/p>\n<\/td>\n
\n15000<\/p>\n<\/td>\n
\n1.000<\/p>\n<\/td>\n
\nEA<\/p>\n<\/td>\n
\n1001<\/p>\n<\/td>\n
\n\u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2<\/p>\n<\/td>\n
\n15000<\/p>\n<\/td>\n
\n1.000<\/p>\n<\/td>\n
\nEA<\/p>\n<\/td>\n
\nS<\/p>\n<\/td>\n
\n0.10<\/p>\n<\/td>\n<\/tr>\n
\n\n1<\/p>\n<\/td>\n
<\/td>\n \n2<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \nL002<\/p>\n<\/td>\n
\n1200<\/p>\n<\/td>\n
\n1.000<\/p>\n<\/td>\n
\nBOX<\/p>\n<\/td>\n
\n2001<\/p>\n<\/td>\n
\n\u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc<\/p>\n<\/td>\n
\n1200<\/p>\n<\/td>\n
\n1.000<\/p>\n<\/td>\n
\nBOX<\/p>\n<\/td>\n
\nAA<\/p>\n<\/td>\n
\n0.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n\u672c\u8a18\u4e8b\u3067\u4f7f\u7528\u3057\u3066\u3044\u308b\u30c6\u30fc\u30d6\u30eb\u306eE-R\u56f3\u3092\u4ee5\u4e0b\u306b\u793a\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\n![\"invoice-er-diagram\"](\"https:\/\/kroki.io\/plantuml\/svg\/eNq9VF1PgzAUfedX9HFbRsJmfGmWJQxmJM6pG1FfO7lqI7QTWh0x_ndbug3YtzHxhZB7TznnnnMLMEFFjgYyhxR9WQi1UOAjjO7diXfpThpdp4l6vdurfl_1bFs9GEmgAug4TlNVBVnUznXOdJVEUQpZpurh8DG0vi0LDN8U4vhfCQP2wekTnMZIs0yCT4Sm9d1wqOnyOXg8qgvRjNEmcqat3EFwYQiyYvIDfZncPI8ogzEIN-GSCYUMxqHWwAWJTe2BilcuRUgW9a4qHD5Unii9UbVBCuQt4p9saRA1du2X2dIBeGrwF57mO40pnKyAJkuXhl5w7Y4a5-1usw5QMsgshk39NUCtuZWutu3EhI_Nx7bcpwKS_fh3SYyUcsKO0y52UvJkw5yKcPXRv9yCeaqXeiVxRjK4-40QdDTFIwGqSZbeY7xafNvum_8JxoFftteLr_vm_heA6vZhXAajYevD5Yd0xqfDTGYFRr0WgB-xG2W8\")
\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n7. \u69cb\u9020\u5316CSV\u30d5\u30a1\u30a4\u30eb\u51fa\u529bPython\u30b9\u30af\u30ea\u30d7\u30c8<\/h2>\n\n\nPython\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u30d5\u30a1\u30a4\u30eb\u51fa\u529b\u3059\u308b\u306b\u306f\u3001pymysql\u3068pandas\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u4f7f\u7528\u3059\u308b\u6b21\u306e\u3088\u3046\u306a\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u51e6\u7406\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nimport pymysql\r\nimport pandas as pd\r\n\r\n# --- Configuration ---\r\ndb_config = {\r\n \"host\": \"localhost\",\r\n \"user\": \"root\",\r\n \"password\": \"user password\",\r\n \"database\": \"structured csv database\",\r\n \"charset\": \"utf8mb4\"\r\n}\r\ndb_config = {\r\n \"host\": \"localhost\",\r\n \"user\": \"root\",\r\n \"password\": \"$V1-mysql-N0\",\r\n \"database\": \"structured_csv\",\r\n \"charset\": \"utf8mb4\"\r\n}\r\noutput_csv_path0 = \"structured_invoice_export0.csv\"\r\noutput_csv_path1 = \"structured_invoice_export1.csv\"\r\ninvoice_id = \"{invoice_id}\"\r\n\r\n# --- SQL Query ---\r\nsql_query0 = f\"\"\"\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\nWHERE i.ID = '{invoice_id}'\r\n-- 2. Tax Breakdown Rows\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n ROW_NUMBER() OVER () AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nWHERE tb.invoiceID = '{invoice_id}'\r\n-- 3. Invoice Line + Item Rows\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER () AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN Item it ON il.itemID = it.ID\r\nWHERE il.invoiceID = '{invoice_id}';\r\n\"\"\"\r\n\r\nsql_query1 = f\"\"\"\r\n-- (1) \u8acb\u6c42\u66f8\u3054\u3068\u306b\u305d\u306e\u9806\u5e8f\u756a\u53f7\u3092\u5b9a\u7fa9\r\nWITH InvoiceIndex AS (\r\n SELECT\r\n ID AS invoiceID,\r\n ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice\r\n FROM Invoice\r\n)\r\n-- (2) \u30d8\u30c3\u30c0\u884c\u3092\u62bd\u51fa\uff08dInvoice \u306e\u307f\u8a2d\u5b9a\uff09\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN InvoiceIndex ii ON i.ID = ii.invoiceID\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\n-- (3) \u7a0e\u5185\u8a33\u3092\u62bd\u51fa\uff08dTaxBreakdown \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nJOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID\r\n-- (4) \u660e\u7d30\uff0b\u5546\u54c1\u60c5\u5831\u3092\u62bd\u51fa\uff08dInvoiceLine \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID\r\nJOIN Item it ON il.itemID = it.ID\r\n-- (5) \u30bd\u30fc\u30c8\uff1a\u30d8\u30c3\u30c0\u2192\u7a0e\u5185\u8a33\u2192\u660e\u7d30\r\nORDER BY\r\n dInvoice,\r\n CASE\r\n WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0\r\n WHEN dTaxBreakdown IS NOT NULL THEN 1\r\n WHEN dInvoiceLine IS NOT NULL THEN 2\r\n ELSE 3\r\n END,\r\n dTaxBreakdown,\r\n dInvoiceLine;\r\n\"\"\"\r\n# --- Execution ---\r\nconnection = pymysql.connect(**db_config)\r\n\r\ndf0 = pd.read_sql(sql_query0, connection)\r\n# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---\r\ndf0[\"dInvoice\"] = df0[\"dInvoice\"].astype(\"Int64\")\r\ndf0[\"dTaxBreakdown\"] = df0[\"dTaxBreakdown\"].astype(\"Int64\")\r\ndf0[\"dInvoiceLine\"] = df0[\"dInvoiceLine\"].astype(\"Int64\")\r\ndf0.to_csv(output_csv_path0, index=False, encoding=\"utf-8-sig\")\r\n\r\ndf1 = pd.read_sql(sql_query1, connection)\r\n# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---\r\ndf1[\"dInvoice\"] = df1[\"dInvoice\"].astype(\"Int64\")\r\ndf1[\"dTaxBreakdown\"] = df1[\"dTaxBreakdown\"].astype(\"Int64\")\r\ndf1[\"dInvoiceLine\"] = df1[\"dInvoiceLine\"].astype(\"Int64\")\r\ndf1.to_csv(output_csv_path1, index=False, encoding=\"utf-8-sig\")\r\n\r\nconnection.close()\r\n\r\nprint(f\"\u2705 Exported invoice to {output_csv_path0} and {output_csv_path1}.\")<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n2025-05-31<\/span>\n<\/div>\n<\/div>\n
\n\u3053\u306e\u30d6\u30ed\u30b0\u8a18\u4e8b\u3067\u306f\u3001\u30a4\u30f3\u30dc\u30a4\u30b9\u30c7\u30fc\u30bf\u3092\u69cb\u9020\u5316CSV\u3068\u3057\u3066\u5b9a\u7fa9\u3057\u3001MySQL\u4e0a\u306b\u518d\u73fe\u30fb\u6d3b\u7528\u3059\u308b\u305f\u3081\u306eSQL\u5b9a\u7fa9\u3068\u305d\u306e\u4f7f\u3044\u65b9\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002\u691c\u8a3c\u74b0\u5883\u306f\u3001Windows 11\u306eMySQL Server 8.0.71\u3067\u3059\u3002\n<\/p><\/blockquote>\n<\/div>\n<\/div>\n<\/div>\n
\n1. \u69cb\u9020\u5316CSV\u306e\u7279\u5fb4\u3068\u6d3b\u7528\u65b9\u6cd5<\/h2>\n
\n\n\u5f93\u6765\u306e\u30a2\u30d7\u30ea\u958b\u767a\u3067\u306f\u3001\u5b9a\u7fa9\u3057\u3066\u3044\u308b\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c6\u30fc\u30d6\u30eb\u3092\u5358\u7d14\u306a\u30af\u30a8\u30ea\u30fc\u3067\u30c0\u30f3\u30d7\u3057\u305fCSV\u304c\u30a4\u30f3\u30bf\u30fc\u30d5\u30a7\u30a4\u30b9\u3068\u3057\u3066\u4f7f\u7528\u3055\u308c\u3066\u3044\u305f\u305f\u3081\u3001\u91cd\u8907\u30c7\u30fc\u30bf\u3092\u542b\u3080\u30c6\u30fc\u30d6\u30eb\u304b\u91cd\u8907\u3092\u56de\u907f\u3057\u305f\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u51fa\u529b\u3057\u3066\u3044\u307e\u3057\u305f\u3002<\/p>\n<\/div>\n
\n\u69cb\u9020\u5316CSV\u3067\u306f\u3001\u91cd\u8907\u306e\u306a\u3044\u4e00\u3064\u306e\u30d5\u30a1\u30a4\u30eb\u3067\u968e\u5c64\u69cb\u9020\u306e\u3042\u308b\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u307e\u305f\u3001\u51fa\u529b\u3055\u308c\u305f\u69cb\u9020\u5316CSV\u3092\u8aad\u307f\u8fbc\u3093\u3060\u30c6\u30fc\u30d6\u30eb\u3092\u5bfe\u8c61\u306b\u968e\u5c64\u69cb\u9020\uff08\u30c7\u30a3\u30e1\u30f3\u30b7\u30e7\u30f3\uff09\u60c5\u5831\u3092\u9078\u629e\u3059\u308b\u3053\u3068\u3067\u5bb9\u6613\u306b\u30c7\u30fc\u30bf\u3092\u30a2\u30d7\u30ea\u5bfe\u5fdc\u306b\u5fa9\u5143\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<\/div>\n
\n\u3053\u306e\u8a18\u4e8b\u3067\u306f\u3001<\/p>\n<\/div>\n
\n\n
- \n
\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3069\u306e\u3088\u3046\u306a\u6761\u4ef6\u3067\u69cb\u9020\u5316CSV\u3092\u51fa\u529b\u3059\u308b\u305f\u3081\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u5b9a\u7fa9\u3059\u308b\u304b<\/p>\n<\/li>\n
- \n
\u69cb\u9020\u5316CSV\u306b\u5bfe\u5fdc\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3069\u306e\u3088\u3046\u306a\u6761\u4ef6\u3092\u6307\u5b9a\u3057\u3066\u5143\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u76f8\u5f53\u3059\u308b\u60c5\u5831\u3092\u62bd\u51fa\u3059\u308b\u304b<\/p>\n<\/li>\n<\/ul>\n<\/div>\n
\n\u3092SQL\u6587\u3092\u4f7f\u7528\u3057\u3066\u89e3\u8aac\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n2. \u30c6\u30b9\u30c8\u74b0\u5883<\/h2>\n
\n\n2.1. \u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/h3>\n
\n\u4ee5\u4e0b\u306b\u3001Buyer\uff08\u8cb7\u3044\u624b\uff09\u3001Seller\uff08\u58f2\u308a\u624b\uff09\u3001Invoice\uff08\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\uff09\u3001TaxBreakdown\uff08\u7a0e\u533a\u5206\u5185\u8a33\uff09\u3001InvoiceLine\uff08\u660e\u7d30\u884c\uff09\u3001Item\uff08\u5546\u54c1\uff09\u306e6\u3064\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u5b9a\u7fa9\u3057\u307e\u3059\u3002\u65e5\u672c\u306e\u9069\u683c\u8acb\u6c42\u66f8\u767a\u884c\u4e8b\u696d\u8005\u756a\u53f7\uff08”T”\uff0b12\u6841\uff09\u306b\u5bfe\u5fdc\u3059\u308b\u5236\u7d04\u3082\u8ffd\u52a0\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u8cb7\u3044\u624b\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8cb7\u3044\u624b\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Buyer (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n taxID VARCHAR(13),\r\n address TEXT NOT NULL,\r\n CONSTRAINT chk_Buyer_TaxID_Format CHECK (\r\n taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'\r\n )\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u58f2\u308a\u624b\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u58f2\u308a\u624b\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Seller (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n taxID VARCHAR(13),\r\n address TEXT NOT NULL,\r\n CONSTRAINT chk_Seller_TaxID_Format CHECK (\r\n taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'\r\n )\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8acb\u6c42\u66f8\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8acb\u6c42\u66f8\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Invoice (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n issueDate DATE NOT NULL,\r\n typeCode VARCHAR(10) NOT NULL,\r\n dueDate DATE NOT NULL,\r\n buyerID VARCHAR(20) NOT NULL,\r\n sellerID VARCHAR(20) NOT NULL,\r\n sumOfLineNetAmount INT NOT NULL,\r\n totalAmountWithoutTax INT NOT NULL,\r\n totalTaxAmount INT NOT NULL,\r\n totalAmountWithTax INT NOT NULL,\r\n FOREIGN KEY (buyerID) REFERENCES Buyer(ID),\r\n FOREIGN KEY (sellerID) REFERENCES Seller(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u7a0e\u533a\u5206\u5185\u8a33\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u7a0e\u533a\u5206\u5185\u8a33\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE TaxBreakdown (\r\n invoiceID VARCHAR(20) NOT NULL,\r\n taxCategoryCode VARCHAR(10) NOT NULL,\r\n taxCategoryRate DECIMAL(5,2) NOT NULL,\r\n taxCategoryTaxableAmount INT NOT NULL,\r\n taxCategoryTaxAmount INT NOT NULL,\r\n FOREIGN KEY (invoiceID) REFERENCES Invoice(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8acb\u6c42\u66f8\u660e\u7d30\u884c\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u8acb\u6c42\u66f8\u660e\u7d30\u884c\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE InvoiceLine (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n invoiceID VARCHAR(20) NOT NULL,\r\n netAmount INT NOT NULL,\r\n itemID VARCHAR(20) NOT NULL,\r\n quantity DECIMAL(10,3) NOT NULL,\r\n uom VARCHAR(10) NOT NULL,\r\n FOREIGN KEY (invoiceID) REFERENCES Invoice(ID),\r\n FOREIGN KEY (itemID) REFERENCES Item(ID)\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u5546\u54c1\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/div>\n\n-- \u5546\u54c1\u30c6\u30fc\u30d6\u30eb\r\nCREATE TABLE Item (\r\n ID VARCHAR(20) PRIMARY KEY,\r\n name VARCHAR(100) NOT NULL,\r\n price INT NOT NULL,\r\n baseQuantity DECIMAL(10,3) NOT NULL,\r\n uom VARCHAR(10) NOT NULL,\r\n taxCategoryCode VARCHAR(10) NOT NULL,\r\n taxCategoryRate DECIMAL(5,2) NOT NULL\r\n);<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n\n2.2. \u521d\u671f\u30c7\u30fc\u30bf\u767b\u9332<\/h3>\n
\n\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3092\u6b21\u306e\u3088\u3046\u306b\u767b\u9332\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u74b0\u5883\u6e96\u5099<\/div>\n\u5916\u6765\u30ad\u30fc\u53c2\u7167\u306a\u3069\u306e\u30c1\u30a7\u30c3\u30af\u3092\u7121\u52b9\u306b\u3057\u3001\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u30c7\u30fc\u30bf\u524a\u9664\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nDELETE FROM InvoiceLine;\r\nDELETE FROM TaxBreakdown;\r\nDELETE FROM Invoice;\r\nDELETE FROM Buyer;\r\nDELETE FROM Seller;\r\nDELETE FROM Item;<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u8cb7\u3044\u624b\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Buyer (\r\n ID, name, taxID, address\r\n)\r\nVALUES\r\n ('B001', '\u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e', 'T012345678901', '\u6771\u4eac\u90fd\u6e2f\u533a');\r\n\r\nINSERT INTO Buyer (\r\n ID, name, address\r\n)\r\nVALUES\r\n ('B002', 'Greenwood High School', '456 School Rd, NY');<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u58f2\u308a\u624b\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Seller (\r\n ID, name, taxID, address\r\n)\r\nVALUES\r\n ('S001', '\u4f50\u85e4\u88fd\u4f5c\u6240', 'T098765432109', '\u5927\u962a\u5e02\u4e2d\u592e\u533a'),\r\n ('S002', 'Liberty Office Supply', 'T765432109876', '123 Main St, New York, NY');<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u5546\u54c1\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Item (\r\n ID, name, price, baseQuantity, uom, taxCategoryCode, taxCategoryRate\r\n)\r\nVALUES\r\n ('1001', '\u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2', 15000, 1, 'EA', 'S', 0.10),\r\n ('2001', '\u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc', 1200, 1, 'BOX', 'AA', 0.08),\r\n ('A001', 'Notebook', 500, 1, 'EA', 'S', 0.10),\r\n ('A002', 'Ballpoint Pen', 120, 1, 'EA', 'S', 0.10);<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO Invoice (\r\n ID, issueDate, typeCode, dueDate,\r\n buyerID, sellerID,\r\n sumOfLineNetAmount, totalAmountWithoutTax, totalTaxAmount, totalAmountWithTax\r\n)\r\nVALUES\r\n ('INV001', '2024-06-30', '380', '2024-07-15', 'B001', 'S001', 16200, 16200, 246, 18446),\r\n ('INV002', '2024-07-01', '380', '2024-07-15', 'B002', 'S002', 620, 620, 62, 682);<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u7a0e\u533a\u5206\u5185\u8a33\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO TaxBreakdown (\r\n invoiceID, taxCategoryCode, taxCategoryRate,\r\n taxCategoryTaxableAmount, taxCategoryTaxAmount\r\n)\r\nVALUES\r\n ('INV001', 'S', 0.10, 15000, 150),\r\n ('INV001', 'AA', 0.08, 1200, 96),\r\n ('INV002', 'S', 0.10, 620, 62);<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u767b\u9332<\/div>\n\nINSERT INTO InvoiceLine (\r\n ID, invoiceID, netAmount, itemID, quantity, uom\r\n)\r\nVALUES\r\n ('L001', 'INV001', 15000, '1001', 1, 'EA'),\r\n ('L002', 'INV001', 1200, '2001', 1, 'BOX'),\r\n ('L003', 'INV002', 500, 'A001', 1, 'EA'),\r\n ('L004', 'INV002', 120, 'A002', 1, 'EA');<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u5916\u6765\u30ad\u30fc\u5236\u7d04\u5fa9\u5143<\/div>\n\nSET SQL_SAFE_UPDATES = 1;<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n3. \u8acb\u6c42\u66f8\u756a\u53f7\u3092\u6307\u5b9a\u3057\u305f\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u5f62\u5f0f\u306e\u691c\u7d22<\/h2>\n
\n\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\u305d\u308c\u305e\u308c\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u6307\u5b9a\u3055\u308c\u305f\u8acb\u6c42\u66f8\u756a\u53f7\u306e\u884c\u3092\u53d6\u308a\u51fa\u3057\u3066\u7d50\u5408\u3057\u305f\u7d50\u679c\u304c\u8fd4\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\n-- 1. \u8acb\u6c42\u66f8\u30d8\u30c3\u30c0 + \u8cb7\u3044\u624b + \u58f2\u308a\u624b\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\nWHERE i.ID = 'INV001'\r\n-- 2. \u7a0e\u533a\u5206\u5185\u8a33\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n ROW_NUMBER() OVER () AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nWHERE tb.invoiceID = 'INV001'\r\n-- 3. \u660e\u7d30\u884c + \u5546\u54c1\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER () AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN Item it ON il.itemID = it.ID\r\nWHERE il.invoiceID = 'INV001';<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n4. \u69cb\u9020\u5316CSV\u306e\u751f\u6210<\/h2>\n
\n\n\u3053\u3053\u3067\u306f\u3001Invoice\uff08\u8acb\u6c42\u66f8\uff09\u306b\u95a2\u3059\u308b\u30c7\u30fc\u30bf\u3092\u7d71\u5408\u3057\u305f\u69cb\u9020\u5316CSV\u3092\u751f\u6210\u3059\u308bSQL\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002CSV\u306f\u3001\u8acb\u6c42\u66f8\u3054\u3068\u306b\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\u30921\u3064\u306e\u8868\u306b\u307e\u3068\u3081\u305f\u5f62\u5f0f\u3067\u51fa\u529b\u3055\u308c\u3001
dInvoice<\/code>,dTaxBreakdown<\/code>, `dInvoiceLine`\u306e\u5404\u5217\u306b\u3088\u3063\u3066\u968e\u5c64\u304c\u8868\u73fe\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n\n4.1. \u69cb\u9020\u5316CSV\u751f\u6210SQL<\/h3>\n
\n\u4ee5\u4e0b\u306eSQL\u306f\u3001\u8acb\u6c42\u66f8\u3054\u3068\u306b\u9023\u756a\uff08
dInvoice<\/code>\uff09\u3092\u632f\u308a\u3064\u3064\u3001\u5404\u30c7\u30fc\u30bf\uff08\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u3001\u7a0e\u533a\u5206\u5185\u8a33\u3001\u660e\u7d30\u884c\uff09\u3092\u7d71\u4e00\u3055\u308c\u305f\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u3067UNION\u3057\u30661\u3064\u306e\u30d3\u30e5\u30fc\uff08\u307e\u305f\u306f\u30c6\u30fc\u30d6\u30eb\uff09\u3068\u3057\u3066\u51fa\u529b\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n\n\n-- \u65e2\u306b\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u305f\u3089\u524a\u9664\r\nDROP TABLE IF EXISTS InvoiceStructuredCSV;\r\n\r\nCREATE table InvoiceStructuredCSV AS\r\n-- (1) WITH\u53e5\u3067\u8acb\u6c42\u66f8\u3054\u3068\u306b\u305d\u306e\u9806\u5e8f\u756a\u53f7\u3092dInvoice\u306b\u5272\u308a\u5f53\u3066\u308b\r\nWITH InvoiceIndex AS (\r\n SELECT\r\n ID AS invoiceID,\r\n ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice\r\n FROM Invoice\r\n)\r\n-- (2) \u30d8\u30c3\u30c0\u884c\u3092\u62bd\u51fa\uff08dInvoice \u306e\u307f\u8a2d\u5b9a\uff09\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN InvoiceIndex ii ON i.ID = ii.invoiceID\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\n-- (3) \u7a0e\u5185\u8a33\u3092\u62bd\u51fa\uff08dTaxBreakdown \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nJOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID\r\n-- (4) \u660e\u7d30\uff0b\u5546\u54c1\u60c5\u5831\u3092\u62bd\u51fa\uff08dInvoiceLine \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID\r\nJOIN Item it ON il.itemID = it.ID\r\n-- (5) \u30bd\u30fc\u30c8\uff1a\u30d8\u30c3\u30c0\u2192\u7a0e\u5185\u8a33\u2192\u660e\u7d30\r\nORDER BY\r\n dInvoice,\r\n CASE\r\n WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0\r\n WHEN dTaxBreakdown IS NOT NULL THEN 1\r\n WHEN dInvoiceLine IS NOT NULL THEN 2\r\n ELSE 3\r\n END,\r\n dTaxBreakdown,\r\n dInvoiceLine;<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n\n4.2. \u89e3\u8aac<\/h3>\n
\n(1)
WITH InvoiceIndex AS (…)<\/code>
\n\u8acb\u6c42\u66f8ID\u9806\u306b\u9023\u756a\uff08dInvoice<\/code>\uff09\u3092\u4ed8\u4e0e\u3057\u3001\u3059\u3079\u3066\u306eUNION\u5bfe\u8c61\u306b\u5171\u901a\u306e\u30ad\u30fc\u3068\u3057\u3066\u5229\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n\n(2) \u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\uff08\u57fa\u672c\u60c5\u5831\uff09\u3092\u58f2\u308a\u624b\u3001\u8cb7\u3044\u624b\u306e\u60c5\u5831\u3068\u5171\u306b1\u884c\u306b\u307e\u3068\u3081\u3066\u51fa\u529b\u3057\u307e\u3059\u3002`dInvoice`\u4ee5\u5916\u306e\u968e\u5c64\u30ad\u30fc\u306fNULL\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<\/div>\n
\n(3) \u8acb\u6c42\u66f8\u3054\u3068\u306e\u7a0e\u533a\u5206\u5185\u8a33\u3092`TaxBreakdown`\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u53d6\u5f97\u3057\u3001`dTaxBreakdown`\u306b\u9023\u756a\u3092\u632f\u3063\u3066\u51fa\u529b\u3057\u307e\u3059\u3002\u30d8\u30c3\u30c0\u60c5\u5831\u306f\u542b\u307f\u307e\u305b\u3093\u3002<\/p>\n<\/div>\n
\n(4) \u8acb\u6c42\u66f8\u660e\u7d30\u884c`InvoiceLine`\u3068\u5546\u54c1\u60c5\u5831`Item`\u3092\u7d50\u5408\u3057\u3001`dInvoiceLine`\u306b\u9023\u756a\u3092\u632f\u3063\u3066\u51fa\u529b\u3057\u307e\u3059\u3002\u3053\u3061\u3089\u3082\u30d8\u30c3\u30c0\u60c5\u5831\u306f\u542b\u307f\u307e\u305b\u3093\u3002<\/p>\n<\/div>\n
\n(5) \u6700\u5f8c\u306b
ORDER BY<\/code> \u306b\u3088\u3063\u3066\u3001`dInvoice`\u3054\u3068\u306b\u30d8\u30c3\u30c0\u2192\u7a0e\u533a\u5206\u5185\u8a33\u2192\u660e\u7d30\u884c\u306e\u9806\u306b\u4e26\u3079\u66ff\u3048\u3092\u884c\u3044\u307e\u3059\u3002\u3053\u308c\u306b\u3088\u308a\u3001\u4eba\u9593\u306b\u3082\u6a5f\u68b0\u306b\u3082\u51e6\u7406\u3057\u3084\u3059\u3044\u69cb\u9020\u5316CSV\u304c\u5f97\u3089\u308c\u307e\u3059\u3002<\/p>\n<\/div>\n\n
\n
\n<\/span><\/p>\n<\/div>\n\n\u3053\u306e\u69cb\u9020\u5316CSV\u306f\u3001\u884c\u30ec\u30d9\u30eb\u3067\u610f\u5473\u304c\u7570\u306a\u308b\u30c7\u30fc\u30bf\uff08\u30d8\u30c3\u30c0\u30fb\u7a0e\u533a\u5206\u5185\u8a33\u30fb\u660e\u7d30\u884c\uff09\u30921\u3064\u306eCSV\u30d5\u30a1\u30a4\u30eb\u306b\u307e\u3068\u3081\u3066\u3044\u307e\u3059\u3002
\n\u5f8c\u7d9a\u51e6\u7406\u3067\u306f\u3001dInvoice<\/code>,dTaxBreakdown<\/code>,dInvoiceLine<\/code> \u306e\u3044\u305a\u308c\u304c\u975eNULL\u304b\u3067\u884c\u306e\u7a2e\u985e\u3092\u8b58\u5225\u3067\u304d\u307e\u3059\u3002<\/p>\n<\/div>\n\n
\n \n\n \n \n \n<\/colgroup>\n \n \n \u5bfe\u8c61<\/p>\n<\/td>\n
\n dInvoice
\n\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0<\/p>\n<\/td>\n\n dTaxBreakdown
\n\u7a0e\u533a\u5206\u5185\u8a33<\/p>\n<\/td>\n\n dInvoiceLine
\n\u660e\u7d30\u884c<\/p>\n<\/td>\n<\/tr>\n\n \n \u8acb\u6c42\u66f8\u30d8\u30c3\u30c0<\/p>\n<\/td>\n
\n \u6570\u5024<\/strong><\/p>\n<\/td>\n
\n \u7a7a\u6b04<\/p>\n<\/td>\n
\n \u7a7a\u6b04<\/p>\n<\/td>\n<\/tr>\n
\n \n \u7a0e\u533a\u5206\u5185\u8a33<\/p>\n<\/td>\n
\n \u6570\u5024<\/strong><\/p>\n<\/td>\n
\n \u6570\u5024<\/strong><\/p>\n<\/td>\n
\n \u7a7a\u6b04<\/p>\n<\/td>\n<\/tr>\n
\n \n \u660e\u7d30\u884c<\/p>\n<\/td>\n
\n \u6570\u5024<\/strong><\/p>\n<\/td>\n
\n \u7a7a\u6b04<\/p>\n<\/td>\n
\n \u6570\u5024<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n
\n5. \u62bd\u51fa\u30af\u30a8\u30ea<\/h2>\n
\n\n\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u60c5\u5831\u3084\u7a0e\u533a\u5206\u5185\u8a33\u60c5\u5831\u306a\u3069\u3092\u53d6\u308a\u51fa\u3059\u306b\u306f\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u3092\u6761\u4ef6\u306b\u3001\u5bfe\u5fdc\u3059\u308b\u30c7\u30a3\u30e1\u30f3\u30b8\u30e7\u30f3\u304c\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u308b\u884c\u3092\u9078\u629e\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u8acb\u6c42\u66f8\u30d8\u30c3\u30c0\u306e\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT DISTINCT\r\n `Invoice.ID` AS ID,\r\n issueDate,\r\n typeCode,\r\n dueDate,\r\n sumOfLineNetAmount,\r\n totalAmountWithoutTax,\r\n totalTaxAmount,\r\n totalAmountWithTax,\r\n `Buyer.name`,\r\n `Seller.name`,\r\n `Seller.taxID`\r\nFROM InvoiceStructuredCSV\r\nWHERE `Invoice.ID` = 'INV001';<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u7a0e\u533a\u5206\u5185\u8a33\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT\r\n d.dInvoice AS invoiceRef,\r\n d.`TaxBreakdown.taxCategoryCode`,\r\n d.`TaxBreakdown.taxCategoryRate`,\r\n d.`TaxBreakdown.taxCategoryTaxAmount`\r\nFROM InvoiceStructuredCSV d\r\nWHERE d.dInvoice = (\r\n SELECT dInvoice\r\n FROM InvoiceStructuredCSV\r\n WHERE `Invoice.ID` = 'INV001'\r\n)\r\nAND d.dTaxBreakdown IS NOT NULL;<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n\u69cb\u9020\u5316CSV\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u3001\u8acb\u6c42\u66f8\u756a\u53f7’INV001’\u306e\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b\u306f\u6b21\u306eSELECT\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\u660e\u7d30\u884c\u30c7\u30fc\u30bf\u691c\u7d22<\/div>\n\nSELECT\r\n d.dInvoice AS invoiceRef,\r\n d.`InvoiceLine.ID`,\r\n d.`InvoiceLine.netAmount`,\r\n d.`InvoiceLine.quantity`,\r\n d.`InvoiceLine.uom`,\r\n d.`Item.ID`,\r\n d.`Item.name`,\r\n d.`Item.price`,\r\n d.`Item.baseQuantity`,\r\n d.`Item.uom`,\r\n d.`Item.taxCategoryCode`,\r\n d.`Item.taxCategoryRate`\r\nFROM InvoiceStructuredCSV d\r\nWHERE d.dInvoice = (\r\n SELECT dInvoice\r\n FROM InvoiceStructuredCSV\r\n WHERE `Invoice.ID` = 'INV001'\r\n AND dInvoice IS NOT NULL\r\n)\r\nAND d.dInvoiceLine IS NOT NULL;<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n6. \u307e\u3068\u3081<\/h2>\n
\n\n\u69cb\u9020\u5316CSV\u306f\u3001\u968e\u5c64\u69cb\u9020\u3092\u30d5\u30e9\u30c3\u30c8\u306b1\u3064\u306eCSV\u306b\u7d71\u5408\u3057\u306a\u304c\u3089\u3001\u5404\u968e\u5c64\u3092dInvoice, dTaxBreakdown, dInvoiceLine\u306a\u3069\u306e\u8b58\u5225\u5b50\u3067\u793a\u3059\u3053\u3068\u306b\u3088\u308a\u3001\u52b9\u7387\u7684\u306a\u96c6\u8a08\u30fb\u62bd\u51fa\u3092\u53ef\u80fd\u306b\u3057\u307e\u3059\u3002\u65e5\u672c\u306e\u9069\u683c\u8acb\u6c42\u66f8\u5236\u5ea6\u306b\u5bfe\u5fdc\u3057\u305f\u8a2d\u8a08\u3082\u53ef\u80fd\u3067\u3059\u3002<\/p>\n<\/div>\n
\nNULL\u306b\u5bfe\u3057\u3066\u3001\u6587\u5b57’NULL’\u3092\u51fa\u529b\u305b\u305a\u3001\u7a7a\u6b04\u3068\u3059\u308b\u305f\u3081\u306b\u6b21\u306e\u3088\u3046\u306bIFNULL\u95a2\u6570\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nSELECT\r\n IFNULL(dInvoice, '') AS dInvoice,\r\n IFNULL(dTaxBreakdown, '') AS dTaxBreakdown,\r\n IFNULL(dInvoiceLine, '') AS dInvoiceLine,\r\n IFNULL(`Invoice.ID`, '') AS `Invoice.ID`,\r\n IFNULL(issueDate, '') AS issueDate,\r\n IFNULL(typeCode, '') AS typeCode,\r\n IFNULL(dueDate, '') AS dueDate,\r\n IFNULL(`Buyer.name`, '') AS `Buyer.name`,\r\n IFNULL(`Seller.name`, '') AS `Seller.name`,\r\n IFNULL(`Seller.taxID`, '') AS `Seller.taxID`,\r\n IFNULL(sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(totalAmountWithTax, '') AS totalAmountWithTax,\r\n IFNULL(`TaxBreakdown.taxCategoryCode`, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(`TaxBreakdown.taxCategoryRate`, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(`TaxBreakdown.taxCategoryTaxAmount`, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n IFNULL(`InvoiceLine.ID`, '') AS `InvoiceLine.ID`,\r\n IFNULL(`InvoiceLine.netAmount`, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(`InvoiceLine.quantity`, '') AS `InvoiceLine.quantity`,\r\n IFNULL(`InvoiceLine.uom`, '') AS `InvoiceLine.uom`,\r\n IFNULL(`Item.ID`, '') AS `Item.ID`,\r\n IFNULL(`Item.name`, '') AS `Item.name`,\r\n IFNULL(`Item.price`, '') AS `Item.price`,\r\n IFNULL(`Item.baseQuantity`, '') AS `Item.baseQuantity`,\r\n IFNULL(`Item.uom`, '') AS `Item.uom`,\r\n IFNULL(`Item.taxCategoryCode`, '') AS `Item.taxCategoryCode`,\r\n IFNULL(`Item.taxCategoryRate`, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceStructuredCSV;<\/code><\/pre>\n<\/div>\n<\/div>\n\n\u69cb\u9020\u5316CSV<\/div>\n\ndInvoice,dTaxBreakdown,dInvoiceLine,Invoice.ID,issueDate,typeCode,dueDate,Buyer.name,Seller.name,Seller.taxID,sumOfLineNetAmount,totalAmountWithoutTax,totalTaxAmount,totalAmountWithTax,TaxBreakdown.taxCategoryCode,TaxBreakdown.taxCategoryRate,TaxBreakdown.taxCategoryTaxAmount,InvoiceLine.ID,InvoiceLine.netAmount,InvoiceLine.quantity,InvoiceLine.uom,Item.ID,Item.name,Item.price,Item.baseQuantity,Item.uom,Item.taxCategoryCode,Item.taxCategoryRate\r\n1,,,INV001,2024-06-30,380,2024-07-15,\u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e,\u4f50\u85e4\u88fd\u4f5c\u6240,T098765432109,16200.0,16200.0,246.0,18666.0,,,,,,,,,,,,,,\r\n1,1,,,,,,,,,,,,,AA,0.08,96.0,,,,,,,,,,,\r\n1,2,,,,,,,,,,,,,S,0.1,150.0,,,,,,,,,,,\r\n1,,1,,,,,,,,,,,,,,,L001,15000.0,1.0,EA,1001,\u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2,15000.0,1.0,EA,S,0.1\r\n1,,2,,,,,,,,,,,,,,,L002,1200.0,1.0,BOX,2001,\u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc,1200.0,1.0,BOX,AA,0.08\r\n2,,,INV002,2024-07-01,380,2024-07-15,Greenwood High School,Liberty Office Supply,T765432109876,620.0,620.0,62.0,682.0,,,,,,,,,,,,,,\r\n2,1,,,,,,,,,,,,,S,0.1,62.0,,,,,,,,,,,\r\n2,,1,,,,,,,,,,,,,,,L003,500.0,1.0,EA,A001,Notebook,500.0,1.0,EA,S,0.1\r\n2,,2,,,,,,,,,,,,,,,L004,120.0,1.0,EA,A002,Ballpoint Pen,120.0,1.0,EA,S,0.1<\/code><\/pre>\n<\/div>\n<\/div>\n\n
\n \n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n<\/colgroup>\n \n \ndInvoice<\/th>\n dTaxBreakdown<\/th>\n dInvoiceLine<\/th>\n Invoice.ID<\/th>\n issueDate<\/th>\n typeCode<\/th>\n dueDate<\/th>\n Buyer.name<\/th>\n Seller.name<\/th>\n Seller.taxID<\/th>\n sumOfLineNetAmount<\/th>\n totalAmountWithoutTax<\/th>\n totalTaxAmount<\/th>\n totalAmountWithTax<\/th>\n TaxBreakdown.taxCategoryCode<\/th>\n TaxBreakdown.taxCategoryRate<\/th>\n TaxBreakdown.taxCategoryTaxAmount<\/th>\n InvoiceLine.ID<\/th>\n InvoiceLine.netAmount<\/th>\n InvoiceLine.quantity<\/th>\n InvoiceLine.uom<\/th>\n Item.ID<\/th>\n Item.name<\/th>\n Item.price<\/th>\n Item.baseQuantity<\/th>\n Item.uom<\/th>\n Item.taxCategoryCode<\/th>\n Item.taxCategoryRate<\/th>\n<\/tr>\n<\/thead>\n \n \n 1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n \n INV001<\/p>\n<\/td>\n
\n 2024-06-30<\/p>\n<\/td>\n
\n 380<\/p>\n<\/td>\n
\n 2024-07-15<\/p>\n<\/td>\n
\n \u5c71\u7530\u5546\u4e8b\u682a\u5f0f\u4f1a\u793e<\/p>\n<\/td>\n
\n \u4f50\u85e4\u88fd\u4f5c\u6240<\/p>\n<\/td>\n
\n T098765432109<\/p>\n<\/td>\n
\n 16200<\/p>\n<\/td>\n
\n 16200<\/p>\n<\/td>\n
\n 246<\/p>\n<\/td>\n
\n 18446<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n \n 1<\/p>\n<\/td>\n
\n 1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \n S<\/p>\n<\/td>\n
\n 0.10<\/p>\n<\/td>\n
\n 150<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n \n 1<\/p>\n<\/td>\n
\n 2<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \n AA<\/p>\n<\/td>\n
\n 0.08<\/p>\n<\/td>\n
\n 96<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n \n 1<\/p>\n<\/td>\n
<\/td>\n \n 1<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \n L001<\/p>\n<\/td>\n
\n 15000<\/p>\n<\/td>\n
\n 1.000<\/p>\n<\/td>\n
\n EA<\/p>\n<\/td>\n
\n 1001<\/p>\n<\/td>\n
\n \u30aa\u30d5\u30a3\u30b9\u30c1\u30a7\u30a2<\/p>\n<\/td>\n
\n 15000<\/p>\n<\/td>\n
\n 1.000<\/p>\n<\/td>\n
\n EA<\/p>\n<\/td>\n
\n S<\/p>\n<\/td>\n
\n 0.10<\/p>\n<\/td>\n<\/tr>\n
\n \n 1<\/p>\n<\/td>\n
<\/td>\n \n 2<\/p>\n<\/td>\n
<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n \n L002<\/p>\n<\/td>\n
\n 1200<\/p>\n<\/td>\n
\n 1.000<\/p>\n<\/td>\n
\n BOX<\/p>\n<\/td>\n
\n 2001<\/p>\n<\/td>\n
\n \u30df\u30cd\u30e9\u30eb\u30a6\u30a9\u30fc\u30bf\u30fc<\/p>\n<\/td>\n
\n 1200<\/p>\n<\/td>\n
\n 1.000<\/p>\n<\/td>\n
\n BOX<\/p>\n<\/td>\n
\n AA<\/p>\n<\/td>\n
\n 0.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n\u672c\u8a18\u4e8b\u3067\u4f7f\u7528\u3057\u3066\u3044\u308b\u30c6\u30fc\u30d6\u30eb\u306eE-R\u56f3\u3092\u4ee5\u4e0b\u306b\u793a\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\n\n7. \u69cb\u9020\u5316CSV\u30d5\u30a1\u30a4\u30eb\u51fa\u529bPython\u30b9\u30af\u30ea\u30d7\u30c8<\/h2>\n
\n\nPython\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u30d5\u30a1\u30a4\u30eb\u51fa\u529b\u3059\u308b\u306b\u306f\u3001pymysql\u3068pandas\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u4f7f\u7528\u3059\u308b\u6b21\u306e\u3088\u3046\u306a\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u51e6\u7406\u3057\u307e\u3059\u3002<\/p>\n<\/div>\n
\n\nimport pymysql\r\nimport pandas as pd\r\n\r\n# --- Configuration ---\r\ndb_config = {\r\n \"host\": \"localhost\",\r\n \"user\": \"root\",\r\n \"password\": \"user password\",\r\n \"database\": \"structured csv database\",\r\n \"charset\": \"utf8mb4\"\r\n}\r\ndb_config = {\r\n \"host\": \"localhost\",\r\n \"user\": \"root\",\r\n \"password\": \"$V1-mysql-N0\",\r\n \"database\": \"structured_csv\",\r\n \"charset\": \"utf8mb4\"\r\n}\r\noutput_csv_path0 = \"structured_invoice_export0.csv\"\r\noutput_csv_path1 = \"structured_invoice_export1.csv\"\r\ninvoice_id = \"{invoice_id}\"\r\n\r\n# --- SQL Query ---\r\nsql_query0 = f\"\"\"\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\nWHERE i.ID = '{invoice_id}'\r\n-- 2. Tax Breakdown Rows\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n ROW_NUMBER() OVER () AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nWHERE tb.invoiceID = '{invoice_id}'\r\n-- 3. Invoice Line + Item Rows\r\nUNION ALL\r\nSELECT\r\n 1 AS dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER () AS dInvoiceLine,\r\n '' AS `Invoice.ID`,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN Item it ON il.itemID = it.ID\r\nWHERE il.invoiceID = '{invoice_id}';\r\n\"\"\"\r\n\r\nsql_query1 = f\"\"\"\r\n-- (1) \u8acb\u6c42\u66f8\u3054\u3068\u306b\u305d\u306e\u9806\u5e8f\u756a\u53f7\u3092\u5b9a\u7fa9\r\nWITH InvoiceIndex AS (\r\n SELECT\r\n ID AS invoiceID,\r\n ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice\r\n FROM Invoice\r\n)\r\n-- (2) \u30d8\u30c3\u30c0\u884c\u3092\u62bd\u51fa\uff08dInvoice \u306e\u307f\u8a2d\u5b9a\uff09\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n IFNULL(i.ID, '') AS `Invoice.ID`,\r\n IFNULL(i.issueDate, '') AS issueDate,\r\n IFNULL(i.typeCode, '') AS typeCode,\r\n IFNULL(i.dueDate, '') AS dueDate,\r\n IFNULL(b.name, '') AS `Buyer.name`,\r\n IFNULL(s.name, '') AS `Seller.name`,\r\n IFNULL(s.taxID, '') AS `Seller.taxID`,\r\n IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,\r\n IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,\r\n IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,\r\n IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,\r\n '' AS `TaxBreakdown.taxCategoryCode`,\r\n '' AS `TaxBreakdown.taxCategoryRate`,\r\n '' AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '' AS `InvoiceLine.ID`,\r\n '' AS `InvoiceLine.netAmount`,\r\n '' AS `InvoiceLine.quantity`,\r\n '' AS `InvoiceLine.uom`,\r\n '' AS `Item.ID`,\r\n '' AS `Item.name`,\r\n '' AS `Item.price`,\r\n '' AS `Item.baseQuantity`,\r\n '' AS `Item.uom`,\r\n '' AS `Item.taxCategoryCode`,\r\n '' AS `Item.taxCategoryRate`\r\nFROM Invoice i\r\nJOIN InvoiceIndex ii ON i.ID = ii.invoiceID\r\nJOIN Buyer b ON i.buyerID = b.ID\r\nJOIN Seller s ON i.sellerID = s.ID\r\n-- (3) \u7a0e\u5185\u8a33\u3092\u62bd\u51fa\uff08dTaxBreakdown \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,\r\n '' AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,\r\n IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,\r\n IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,\r\n '', '', '', '',\r\n '', '', '', '', '', '', ''\r\nFROM TaxBreakdown tb\r\nJOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID\r\n-- (4) \u660e\u7d30\uff0b\u5546\u54c1\u60c5\u5831\u3092\u62bd\u51fa\uff08dInvoiceLine \u306e\u307f\u8a2d\u5b9a\uff09\r\nUNION ALL\r\nSELECT\r\n ii.dInvoice,\r\n '' AS dTaxBreakdown,\r\n ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,\r\n '', '', '', '', '', '', '', '', '', '', '', '', '', '',\r\n IFNULL(il.ID, '') AS `InvoiceLine.ID`,\r\n IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,\r\n IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,\r\n IFNULL(il.uom, '') AS `InvoiceLine.uom`,\r\n IFNULL(it.ID, '') AS `Item.ID`,\r\n IFNULL(it.name, '') AS `Item.name`,\r\n IFNULL(it.price, '') AS `Item.price`,\r\n IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,\r\n IFNULL(it.uom, '') AS `Item.uom`,\r\n IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,\r\n IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`\r\nFROM InvoiceLine il\r\nJOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID\r\nJOIN Item it ON il.itemID = it.ID\r\n-- (5) \u30bd\u30fc\u30c8\uff1a\u30d8\u30c3\u30c0\u2192\u7a0e\u5185\u8a33\u2192\u660e\u7d30\r\nORDER BY\r\n dInvoice,\r\n CASE\r\n WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0\r\n WHEN dTaxBreakdown IS NOT NULL THEN 1\r\n WHEN dInvoiceLine IS NOT NULL THEN 2\r\n ELSE 3\r\n END,\r\n dTaxBreakdown,\r\n dInvoiceLine;\r\n\"\"\"\r\n# --- Execution ---\r\nconnection = pymysql.connect(**db_config)\r\n\r\ndf0 = pd.read_sql(sql_query0, connection)\r\n# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---\r\ndf0[\"dInvoice\"] = df0[\"dInvoice\"].astype(\"Int64\")\r\ndf0[\"dTaxBreakdown\"] = df0[\"dTaxBreakdown\"].astype(\"Int64\")\r\ndf0[\"dInvoiceLine\"] = df0[\"dInvoiceLine\"].astype(\"Int64\")\r\ndf0.to_csv(output_csv_path0, index=False, encoding=\"utf-8-sig\")\r\n\r\ndf1 = pd.read_sql(sql_query1, connection)\r\n# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---\r\ndf1[\"dInvoice\"] = df1[\"dInvoice\"].astype(\"Int64\")\r\ndf1[\"dTaxBreakdown\"] = df1[\"dTaxBreakdown\"].astype(\"Int64\")\r\ndf1[\"dInvoiceLine\"] = df1[\"dInvoiceLine\"].astype(\"Int64\")\r\ndf1.to_csv(output_csv_path1, index=False, encoding=\"utf-8-sig\")\r\n\r\nconnection.close()\r\n\r\nprint(f\"\u2705 Exported invoice to {output_csv_path0} and {output_csv_path1}.\")<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n