mysql - 从 mysql 中的主列表制作库存列表

Question

关闭。这个问题需要多问 focused 。目前不接受答案。

---

想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post .

已关闭 5 年前。

Improve this question

我在不同地点有库存，并且希望每个地点都有单独的库存列表。每个位置的库存应该包含相同的东西，所以我想使用一个“主列表”来存储每个项目的名称和标准数量。然后每个位置应该有每个项目的实际数量。

我可以复制每个位置的整个列表，但我希望能够更新主列表。我尝试在网络上搜索解决方案，但找不到我要查找的内容，或者我不知道要搜索什么。有什么想法可以解决这个问题吗？

谢谢

Answer 1

您需要这样的东西(仅包含必填字段的简化版本):

数据库结构:

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(50) NOT NULL,
  `required_quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `items` (`id`, `item_name`, `required_quantity`) VALUES
(1, 'Item 1', 10),
(2, 'Item 2', 20);

-- --------------------------------------------------------


CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `locations`
--

INSERT INTO `locations` (`id`, `location_name`) VALUES
(1, 'Location 1'),
(2, 'Location 2');

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `locations_items` (
  `location_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `actual_quantity` int(11) NOT NULL,
  UNIQUE KEY `location_id` (`location_id`,`item_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `locations_items` (`location_id`, `item_id`, `actual_quantity`) VALUES
(1, 1, 5),
(2, 2, 7);


ALTER TABLE `locations_items`
  ADD CONSTRAINT `locations_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `locations_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON UPDATE CASCADE;

查询以获取每个位置和每个项目的所需数量(基本数量)和每个位置的实际数量:

SELECT 

q.location_id,
q.location_name,
q.item_id,
q.item_name,
q.required_quantity,
IFNULL(li.actual_quantity, 0) AS actual_quantity

FROM
(SELECT
    l.id AS location_id,
    l.location_name,
    i.id AS item_id,
    i.item_name,
    i.required_quantity
FROM
    locations AS l
CROSS JOIN items AS i
) AS q

LEFT JOIN locations_items AS li ON q.location_id = li.location_id AND q.item_id = li.item_id

SQL Fiddle Demo

表 items 中有常用元素列表，您可以在其中输入所有地点所需的标准数量。如果您需要更新它，请在同一个表中进行更新，它将立即应用于所有位置。

在表locations_items中插入/更新实际位置，该表对于每个位置和项目都是唯一的。您可以在那里记录每个位置的实际数量。

通过查询，您可以获取所有位置的所有所需元素/实际数量。您当然可以使用附加条件对其进行微调以获得特定位置或项目。