{"id":11909,"date":"2025-12-29T10:39:55","date_gmt":"2025-12-29T01:39:55","guid":{"rendered":"https:\/\/www.moonmile.net\/blog\/?p=11909"},"modified":"2025-12-30T15:30:56","modified_gmt":"2025-12-30T06:30:56","slug":"%e8%ab%96%e7%90%86%e5%89%8a%e9%99%a4%e3%81%a8%e7%89%a9%e7%90%86%e5%89%8a%e9%99%a4%e3%81%ae%e7%b6%9a%e3%81%8d%e3%80%80%e9%83%a8%e5%93%81%e5%8f%97%e6%b3%a8%e3%82%b7%e3%82%b9%e3%83%86%e3%83%a0%e3%82%92-r","status":"publish","type":"post","link":"http:\/\/www.moonmile.net\/blog\/archives\/11909","title":{"rendered":"\u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u7d9a\u304d\u3000\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u3092 Rust + SQL \u6587\u3067\u89e3\u3044\u3066\u307f\u308b"},"content":{"rendered":"\n

\u524d\u56de\u306e \u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u7d9a\u304d\uff08\u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u306e\u4f8b\uff09https:\/\/www.moonmile.net\/blog\/archives\/11888<\/a> \u3067\u306f\u3001\u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u9055\u3044\u3067\u3042\u307e\u308a\u5dee\u304c\u51fa\u3066\u6765\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u3082\u3046\u5c11\u3057\u8e0f\u307f\u8fbc\u3093\u3067\u307f\u307e\u3059\u3002\u3044\u308f\u3086\u308b SQL \u6587\u3092\u76f4\u63a5\u66f8\u3044\u3066\u7de8\u96c6\u3059\u308b\u4f8b\u3092\u898b\u3066\u3044\u304d\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n

\u7d50\u8ad6\u304b\u3089\u8a00\u3048\u3070\u3001SQL \u6587\u3092\u76f4\u63a5\u66f8\u304f\u3068\u304d\u306b\u306f deleted_at \u30ab\u30e9\u30e0\u3092\u4f7f\u3063\u305f\u8ad6\u7406\u524a\u9664\u306f\u9762\u5012\u304f\u3055\u3044\u3057\u7d50\u69cb\u4e0d\u5229\u3067\u3059\u3002\u300c\u8ad6\u7406\u524a\u9664\u304c\u30a2\u30f3\u30c1\u30d1\u30bf\u30fc\u30f3\u300d\u3068\u79f0\u3055\u308c\u308b\u6240\u4ee5\u304c\u3053\u3053\u306b\u3042\u308a\u307e\u3059\u3002\u305f\u3060\u3057\u3001\u73fe\u5728\u306b\u304a\u3044\u3066\u306f ORM \u3092\u4f7f\u3063\u3066 deleted_at \u30ab\u30e9\u30e0\u3092\u3046\u307e\u304f\u96a0\u853d\u3059\u308b\u65b9\u6cd5\u304c\u3042\u308b\u306e\u3067\u3001ORM \u3092\u4f7f\u3063\u3066\u3044\u308b\u9650\u308a\u306f\u3042\u307e\u308a\u4e0d\u4fbf\u3092\u611f\u3058\u307e\u305b\u3093\u3002\u305d\u3046\u3044\u3046\u610f\u5473\u3067\u306f\u300c\u8ad6\u7406\u524a\u9664\u306f\u30a2\u30f3\u30c1\u30d1\u30bf\u30fc\u30f3\u300d\u3068\u65ad\u8a00\u3057\u3066\u3057\u307e\u3046\u306e\u306f\u3001\u3061\u3087\u3063\u3068\u904e\u53bb\u306e\u8a71\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002<\/p>\n\n\n\n

\u8ad6\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/h2>\n\n\n
\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\nCREATE TABLE parts (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_number VARCHAR(64) UNIQUE NOT NULL,\n    part_name VARCHAR(255) NOT NULL,\n    description TEXT NULL,\n    unit_price DECIMAL(10, 2) NOT NULL,\n    lead_time_days INT DEFAULT 0,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    deleted_at TIMESTAMP NULL\n);\n \n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\nCREATE TABLE part_details (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    weight DECIMAL(10, 4) NULL,\n    dimensions VARCHAR(255) NULL,\n    material VARCHAR(128) NULL,\n    storage_location VARCHAR(128) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\nCREATE TABLE part_suppliers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    supplier_id BIGINT NOT NULL,\n    supplier_part_number VARCHAR(64) NULL,\n    supplier_price DECIMAL(10, 2) NOT NULL,\n    lead_time_days INT DEFAULT 0,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id),\n    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u53d7\u6ce8\u60c5\u5831\nCREATE TABLE purchase_orders (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    po_number VARCHAR(64) UNIQUE NOT NULL,\n    supplier_id BIGINT NOT NULL,\n    order_date DATE NOT NULL,\n    expected_delivery_date DATE NOT NULL,\n    actual_delivery_date DATE NULL,\n    status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n \n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\nCREATE TABLE part_production (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    production_date DATE NOT NULL,\n    quantity_produced INT NOT NULL,\n    quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n    notes TEXT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n \n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\nCREATE TABLE bom_items (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    bom_id BIGINT NOT NULL,\n    part_id BIGINT NOT NULL,\n    quantity INT NOT NULL,\n    sequence INT NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (bom_id) REFERENCES bom(id),\n    FOREIGN KEY (part_id) REFERENCES parts(id),\n    UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n \n-- BOM \u30de\u30b9\u30bf\u30fc\nCREATE TABLE bom (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    product_number VARCHAR(64) UNIQUE NOT NULL,\n    product_name VARCHAR(255) NOT NULL,\n    version INT DEFAULT 1,\n    effective_date DATE NOT NULL,\n    status ENUM('active', 'obsolete') DEFAULT 'active',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    deleted_at TIMESTAMP NULL\n);\n \n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\nCREATE TABLE customer_orders (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    order_number VARCHAR(64) UNIQUE NOT NULL,\n    customer_id BIGINT NOT NULL,\n    bom_id BIGINT NOT NULL,\n    order_date DATE NOT NULL,\n    delivery_date DATE NOT NULL,\n    quantity INT NOT NULL,\n    status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (customer_id) REFERENCES customers(id),\n    FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n \n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\nCREATE TABLE order_history (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    customer_order_id BIGINT NOT NULL,\n    status_changed_to VARCHAR(64) NOT NULL,\n    changed_at TIMESTAMP NOT NULL,\n    notes TEXT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n \n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\nCREATE TABLE customers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    customer_code VARCHAR(64) UNIQUE NOT NULL,\n    customer_name VARCHAR(255) NOT NULL,\n    contact_person VARCHAR(128) NULL,\n    phone VARCHAR(20) NULL,\n    email VARCHAR(255) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n \n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\nCREATE TABLE suppliers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    supplier_code VARCHAR(64) UNIQUE NOT NULL,\n    supplier_name VARCHAR(255) NOT NULL,\n    contact_person VARCHAR(128) NULL,\n    phone VARCHAR(20) NULL,\n    email VARCHAR(255) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n<\/pre><\/div>\n\n\n
\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/h2>\n\n\n
\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE parts (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_number VARCHAR(64) UNIQUE NOT NULL,\n    part_name VARCHAR(255) NOT NULL,\n    description TEXT NULL,\n    unit_price DECIMAL(10, 2) NOT NULL,\n    lead_time_days INT DEFAULT 0,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u90e8\u54c1\u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE parts_history (\n    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    part_number VARCHAR(64) NOT NULL,\n    part_name VARCHAR(255) NOT NULL,\n    description TEXT NULL,\n    unit_price DECIMAL(10, 2) NOT NULL,\n    lead_time_days INT DEFAULT 0,\n    version INT NOT NULL,\n    effective_from TIMESTAMP NOT NULL,\n    effective_to TIMESTAMP NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    product_number VARCHAR(64) UNIQUE NOT NULL,\n    product_name VARCHAR(255) NOT NULL,\n    version INT DEFAULT 1,\n    effective_date DATE NOT NULL,\n    status ENUM('active', 'obsolete') DEFAULT 'active',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- BOM \u30de\u30b9\u30bf\u30fc\u5c65\u6b74\nCREATE TABLE bom_history (\n    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    bom_id BIGINT NOT NULL,\n    product_number VARCHAR(64) NOT NULL,\n    product_name VARCHAR(255) NOT NULL,\n    version INT NOT NULL,\n    effective_date DATE NOT NULL,\n    status ENUM('active', 'obsolete') DEFAULT 'active',\n    effective_from TIMESTAMP NOT NULL,\n    effective_to TIMESTAMP NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u90e8\u54c1\u88dc\u52a9\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_details (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    weight DECIMAL(10, 4) NULL,\n    dimensions VARCHAR(255) NULL,\n    material VARCHAR(128) NULL,\n    storage_location VARCHAR(128) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u8abf\u9054\u5148\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_suppliers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    supplier_id BIGINT NOT NULL,\n    supplier_part_number VARCHAR(64) NULL,\n    supplier_price DECIMAL(10, 2) NOT NULL,\n    lead_time_days INT DEFAULT 0,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id),\n    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u53d7\u6ce8\u60c5\u5831\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE purchase_orders (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    po_number VARCHAR(64) UNIQUE NOT NULL,\n    supplier_id BIGINT NOT NULL,\n    order_date DATE NOT NULL,\n    expected_delivery_date DATE NOT NULL,\n    actual_delivery_date DATE NULL,\n    status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)\n);\n\n-- \u90e8\u54c1\u751f\u7523\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE part_production (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    part_id BIGINT NOT NULL,\n    production_date DATE NOT NULL,\n    quantity_produced INT NOT NULL,\n    quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',\n    notes TEXT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (part_id) REFERENCES parts(id)\n);\n\n-- \u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u30c6\u30fc\u30d6\u30eb\uff08BOM\uff09\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE bom_items (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    bom_id BIGINT NOT NULL,\n    part_id BIGINT NOT NULL,\n    quantity INT NOT NULL,\n    sequence INT NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (bom_id) REFERENCES bom(id),\n    FOREIGN KEY (part_id) REFERENCES parts(id),\n    UNIQUE KEY unique_bom_part (bom_id, part_id)\n);\n\n-- \u53d7\u6ce8\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u7ba1\u7406\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customer_orders (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    order_number VARCHAR(64) UNIQUE NOT NULL,\n    customer_id BIGINT NOT NULL,\n    bom_id BIGINT NOT NULL,\n    order_date DATE NOT NULL,\n    delivery_date DATE NOT NULL,\n    quantity INT NOT NULL,\n    status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    FOREIGN KEY (customer_id) REFERENCES customers(id),\n    FOREIGN KEY (bom_id) REFERENCES bom(id)\n);\n\n-- \u9867\u5ba2\u767a\u6ce8\u5c65\u6b74\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE order_history (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    customer_order_id BIGINT NOT NULL,\n    status_changed_to VARCHAR(64) NOT NULL,\n    changed_at TIMESTAMP NOT NULL,\n    notes TEXT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)\n);\n\n-- \u9867\u5ba2\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE customers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    customer_code VARCHAR(64) UNIQUE NOT NULL,\n    customer_name VARCHAR(255) NOT NULL,\n    contact_person VARCHAR(128) NULL,\n    phone VARCHAR(20) NULL,\n    email VARCHAR(255) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- \u30b5\u30d7\u30e9\u30a4\u30e4\u30fc\u30de\u30b9\u30bf\u30fc\uff08\u7269\u7406\u524a\u9664\uff09\nCREATE TABLE suppliers (\n    id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    supplier_code VARCHAR(64) UNIQUE NOT NULL,\n    supplier_name VARCHAR(255) NOT NULL,\n    contact_person VARCHAR(128) NULL,\n    phone VARCHAR(20) NULL,\n    email VARCHAR(255) NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n<\/pre><\/div>\n\n\n
\u3053\u308c\u3092\u30d9\u30fc\u30b9\u306b\u3057\u3066\u3001Rust \u3092\u4f7f\u3063\u3066 SQL \u6587\u3092\u76f4\u63a5\u66f8\u304f\u30b3\u30fc\u30c9\u3092\u751f\u6210\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002\u5e78\u3044\uff1f\u306b\u3057\u3066 Rust \u3067\u306f softdelete \u7cfb\u306e\u30e9\u30a4\u30d6\u30e9\u30ea\u304c\u5b58\u5728\u3057\u306a\u3044\u306e\u3067\u3001\u3053\u306e\u624b\u306e\u30b3\u30fc\u30c9\u306f SQL \u6587\u3092\u76f4\u63a5\u66f8\u304f\u3057\u304b\u3042\u308a\u307e\u305b\u3093\u3002\u307e\u3042\u3001\u63a2\u305b\u3070 ORM \u304c\u3042\u308b\u3093\u3067\u3057\u3087\u3046\u304c\u3001\u3053\u3053\u3067\u306f\u60aa\u624b\u306e\u305f\u3081\u306b\u72a0\u7272\u306b\u306a\u3063\u3066\u3082\u3089\u3044\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u30b1\u30fc\u30b91<\/strong><\/h2>\n\n\n\n
\u9867\u5ba2\u304b\u3089\u53e4\u3044\u767a\u6ce8\u66f8\u306b\u57fa\u3065\u3044\u3066\u90e8\u54c1\u306e\u767a\u6ce8\u3092\u53d7\u3051\u305f\u3002\u6b8b\u5ff5\u306a\u304c\u3089\u904e\u53bb\u306e\u767a\u6ce8\u306e\u305f\u3081\u306b\u3001\u767a\u6ce8\u66f8\u306e\u4e2d\u306b\u3042\u308b\u3001\u90e8\u54c1\u756a\u53f7\u306f\u73fe\u5728\u6271\u3063\u3066\u3044\u306a\u3044\u3002\u3053\u306e\u90e8\u54c1\u756a\u53f7\u3092\u65b0\u3057\u304f\u3057\u3066\u898b\u7a4d\u3082\u308a\u66f8\u3092\u4f5c\u6210\u3057\u305f\u3044\u3002<\/p>\n\n\n\n
\u3053\u306e\u3068\u304d\u306b\u3001\u904e\u53bb\u306e\u767a\u6ce8\u66f8\u3068\u5408\u8a08\u91d1\u984d\u3092\u7167\u5408\u3057\u3066\u3001\u5dee\u5206\u3092\u8a08\u7b97\u3057\u3066\u304a\u304d\u305f\u3044\u3002<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\nuse sqlx::{MySql, Pool, Row};\nuse sqlx::mysql::MySqlPoolOptions;\nuse decimal::Decimal;\n\n#[derive(Debug, Clone)]\nstruct OrderItem {\n    part_number: String,\n    part_name: String,\n    quantity: i32,\n    unit_price: Decimal,\n    total_price: Decimal,\n}\n\n#[derive(Debug)]\nstruct OrderComparison {\n    old_items: Vec<OrderItem>,\n    new_items: Vec<OrderItem>,\n    old_total: Decimal,\n    new_total: Decimal,\n    difference: Decimal,\n}\n\nasync fn compare_orders_with_replacement(\n    pool: &Pool<MySql>,\n    old_bom_id: i64,\n    old_part_id_b: i64,\n    new_part_id_d: i64,\n) -> Result<OrderComparison, sqlx::Error> {\n    \/\/ \u53e4\u3044\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\n    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price\n         FROM bom_items bi\n         JOIN parts p ON bi.part_id = p.id\n         WHERE bi.bom_id = ? \n         ORDER BY bi.sequence"\n    )\n    .bind(old_bom_id)\n    .fetch_all(pool)\n    .await?\n    .into_iter()\n    .map(|(part_number, part_name, quantity, unit_price)| {\n        let total_price = unit_price * Decimal::from(quantity);\n        OrderItem {\n            part_number,\n            part_name,\n            quantity,\n            unit_price,\n            total_price,\n        }\n    })\n    .collect();\n\n    \/\/ \u65b0\u3057\u3044\u767a\u6ce8\u66f8\u7528\u306b\u90e8\u54c1B\u3092\u90e8\u54c1D\u306b\u7f6e\u304d\u63db\u3048\n    let mut new_items = old_items.clone();\n    for item in &mut new_items {\n        if item.part_number == "B" {\n            \/\/ \u90e8\u54c1D\u306e\u60c5\u5831\u3092\u53d6\u5f97\n            let new_part: (String, String, Decimal) = sqlx::query_as(\n                "SELECT part_number, part_name, unit_price FROM parts WHERE id = ? AND deleted_at IS NULL"\n            )\n            .bind(new_part_id_d)\n            .fetch_one(pool)\n            .await?;\n\n            item.part_number = new_part.0;\n            item.part_name = new_part.1;\n            item.unit_price = new_part.2;\n            item.total_price = new_part.2 * Decimal::from(item.quantity);\n        }\n    }\n\n    let old_total: Decimal = old_items.iter().map(|i| i.total_price).sum();\n    let new_total: Decimal = new_items.iter().map(|i| i.total_price).sum();\n    let difference = new_total - old_total;\n\n    Ok(OrderComparison {\n        old_items,\n        new_items,\n        old_total,\n        new_total,\n        difference,\n    })\n}\n<\/pre><\/div>\n\n\n
\u203b 2025\/12\/30 \u8ffd\u8a18
\u6700\u521d\u306b AI \u306b\u4f5c\u6210\u3057\u3066\u8cb0\u3063\u305f\u3068\u304d\u306b\u3053\u3053\u306eSQL\u6587\u306b\u306f deleted_at IS NULL \u304c\u4ed8\u3044\u3066\u3044\u307e\u3057\u305f\u3002\u3053\u306e\u5834\u5408\u3001”\u53e4\u3044\u90e8\u54c1\u8abf\u9054\u307e\u3067\u8abf\u3079\u308b” \u3053\u3068\u304c\u5fc5\u8981\u306a\u306e\u3067 deleted_at \u306e\u72b6\u614b\u306b\u95a2\u4fc2\u306a\u304f\uff1d\u65b0\u65e7\u95a2\u4fc2\u306a\u304f\u30c7\u30fc\u30bf\u3092\u691c\u7d22\u3057\u306a\u3051\u308c\u3070\u3044\u3051\u307e\u305b\u3093\u3002\u3053\u306e\u90e8\u5206 AI \u3067\u3082\u9593\u9055\u3048\u308b\u306e\u3067\u6ce8\u610f\u304c\u5fc5\u8981\u3067\u3059\u3002<\/p>\n\n\n\n
\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\nuse sqlx::{MySql, Pool, Row};\nuse sqlx::mysql::MySqlPoolOptions;\nuse decimal::Decimal;\n\n#[derive(Debug, Clone)]\nstruct OrderItem {\n    part_number: String,\n    part_name: String,\n    quantity: i32,\n    unit_price: Decimal,\n    total_price: Decimal,\n}\n\n#[derive(Debug)]\nstruct OrderComparison {\n    old_items: Vec<OrderItem>,\n    new_items: Vec<OrderItem>,\n    old_total: Decimal,\n    new_total: Decimal,\n    difference: Decimal,\n}\n\nasync fn compare_orders_with_replacement(\n    pool: &Pool<MySql>,\n    old_bom_id: i64,\n    old_part_number_b: &str,\n    new_part_id_d: i64,\n) -> Result<OrderComparison, sqlx::Error> {\n    \/\/ \u53e4\u3044\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\u3002parts \u306b\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u306e\u6700\u65b0\u7248\u3092\u4f7f\u7528\u3059\u308b\n    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        r#"\n        WITH latest_history AS (\n            SELECT ph.*, ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn\n            FROM parts_history ph\n        )\n        SELECT\n            COALESCE(p.part_number, lh.part_number) AS part_number,\n            COALESCE(p.part_name, lh.part_name) AS part_name,\n            bi.quantity,\n            COALESCE(p.unit_price, lh.unit_price) AS unit_price\n        FROM bom_items bi\n        LEFT JOIN parts p ON p.id = bi.part_id\n        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1\n        WHERE bi.bom_id = ?\n        ORDER BY bi.sequence\n        "#\n    )\n    .bind(old_bom_id)\n    .fetch_all(pool)\n    .await?\n    .into_iter()\n    .map(|(part_number, part_name, quantity, unit_price)| {\n        let total_price = unit_price * Decimal::from(quantity);\n        OrderItem {\n            part_number,\n            part_name,\n            quantity,\n            unit_price,\n            total_price,\n        }\n    })\n    .collect();\n\n    \/\/ \u65b0\u3057\u3044\u767a\u6ce8\u66f8\u7528\u306b\u90e8\u54c1B\u3092\u90e8\u54c1D\u306b\u7f6e\u304d\u63db\u3048\n    let mut new_items = old_items.clone();\n    for item in &mut new_items {\n        if item.part_number == old_part_number_b {\n            let new_part: (String, String, Decimal) = sqlx::query_as(\n                "SELECT part_number, part_name, unit_price FROM parts WHERE id = ?"\n            )\n            .bind(new_part_id_d)\n            .fetch_one(pool)\n            .await?;\n\n            item.part_number = new_part.0;\n            item.part_name = new_part.1;\n            item.unit_price = new_part.2;\n            item.total_price = new_part.2 * Decimal::from(item.quantity);\n        }\n    }\n\n    let old_total: Decimal = old_items.iter().map(|i| i.total_price).sum();\n    let new_total: Decimal = new_items.iter().map(|i| i.total_price).sum();\n    let difference = new_total - old_total;\n\n    Ok(OrderComparison {\n        old_items,\n        new_items,\n        old_total,\n        new_total,\n        difference,\n    })\n}\n<\/pre><\/div>\n\n\n
\u3055\u3066\u30012\u3064\u306e SQL \u3067\u3069\u3061\u3089\u304c\u8907\u96d1\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n\n\n
\n    \/\/ \u53e4\u3044\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\n    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price\n         FROM bom_items bi\n         JOIN parts p ON bi.part_id = p.id\n         WHERE bi.bom_id = ? \n         ORDER BY bi.sequence"\n    )\n\n<\/pre><\/div>\n\n
\n    \/\/ \u53e4\u3044\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\u3002parts \u306b\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u306e\u6700\u65b0\u7248\u3092\u4f7f\u7528\u3059\u308b\n    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        r#"\n        WITH latest_history AS (\n            SELECT ph.*, ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn\n            FROM parts_history ph\n        )\n        SELECT\n            COALESCE(p.part_number, lh.part_number) AS part_number,\n            COALESCE(p.part_name, lh.part_name) AS part_name,\n            bi.quantity,\n            COALESCE(p.unit_price, lh.unit_price) AS unit_price\n        FROM bom_items bi\n        LEFT JOIN parts p ON p.id = bi.part_id\n        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1\n        WHERE bi.bom_id = ?\n        ORDER BY bi.sequence\n        "#\n    )\n<\/pre><\/div>\n\n\n
\u898b\u3066\u308f\u304b\u308b\u901a\u308a\u3001\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3\u306e\u307b\u3046\u304c\u8907\u96d1\u3067\u3059\u3002\u307e\u3042\u3001\u3053\u308c\u306f\u3042\u307e\u308a\u30d5\u30a7\u30a2\u3067\u306f\u306a\u304f\u3066\u3001\u53e4\u3044\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3057\u3088\u3046\u3068\u3057\u305f\u3068\u304d\u306b\u306f\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3\u306e\u307b\u3046\u304c\u624b\u304c\u304b\u304b\u308b\u306e\u306f\u5f53\u305f\u308a\u524d\u306e\u3053\u3068\u3067\u3059\u3002<\/p>\n\n\n\n
\u3053\u308c\u3092\u901a\u5e38\u306e\u73fe\u72b6\u3042\u308b\u30d1\u30fc\u30c4\u304b\u3089\u691c\u7d22\u3059\u308b SQL \u6587\u306b\u7f6e\u304d\u63db\u3048\u308b\u3068<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\n    \/\/ \u901a\u5e38\u306e\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\n    let items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price\n         FROM bom_items bi\n         JOIN parts p ON bi.part_id = p.id\n         WHERE bi.bom_id = ? AND p.deleted_at IS NULL\n         ORDER BY bi.sequence"\n    )\n\n<\/pre><\/div>\n\n\n
\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\n    \/\/ \u901a\u5e38\u767a\u6ce8\u66f8\u304b\u3089\u90e8\u54c1\u60c5\u5831\u3092\u53d6\u5f97\n    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(\n        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price\n         FROM bom_items bi\n         JOIN parts p ON bi.part_id = p.id\n         WHERE bi.bom_id = ?\n         ORDER BY bi.sequence"\n    )\n\n<\/pre><\/div>\n\n\n
\u3068\u306a\u308b\u306e\u3067\u3001\u8ad6\u7406\u524a\u9664\u306e\u5834\u5408\u306f\u5fc5\u305a deleted_at IS NULL \u306e\u6761\u4ef6\u3092\u4ed8\u3051\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\u3053\u308c\u306f\u8ad6\u7406\u524a\u9664\u306e\u5834\u5408\u306f\u5f0a\u5bb3\u3067\u3059\u3088\u306d\u3002
deleted_at IS NULL \u3092\u4ed8\u3051\u308b\u306e\u3092\u5fd8\u308c\u308b\u3068\u5927\u5909\u306a\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002\u9006\u306b\u8a00\u3048\u3070\u3001AI \u30a8\u30fc\u30b8\u30a7\u30f3\u30c8\u306a\u3069\u3092\u4f7f\u3063\u305f\u5834\u5408\u306b\u306f AI \u306f\u5fd8\u308c\u306a\u3044\u306e\u3067\u554f\u984c\u304c\u51fa\u306a\u3044\u3057\u3001ORM \u3092\u4f7f\u3046\u5834\u5408\u3082\u554f\u984c\u304c\u51fa\u307e\u305b\u3093\u3002<\/p>\n\n\n\n
\u305f\u307e\u306b\u3001\u30c7\u30fc\u30bf\u91cf\u304c\u591a\u304f\u306a\u308b\u3068 deleted_at IS NULL \u306e\u6761\u4ef6\u304c\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306b\u5f71\u97ff\u3092\u4e0e\u3048\u308b\u3053\u3068\u304c\u3042\u308b\u3068\u3044\u3046\u8a18\u4e8b\u304c\u3042\u308a\u307e\u3059\u304c\u3001\u305d\u308c\u306f\u305d\u3082\u305d\u3082\u30c6\u30fc\u30d6\u30eb\u8a2d\u8a08\u304c\u60aa\u3044\u3067\u3059\u3002\u305d\u306e\u5834\u5408\u306f\u3001\u9006\u306b\u30ed\u30b0\u3084\u30b8\u30e3\u30fc\u30ca\u30eb\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u691c\u7d22\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u3068\u73fe\u5728\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5206\u3051\u308b\u3068\u3044\u3046\u8a2d\u8a08\u306b\u3059\u3079\u304d\u3067\u3057\u3087\u3046\u3002
\u3053\u306e\u3042\u305f\u308a\u3001\u67b6\u7a7a\u306e\u8b70\u8ad6\u306b\u9665\u308a\u304c\u3061\u306a\u306e\u3067\u3001\u3053\u306e\u3088\u3046\u306b\u5177\u4f53\u4f8b\u3092\u793a\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n\n\n\n
\u30b1\u30fc\u30b92<\/strong><\/h2>\n\n\n\n
\u3082\u3046\u3072\u3068\u3064\u30b1\u30fc\u30b9\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n\n\n\n
\u5ba2\u5148\u304b\u3089\u767a\u6ce8\u66f8\u3092\u53d7\u3051\u305f\u304c\u3001\u767a\u6ce8\u66f8\u306b\u8a18\u8f09\u3055\u308c\u3066\u3044\u308b\u90e8\u54c1\u756a\u53f7\u3092\u30b7\u30b9\u30c6\u30e0\u3067\u691c\u7d22\u3059\u308b\u3068\u51fa\u3066\u6765\u306a\u3044\u3002\u3069\u3046\u3084\u3089\u3001\u4ee5\u524d\u306e\u898b\u7a4d\u3082\u308a\u3092\u51fa\u3057\u305f\u6642\u306b\u306f\u3001\u8abf\u9054\u53ef\u80fd\u3060\u3063\u305f\u3082\u306e\u3067\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u3092\u4f5c\u6210\u3057\u3066\u3044\u305f\u306e\u3060\u304c\u3001\u305d\u306e\u5f8c\u306b\u5ec3\u76e4\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u305f\u3089\u3057\u3044\u3002\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u306e\u6574\u5099\u304c\u8ffd\u3044\u4ed8\u304b\u305a\u3001\u90e8\u54c1\u306e\u7d44\u307f\u5408\u308f\u305b\u306e\u307b\u3046\u304c\u6709\u52b9\u306b\u306a\u3063\u3066\u3044\u308b\u304c\u3001\u90e8\u54c1\u756a\u53f7\u3092\u8abf\u3079\u308b\u3068\u753b\u9762\u306b\u51fa\u3066\u6765\u306a\u3044\u3068\u3044\u3046\u72b6\u6cc1\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u3066\u3044\u308b\u3002<\/p>\n\n\n\n
\u767a\u6ce8\u66f8\u306b\u7d44\u307f\u5408\u308f\u305b\u756a\u53f7\u304c\u66f8\u3044\u3066\u3042\u3063\u305f\u3068\u304d\u306b\u3001\u305d\u306e\u7d44\u307f\u5408\u308f\u305b\u306b\u542b\u307e\u308c\u308b\u90e8\u54c1\u756a\u53f7\u304c\u6709\u52b9\u3067\u3042\u308b\u304b\u3069\u3046\u304b\u306e\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u30021\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u73fe\u72b6\u8abf\u9054\u3067\u304d\u308b\u90e8\u54c1\u3060\u3051\u3067\u30c1\u30a7\u30c3\u30af\u3092\u3059\u308b\u304c\u30012\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u3067\u306f\u3001\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3082\u53c2\u7167\u3057\u3066\u3001\u904e\u53bb\u306b\u5b58\u5728\u3057\u305f\u90e8\u54c1\u756a\u53f7\u3082\u542b\u3081\u3066\u30c1\u30a7\u30c3\u30af\u3092\u884c\u3044\u305f\u3044\u3002
1\u56de\u76ee\u30682\u56de\u76ee\u306e\u30c1\u30a7\u30c3\u30af\u306e\u30e1\u30bd\u30c3\u30c9\u3092\u4f5c\u6210\u3057\u3066\u6b32\u3057\u3044\u3002
\u5165\u529b\u306f customer_orders.order_number \u3068\u3057\u305f\u3044\u3002<\/p>\n\n\n\n
\u8ad6\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\nuse sqlx::{mysql::MySqlPoolOptions, MySql, Pool, Row};\n\n#[derive(Debug)]\nstruct PartCheck {\n    part_number: String,\n    part_name: Option<String>,\n    quantity: i32,\n    status: String, \/\/ "available" | "deleted" | "missing"\n}\n\n\/\/ \u73fe\u5728\u8abf\u9054\u53ef\u80fd\u306a\u90e8\u54c1\u3060\u3051\u3092\u898b\u308b\nasync fn check_order_parts_current_logical(\n    pool: &Pool<MySql>,\n    order_number: &str,\n) -> Result<Vec<PartCheck>, sqlx::Error> {\n    let rows = sqlx::query(\n        r#"\n        SELECT bi.part_id,\n               p.part_number,\n               p.part_name,\n               bi.quantity\n        FROM customer_orders co\n        JOIN bom_items bi ON bi.bom_id = co.bom_id\n        JOIN parts p ON p.id = bi.part_id\n        WHERE co.order_number = ?\n          AND p.deleted_at IS NULL\n        ORDER BY bi.sequence\n        "#,\n    )\n    .bind(order_number)\n    .fetch_all(pool)\n    .await?;\n\n    Ok(rows\n        .into_iter()\n        .map(|row| PartCheck {\n            part_number: row.get("part_number"),\n            part_name: Some(row.get("part_name")),\n            quantity: row.get("quantity"),\n            status: "available".to_string(),\n        })\n        .collect())\n}\n\n\/\/ \u524a\u9664\u6e08\u307f\u3082\u542b\u3081\u3066\u6709\u52b9\u6027\u3092\u5224\u5b9a\uff08\u8ad6\u7406\u524a\u9664\u306a\u306e\u3067\u5c65\u6b74\u306f\u7121\u304f\u3001\u73fe\u884c\u30c6\u30fc\u30d6\u30eb\u306edeleted_at\u3067\u5224\u65ad\uff09\nasync fn check_order_parts_with_deleted_logical(\n    pool: &Pool<MySql>,\n    order_number: &str,\n) -> Result<Vec<PartCheck>, sqlx::Error> {\n    let rows = sqlx::query(\n        r#"\n        SELECT bi.part_id,\n               p.part_number,\n               p.part_name,\n               bi.quantity,\n               p.deleted_at IS NULL AS is_available,\n               p.deleted_at IS NOT NULL AS is_deleted\n        FROM customer_orders co\n        JOIN bom_items bi ON bi.bom_id = co.bom_id\n        LEFT JOIN parts p ON p.id = bi.part_id\n        WHERE co.order_number = ?\n        ORDER BY bi.sequence\n        "#,\n    )\n    .bind(order_number)\n    .fetch_all(pool)\n    .await?;\n\n    Ok(rows\n        .into_iter()\n        .map(|row| {\n            let part_number: Option<String> = row.try_get("part_number").ok();\n            let status = if part_number.is_none() {\n                "missing"\n            } else if row.get::<bool, _>("is_available") {\n                "available"\n            } else if row.get::<bool, _>("is_deleted") {\n                "deleted"\n            } else {\n                "missing"\n            };\n            PartCheck {\n                part_number: part_number.unwrap_or_else(|| "(none)".to_string()),\n                part_name: row.try_get("part_name").ok(),\n                quantity: row.get("quantity"),\n                status: status.to_string(),\n            }\n        })\n        .collect())\n}\n<\/pre><\/div>\n\n\n
\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3<\/strong><\/p>\n\n\n
\nuse sqlx::{mysql::MySqlPoolOptions, MySql, Pool, Row};\n\n#[derive(Debug)]\nstruct PartCheck {\n    part_number: String,\n    part_name: Option<String>,\n    quantity: i32,\n    status: String, \/\/ "available" | "historical" | "missing"\n}\n\n\/\/ \u73fe\u5728\u8abf\u9054\u53ef\u80fd\u306a\u90e8\u54c1\u3060\u3051\u3092\u898b\u308b\uff08parts\u306e\u307f\uff09\nasync fn check_order_parts_current_physical(\n    pool: &Pool<MySql>,\n    order_number: &str,\n) -> Result<Vec<PartCheck>, sqlx::Error> {\n    let rows = sqlx::query(\n        r#"\n        SELECT bi.part_id,\n               p.part_number,\n               p.part_name,\n               bi.quantity\n        FROM customer_orders co\n        JOIN bom_items bi ON bi.bom_id = co.bom_id\n        JOIN parts p ON p.id = bi.part_id\n        WHERE co.order_number = ?\n        ORDER BY bi.sequence\n        "#,\n    )\n    .bind(order_number)\n    .fetch_all(pool)\n    .await?;\n\n    Ok(rows\n        .into_iter()\n        .map(|row| PartCheck {\n            part_number: row.get("part_number"),\n            part_name: Some(row.get("part_name")),\n            quantity: row.get("quantity"),\n            status: "available".to_string(),\n        })\n        .collect())\n}\n\n\/\/ \u5c65\u6b74\u3082\u53c2\u7167\u3057\u3066\u6709\u52b9\u6027\u3092\u5224\u5b9a\uff08\u6700\u65b0\u5c65\u6b74\u3092\u63a1\u7528\uff09\nasync fn check_order_parts_with_history_physical(\n    pool: &Pool<MySql>,\n    order_number: &str,\n) -> Result<Vec<PartCheck>, sqlx::Error> {\n    let rows = sqlx::query(\n        r#"\n        WITH latest_history AS (\n            SELECT ph.*,\n                   ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn\n            FROM parts_history ph\n        )\n        SELECT\n            COALESCE(p.part_number, lh.part_number) AS part_number,\n            COALESCE(p.part_name, lh.part_name)   AS part_name,\n            bi.quantity,\n            CASE\n                WHEN p.id IS NOT NULL THEN 'available'\n                WHEN lh.history_id IS NOT NULL THEN 'historical'\n                ELSE 'missing'\n            END AS status\n        FROM customer_orders co\n        JOIN bom_items bi ON bi.bom_id = co.bom_id\n        LEFT JOIN parts p ON p.id = bi.part_id\n        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1\n        WHERE co.order_number = ?\n        ORDER BY bi.sequence\n        "#,\n    )\n    .bind(order_number)\n    .fetch_all(pool)\n    .await?;\n\n    Ok(rows\n        .into_iter()\n        .map(|row| PartCheck {\n            part_number: row.get("part_number"),\n            part_name: row.try_get("part_name").ok(),\n            quantity: row.get("quantity"),\n            status: row.get("status"),\n        })\n        .collect())\n}\n<\/pre><\/div>\n\n\n
\u898b\u3066\u306e\u901a\u308a\u3001\u904e\u53bb\u306e\u30c7\u30fc\u30bf\u3092\u8abf\u3079\u308b\u5834\u5408\u306f\u3001\u7269\u7406\u524a\u9664\u30d1\u30bf\u30fc\u30f3\u306e\u307b\u3046\u304c SQL \u6587\u304c\u8907\u96d1\u3067\u3059\u3002\u7279\u306b\u5c65\u6b74\u30c6\u30fc\u30d6\u30eb\u3092\u53c2\u7167\u3059\u308b\u5834\u5408\u306b\u306f\u3001\u6700\u65b0\u306e\u5c65\u6b74\u3092\u53d6\u5f97\u3059\u308b\u305f\u3081\u306b\u30a6\u30a3\u30f3\u30c9\u30a6\u95a2\u6570\u3092\u4f7f\u3046\u5fc5\u8981\u304c\u3042\u308a\u3001SQL \u6587\u304c\u5927\u5e45\u306b\u8907\u96d1\u5316\u3057\u307e\u3059\u3002
\u9006\u306b\u8a00\u3048\u3070\u3001\u904e\u53bb\u306e\u30c7\u30fc\u30bf\u3092\u8abf\u3079\u306a\u3044\u9650\u308a\u3001\u7269\u7406\u524a\u9664\u306e\u307b\u3046\u304c\u6709\u5229\u3068\u3044\u3046\u308f\u3051\u3067\u3059\u3002
\u3064\u307e\u308a\u306f\u3001\u8981\u4ef6\u3068\u3057\u3066\u300c\u904e\u53bb\u306e\u30c7\u30fc\u30bf\u3092\u9061\u3063\u3066\u8abf\u3079\u308b\u3053\u3068\u304c\u3042\u308b\u306e\u304b\uff1f\u300d\u3092\u30c1\u30a7\u30c3\u30af\u3057\u3066\u304a\u3051\u3070\u3001\u30c7\u30fc\u30bf\u69cb\u9020\u3092\u6c7a\u3081\u308b\u4e0a\u3067\u8ad6\u7406\u524a\u9664\u306b\u3059\u308b\u304b\u7269\u7406\u524a\u9664\u306b\u3059\u308b\u304b\u3068\u3044\u3046\u5224\u65ad\u6750\u6599\u306b\u306a\u308a\u307e\u3059\u3002
\u86c7\u8db3\u3092\u8a00\u3048\u3070\u3001\u300c\u904e\u53bb\u306e\u30c7\u30fc\u30bf\u3092\u8abf\u3079\u308b\u304b\u5426\u304b\uff1f\u300d\u306e\u6761\u4ef6\u3092\u4ed8\u3051\u305a\u306b\u3001\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u306e\u8a2d\u8a08\u306b\u300c\u8ad6\u7406\u524a\u9664\u304b\u7269\u7406\u524a\u9664\u304b\uff1f\u300d\u3092\u6301\u3061\u8fbc\u3080\u3053\u3068\u81ea\u4f53\u304c\u30a2\u30f3\u30c1\u30d1\u30bf\u30fc\u30f3\u3068\u3044\u3046\u8a33\u3067\u3059\u306d\u3002<\/p>\n\n\n\n
<\/p>\n\n\n\n
\u4f59\u8ac7\u3067\u3059\u304c\u3001\u8907\u6570\u30c6\u30fc\u30d6\u30eb\u3092\u691c\u7d22\u3059\u308b\u3068\u304d\u306b is null \u30c1\u30a7\u30c3\u30af\u304c\u5404\u30c6\u30fc\u30d6\u30eb\u306b\u4ed8\u52a0\u3055\u308c\u3066\u9762\u5012\u304f\u3055\u3044\/\u9593\u9055\u3044\u304c\u7f6e\u304d\u3084\u3059\u3044\u3068\u3044\u3046\u6307\u6458\u304c\u305f\u3073\u305f\u3073\u3042\u308a\u307e\u3059\u304c\u3001\u73fe\u5b9f\u7684\u306b\u306f\u305d\u3046\u306f\u306a\u3089\u306a\u3044\u3067\u3059\u3002<\/p>\n\n\n
\nselect *\nfrom A inner join B on A.id = B.a_id and B.deleted_at is null\n     B inner join C on B.id = C.b_id and C.deleted_at is null\n     C inner join D on C.id = D.c_id and D.deleted_at is null\nwhere A.deleted_at is null;\n<\/pre><\/div>\n\n\n
\u4e00\u898b\u3059\u308b\u3068\u3001A,B,C,D \u306e\u5168\u3066\u306b is null \u306e\u6761\u4ef6\u3092\u4ed8\u3051\u306a\u3044\u3088\u3046\u306a\u611f\u3058\u304c\u3057\u307e\u3059\u304c\u3001<\/p>\n\n\n
\nselect *\nfrom A inner join B on A.id = B.a_id \n     B inner join C on B.id = C.b_id \n     C inner join D on C.id = D.c_id \nwhere A.deleted_at is null;\n<\/pre><\/div>\n\n\n
\u904b\u7528\u4e0a\u306f\u3001A.deleted_at is null \u306e\u6761\u4ef6\u3060\u3051\u3067\u5341\u5206\u3067\u3059\u3002<\/p>\n\n\n\n
\u3053\u308c\u306f SQL \u306e\u7d50\u5408\u306e\u4ed5\u7d44\u307f\u306b\u3088\u308b\u3082\u306e\u3067\u3001A.deleted_at is null \u306e\u6761\u4ef6\u3067 A \u3068\u306f\u95a2\u4fc2\u306a\u3044\u30ec\u30b3\u30fc\u30c9\u3092\u3001B,C,D \u304b\u3089\u62fe\u3063\u3066\u304f\u308b\u3053\u3068\u306f\u306a\u3044\u304b\u3089\u3067\u3059\u3002\u9006\u306b B.deleted_at is not null \u306e\u30ec\u30b3\u30fc\u30c9\u3092\u53c2\u7167\u3057\u3066\u3044\u305f\u3089\u3001\u305d\u308c\u306f\u904b\u7528\u4e0a\u306e\u30df\u30b9\u304b\u3001\u753b\u9762\u8a2d\u8a08\u3067\u306e\u30ac\u30fc\u30c9\u639b\u304b\u3063\u3066\u3044\u306a\u3044\u305f\u3081\u3067\u3059\u3002<\/p>\n\n\n\n
\u3053\u306e\u3042\u305f\u308a\u3082\u8003\u5bdf\u3057\u3066\u3044\u304f\u3068\u3001\u5b9f\u306f\u8ad6\u7406\u524a\u9664\u3092\u3059\u308b\u306b\u3057\u3066\u3082\u3001\u30e1\u30a4\u30f3\u3068\u306a\u308b\u30c6\u30fc\u30d6\u30eb\u3060\u3051\u306b\u6ce8\u76ee\u3059\u308c\u3070\u3088\u3044\u3068\u3044\u3046\u7d50\u8ad6\u3067\u3059\u3002<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u524d\u56de\u306e \u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u7d9a\u304d\uff08\u5de5\u5834\u306e\u90e8\u54c1\u53d7\u6ce8\u30b7\u30b9\u30c6\u30e0\u306e\u4f8b\uff09https:\/\/www.moonmile.net\/blog\/archives\/11888 \u3067\u306f\u3001\u8ad6\u7406\u524a\u9664\u3068\u7269\u7406\u524a\u9664\u306e\u9055\u3044\u3067\u3042\u307e\u308a\u5dee\u304c\u51fa\u3066\u6765\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u3082\u3046\u5c11 … \u7d9a\u304d\u3092\u8aad\u3080 →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3,39],"tags":[],"class_list":["post-11909","post","type-post","status-publish","format-standard","hentry","category-dev","category-39"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/posts\/11909","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/comments?post=11909"}],"version-history":[{"count":4,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/posts\/11909\/revisions"}],"predecessor-version":[{"id":11913,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/posts\/11909\/revisions\/11913"}],"wp:attachment":[{"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/media?parent=11909"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/categories?post=11909"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.moonmile.net\/blog\/wp-json\/wp\/v2\/tags?post=11909"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}