主页 / user-214490

Andy Wong's questions

Martin Hope
Andy Wong
Asked: 2020-08-24 00:46:03 +0800 CST
  • 0

我有 4 个相互关联的表。

表位置：

CREATE TABLE `location` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Dallas'),
(2, 'Boston'),
(3, 'Houston');

表项：

CREATE TABLE `item` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item` (`id`, `brand`) VALUES
(1, 'Nissan Almera M/T 2009-2015'),
(2, 'Toyota Corolla A/T 2005-2012'),
(3, 'Nissan Terra A/T 2010-2017'),
(4, 'Suzuki Esteem M/T 1980-1990'),
(5, 'Toyota Fortuner A/T 2014-2020');

表 item_in：

CREATE TABLE `item_in` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_in` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 1, 1000),
(2, 1, 2, 500),
(3, 2, 2, 200),
(4, 2, 2, 300),
(5, 3, 3, 300),
(6, 1, 3, 800),
(7, 3, 5, 300),
(8, 3, 4, 400);

表 item_out：

CREATE TABLE `item_out` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_out` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 2, 20),
(2, 1, 1, 25),
(3, 2, 2, 25),
(4, 3, 3, 25),
(5, 3, 5, 10),
(6, 3, 4, 15),
(7, 1, 1, 200),
(8, 2, 2, 50);

使用动态 SQL，我能够根据它们的位置和项目（item_in 数量减去 item_out 数量）获取每个项目的单独剩余数量，并将位置名称作为列。（见下面的代码）：

SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;

SELECT GROUP_CONCAT( DISTINCT
          CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
          INTO @sql1
          FROM item_in
          JOIN location on location.id = item_in.location_id;
        
SELECT GROUP_CONCAT( DISTINCT
          CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
          INTO @sql2
          FROM item_out
          JOIN location on location.id = item_out.location_id;
          
SET @sql = CONCAT('SELECT item.brand AS Item, IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston FROM item LEFT JOIN (SELECT item_in.item_id, ', @sql1, ' FROM item_in
                    GROUP BY item_in.item_id) AS item_in ON item.id = item_in.item_id LEFT JOIN (SELECT item_out.item_id, ', @sql2, ' FROM item_out
                    GROUP BY item_out.item_id) AS item_out ON item.id = item_out.item_id');
                    
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果：

Item                         | Dallas | Boston | Houston
Nissan Almera M/T 2009-2015        775        0         0                           
Toyota Corolla A/T 2005-2012       480      425         0
Nissan Terra A/T 2010-2017         800        0       275
Suzuki Esteem M/T 1980-1990          0        0       385
Toyota Fortuner A/T 2014-2020        0        0       290

我的问题是，我该如何更改代码以便动态显示位置名称列，而不是在查询中手动对其进行硬编码，因为用户可以随时添加新位置？如果有人可以查看我的代码，我将非常感谢您的帮助。我遇到麻烦的唯一部分是如何不对这些行进行硬编码并动态执行它们：

IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston
mysql dynamic-sql