Блог

Прежде всего, это длинный пост. Спасибо, что нашли время внимательно прочитать это! 🙂

У меня есть три таблицы следующим образом (извините за длинный пост, но лучше прояснить …)

— Структура таблицы для table itemdata

 CREATE TABLE IF NOT EXISTS `itemdata` (
  `itemID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand`  enum('dnc','fbiz','jbs','wspirit','aspirit','grace','legend','stencil','gfl','bocini','beseen','ljapp','lwreid') NOT NULL,
  `category` enum('shirts','headwear','winter','sports','bags','misc') NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(20) NOT NULL,
  `colourway` varchar(255) NOT NULL DEFAULT 'Black',
  `gender` enum('mens','ladies','kids','unisex') NOT NULL,
  `sizerange` varchar(255) NOT NULL DEFAULT 'S|M|L|2XL|3XL|4XL|5XL',
  `discontinued` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemID`),
  KEY `name` (`name`,`code`,`gender`),
  KEY `brand` (`brand`),
  KEY `category` (`category`),
  KEY `discontinued` (`discontinued`),
  KEY `colourway` (`colourway`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=313 ;
  

— Пример данных для таблицы itemdata

 INSERT INTO `itemdata` (`itemID`, `brand`, `category`, `name`, `code`, `colourway`, `gender`, `sizerange`, `discontinued`) VALUES (1, 'jbs', 'shirts', 'Fitted Tee', '1LHT1', 'Blue', 'ladies', '8|10|12|14|16|18|20', 0);
  

— Структура таблицы для table orderitems

 CREATE TABLE IF NOT EXISTS `orderitems` (
  `orderID` int(10) unsigned NOT NULL,
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  KEY `orderid` (`orderID`,`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

— Пример данных для таблицы orderitems

 INSERT INTO `orderitems` (`orderID`, `itemID`, `size`, `qty`) VALUES (1, 123, 'S', 1);
  

— Структура таблицы для table stockitems

 CREATE TABLE IF NOT EXISTS `stockitems` (
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(11) NOT NULL,
  KEY `size` (`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

— Пример данных для таблицы stockitems

 INSERT INTO `stockitems` (`itemID`, `size`, `qty`) VALUES (124, 'S', 5);
  

Две таблицы items: stockitems и orderitems
И одну таблицу list: itemdata

Теперь, что я пытаюсь сделать, это запустить единый запрос, который просмотрит обе таблицы товаров ( stockitems amp; orderitems и вернет информацию из itemdata плюс общее количество как на складе, так и в заказе для каждого идентификатора товара, цветовой гаммы и размера.

РЕДАКТИРОВАТЬ: Я забыл добавить, что две таблицы items организованы немного по-разному. В stockitems таблице никогда не будет повторяющейся строки, где, поскольку в orderitems может быть много одинаковых строк, все эти «повторяющиеся» строки будут точно такими же, за исключением qty поля, которое может отличаться, а может и не отличаться. Мне нужно получить СУММУ qty поля для orderitems таблицы, сгруппированную по размеру и ItemId.

Поэтому в идеале я хотел бы:
itemID , brand , category , name , code , colourway , gender , discontinued , size , qty_stock , qty_orders

Я экспериментировал с различными запросами чуть более 2 часов и пытался найти статью о том, как выполнить то, что я хотел бы сделать, с небольшим успехом.

Единственное, что мне удалось, это получить требуемые данные, но это вернуло только те данные, которые существовали в обоих orderitems И stockitems , но мне нужно, чтобы они возвращали данные, даже если товар есть только на складе или только заказан.

Вот запрос для этого:

 SELECT id.itemID, id.category, id.brand, id.code, id.gender, id.name, id.colourway, 
  id.discontinued, stk.size, stk.qty, ord.size, ord.qty
FROM itemdata id 
  JOIN stockitems stk ON (stk.itemID = id.itemID)
  JOIN orderitems ord ON (ord.itemID = id.itemID)
WHERE stk.size = ord.size
  

Есть идеи?

Отвечая на то, что вы действительно просили, то есть как

 return the information from itemdata plus the total quantity 
both in stock and ordered for each itemID, colourway amp; size
  

Прежде всего, вы не храните никакой информации о цвете ни в orderinfo, ни в stockinfo table, поэтому нет способа получить количество в зависимости от цвета. Итак, давайте проигнорируем цвет.

Чтобы получить кол-во как из orderinfo, так и из stockinfo на основе ItemId и size, не будучи уверенным, что в любой из этих таблиц есть строка для данного id и размера, вам нужно ПОЛНОЕ ВНЕШНЕЕ соединение, которое не поддерживается в mysql, но вы можете достичь того же результата, используя два зеркальных ЛЕВЫХ соединения и ОБЪЕДИНЕНИЕ. Для таблицы orderinfo вам нужно СГРУППИРОВАТЬ ПО идентификатору элемента и размеру, чтобы суммировать количество внутри каждой группы. После объединения обеих таблиц вы можете внутренне ОБЪЕДИНИТЬ результат с таблицей itemdata в ItemId, чтобы получить информацию для каждого элемента. На самом деле вам не нужно касаться sizerange, так как нам не нужно проверять в этом запросе, соответствуют ли размеры возможному sizerange. Таким образом, окончательный запрос должен выглядеть следующим образом:

 SELECT oisi.size,oisi.oqty,oisi.sqty,id.* FROM
itemdata id
JOIN   ( SELECT oi.itemID,oi.size,oi.oqty,COALESCE(si.qty,0) as sqty
         FROM ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         LEFT JOIN stockitems si 
         ON si.itemID=oi.itemID AND si.size=oi.size
         UNION 
         SELECT si.itemID,si.size,COALESCE(oi.oqty,0) as oqty,si.qty as sqty 
         FROM stockitems si
         LEFT JOIN ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         ON si.itemID=oi.itemID AND si.size=oi.size ) oisi
ON id.itemID=oisi.itemID
  

Вы можете ограничить результирующий набор любой комбинацией категории, бренда, пола и т.д., Добавив WHERE в конце, и отсортировать его по любому столбцу, который вы хотите, используя ORDER BY.

Я также предлагаю вам сделать:

 ALTER TABLE orderitems ADD INDEX item_size( itemID, size ) ;
  

поскольку мы используем эти столбцы для ГРУППИРОВКИ ПО.

Есть две вещи, которые приводят к исчезновению строк, если они отсутствуют в обеих таблицах.

Оба stockitems и orderitems объединяются с помощью ВНУТРЕННЕГО объединения (к которому присоединяются псевдонимы). У вас также есть условие для stk.size = ord.size , которое никогда не будет приниматься за TRUE для элемента, который не существует ни в одной из таблиц.

Можем ли мы предположить, что OrderID в orderitems и (ItemId, size) в stockitems являются ПЕРВИЧНЫМИ ключами?

Если это так, вы могли бы получить все записи с:

ВЫБЕРИТЕ id.ItemId, id.category, id.brand, id.code, id.gender, id.name, id.colorway, 
 id.прекращено, stk.size, stk.qty, ord.size, ord.qty
ИЗ идентификатора itemdata 
 ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ stockitems stk ВКЛЮЧЕНО (stk.ItemId = id.ItemId)
 ОСТАЛОСЬ ВКЛЮЧИТЬ элементы ВНЕШНЕГО СОЕДИНЕНИЯ orderitems (ord.ItemId = id.ItemId)

Это даст нам довольно хороший список. Однако условие размера отсутствует. Чтобы исправить это, мы должны определить, что должно произойти, если строка существует с любой стороны, но не с другой из stockitems / orderitems.

Мы не можем использовать равное сравнение, потому что, если оно отсутствует, столбец будет заполнен NULL, а по определению, NULL ничему не равно.

Здесь этот дизайн немного не подходит для запросов такого типа, вероятно, было бы проще, если бы вы добавили столбец sizerange в качестве дополнительной таблицы:

СОЗДАЙТЕ ТАБЛИЦУ sizeranges ( 
 размер CHAR(10) НЕ равен NULL, -- или ENUM, для большей согласованности 
 ItemId INT NOT NULL, 
 ПЕРВИЧНЫЙ КЛЮЧ (размер, ItemId) 
);

Тогда мы не зависим от того, существует ли элемент в orderitems или stockitems, чтобы мы могли легко анализировать возможные размеры элемента. Это дало бы нам:

ВЫБЕРИТЕ id.ItemId, id.category, id.brand, id.code, id.gender, id.name , id.colorway, 
 идентификатор.снят с производства, старший размер, IFNULL (stk.кол-во, 0), IFNULL (ord.кол-во, 0)
ИЗ идентификатора itemdata 
 ВНУТРЕННИЙ размер соединения изменяет значение sr НА (sr.ItemId = id.ItemId)
 ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ stockitems stk ON (stk.ItemId = id.ItemId) И (stk.size = sr.size)
 ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ orderitems ord ВКЛЮЧЕНО (ord.ItemId = id.ItemId) И (ord.size = sr.size)

Надеюсь, это поможет,