#mysql #sql
#mysql #sql
Вопрос:
Соответствующая часть моей базы данных выглядит следующим образом (MS Visio, я знаю, что я жалкий: D): 
Мне нужно извлечь список, состоящий из всех элементов в категории, а также пакетов. Поэтому я должен использовать UNION . Первая часть a UNION для вашей справки (поскольку она задает формат данных для SELECT во второй части UNION ; обратите внимание, что это ? указывает, куда входит аргумент node-mysql ):
SELECT `ID`, `Name`, `Description`,
`PictureID`, `SellingPrice`,
`Cost`, 0 AS `Bundle`
FROM `Item`
WHERE `CategoryID`=? AND
`ID` IN (
SELECT `ItemID`
FROM `Stock`
WHERE `CityID`=?
AND `IsLimitless`=1 OR `Quantity`>0
)
Поэтому я хочу представить свои пакеты так, как будто они также являются элементами, со всеми одинаковыми полями и т.д.
Моя попытка:
SELECT `ID`, `Name`, `Description`, `PictureID`,
(
SELECT SUM( // Here SQL indicates a syntax problem
SELECT `ItemAmount`*`PriceModifier`*(
SELECT `SellingPrice`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
)
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
)
) AS `SellingPrice`,
(
SELECT SUM(
SELECT `ItemAmount`*(
SELECT `Cost`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
)
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
)
) AS `Cost`,
1 AS `Bundle`
FROM `Bundle`
WHERE `ID` IN (
SELECT `BundleID`
FROM `BundleCategory`
WHERE `CategoryID`=?
)
//No need to check bundles for stock due to business logic
У меня есть слабая идея, что я слишком усложняю это, но, к сожалению, я не могу указать на это пальцем.
Любой совет будет очень приветствоваться и заранее благодарен за ваше время. <3
Пример данных:
Fields of no interest like "Description"/"PictureID"/"SupplierID" will be omitted
for the relevant parts to fit on screen
**Bundle**
ID Name Description PictureID
1 Valentine Pack Blah-blah tasty buy me imgur link in text
**Item**
ID Name SellingPrice Cost CategoryID
1 Movie Ticket 10 2 24
2 Box of Chocolates 5 1 4
3 Teddy Bear 15 3 2
4 Roses 10 4 8
**Stock**
ItemID CityID Quantity IsLimitLess
1 1 25 false
1 2 11 false
2 1 84 false
3 1 33 false
4 1 1 true
4 3 1 true
**BundleItem**
BundleID ItemID ItemAmount PriceModifier
1 1 2 1.25
1 2 1 1
1 3 1 1
1 4 5 0.75
**BundleCategory** (bundle for marketing reasons can appear in different
categories depending on its contents)
BundleID CategoryID
1 4 //Sweets
1 2 //Toys
1 8 //Flowers
Желаемый результат: (Для поиска CityId 1, CategoryID 8, Цветы)
ID Name (Descr/PicID) SellingPrice Cost Bundle
4 Roses 10 4 false
1 Valentine Pack 82.5 28 true
/*2*10*1.25 2*2 <movie
1*1*5 1*1 <chocolate
1*1*15 3*1 <teddy bear
5*0.75*10 5*4 <roses */
Предлагаемые пользователем решения
Согласно предложению @drakin8564, я попытался выполнить
SELECT `ID`, `Name`, `Description`, `PictureID`,
(
SELECT SUM((
SELECT `ItemAmount`*`PriceModifier`*(
SELECT `SellingPrice`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
)
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
))
) AS `SellingPrice`,
(
SELECT SUM((
SELECT `ItemAmount`*(
SELECT `Cost`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
)
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
))
) AS `Cost`,
1 AS `Bundle`
FROM `Bundle`
WHERE `ID` IN (
SELECT `BundleID`
FROM `BundleCategory`
WHERE `CategoryID`=8
)
ВОЗВРАТ
(1242): Subquery returns more than 1 row.
Это происходит, даже когда я пытаюсь SELECT SUM((SELECT ID FROM Item)) . Странно.
Я прокомментировал другие решения о том, насколько хорошо они работают. Я ценю всех вас, ребята, принимающих участие в этом. <3
Комментарии:
1. @GordonLinoff уверен, сделает это сейчас.
2. Лучше добавлять префиксы к именам столбцов, когда у вас несколько таблиц. Нам было бы легче понять, откуда берется каждый столбец. Вы можете использовать однобуквенные псевдонимы, чтобы уменьшить беспорядок, например
...FROM BundleItem b WHERE b.BundleID = ....3. Если я это понимаю, в одном пакете может быть несколько элементов. Вы хотите, чтобы в результатах была только одна запись на пакет?
4. @Vasya Да, по одной записи на пакет.
5. @TheImpaler Я думаю, что исправил самые запутанные моменты. Если требуются дальнейшие изменения — сообщите.
Ответ №1:
Похоже, у вас было несколько проблем с синтаксисом. Ваш код работал с несколькими изменениями. Подробности см. в комментариях к запросу.
http://sqlfiddle.com /#!9/ee0725/16
SELECT `ID`, `Name`, `Description`, `PictureID`,
(SELECT SUM(`ItemAmount`*`PriceModifier`*( -- changed order of SELECT and SUM; removed extra SELECT; fixed Parens
SELECT `SellingPrice`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
))
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`)
AS `SellingPrice`,
(SELECT SUM(`ItemAmount`*( -- changed order of SELECT and SUM; removed extra SELECT; fixed Parens
SELECT `Cost`
FROM `Item`
WHERE `ID`=`BundleItem`.`ItemID`
))
FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`)
AS `Cost`,
1 AS `Bundle`
FROM `Bundle`
WHERE `ID` IN (
SELECT `BundleID`
FROM `BundleCategory`
WHERE `CategoryID`=8
);
Комментарии:
1. Я подтвердил, что это действительно работает! Я отмечу это как принятый ответ из-за того, насколько он близок к моему решению, что просто гарантирует, что я понимаю его лучше всего. Большое вам спасибо за ваше время!
Ответ №2:
Что-то вроде этого должно работать
SELECT tb.`ID`, MAX(tb.`Name`), MAX(tb.`Description`), MAX(tb.`PictureID`),
SUM(`ItemAmount`*`PriceModifier`*`SellingPrice`) AS `SellingPrice`,
SUM(`ItemAmount`*`Cost`) AS `Cost`,
1 AS `Bundle`
FROM `Bundle` tb
JOIN `BundleItem` tbi on tb.ID=tbi.BundleID
JOIN `Item` ti on tbi.ItemID=ti.ID
WHERE tb.`ID` IN (
SELECT `BundleID`
FROM `BundleCategory`
WHERE `CategoryID`=?
)
GROUP BY tb.ID
//No need to check bundles for stock due to business logic
Комментарии:
1. Хорошо, ваше решение работает на первый взгляд, я добавлю больше тестовых данных, чтобы убедиться, что в нем нет неприятных сюрпризов. Ура! Я хотел спросить
MAX(tb.Name), для чего это нужно? Что это дает?2. @TorlanDelta: нам нужно использовать Max из-за Group By (если используется group by, вы можете выбрать только то, что находится в предложении group by, или использовать агрегатную функцию). Поскольку мы группируем записи по идентификатору пакета, все записи в одной группе будут иметь одинаковое имя пакета, и Max вернет это имя пакета.
Ответ №3:
Ваша синтаксическая ошибка связана с тем, что ваш подзапрос не завернут в () . Примеры ниже.
Это не удастся:
SELECT SUM(SELECT 1);
Это будет работать:
SELECT SUM((SELECT 1));
Комментарии:
1. Хотя вы правы, применение этого предложения к моему решению не помогло. Тем не менее, спасибо за ценную информацию, это определенно не очевидно! Хотя ваше решение будет терпеть неудачу
(1242) Subquery returns more than 1 rowвсякий раз, когда SELECT применяется к фактической таблице с более чем 1 результатом, для чего в первую очередь предназначена СУММА: P2. Вы абсолютно правы. Я думаю, я не читал так далеко. Я собирался решить непосредственную проблему, которая заключалась в синтаксической ошибке.
Ответ №4:
Предположение № 1: у всех товаров должно быть достаточно запасов в городе, чтобы пакет был доступен в этом городе. (См. Комментарии к запросу о том, как удалить это бизнес-правило)
В образце данных нет пакетов, которые полностью имеются в наличии в любых городах — чтобы исправить это, я изменил количество для ItemId = 4 в CityId = 1 с «1» на «5». Это создало желаемый результат.
Предположение № 2: запас.Допустимо количество = 0.
Это решение выдает результаты запроса, которые содержат все товары и пакеты для каждого города и категории, где Товар или Пакет есть в наличии. Предложение where внизу фильтрует его по CityId=1 и Category = 8 для исходного запроса.
Примечание: вы можете вставить решение и схему ниже в www.sqlfiddle.com и посмотрите на результаты.
ОБНОВЛЕНИЕ исправлено объединение BundleCategory.
Решение
select * from (
select
Stock.CityID,
Item.CategoryID,
Item.ID,
Item.Name,
Item.Description,
Item.SellingPrice,
Item.Cost,
'false' as Bundle
from Item
inner join Stock on Stock.ItemID = Item.ID
where IFNULL(Stock.Quantity,0) > 0 -- remove this to show out of stock items
union
select
BundleSummary.CityID,
BundleCategory.CategoryID,
Bundle.ID,
Bundle.Name,
Bundle.Description,
BundleSummary.SellingPrice as SellingPrice,
BundleSummary.Cost as Cost,
'true' as Bundle
from Bundle
inner join (
select
BundleItem.BundleID,
City.CityID,
MIN(IF(IFNULL(Stock.Quantity, 0) < BundleItem.ItemAmount, 0, 1)) as InStock,
SUM(Item.SellingPrice * BundleItem.ItemAmount * BundleItem.PriceModifier) as SellingPrice,
SUM(Item.Cost * BundleItem.ItemAmount) as Cost
from BundleItem
inner join Item on Item.ID = BundleItem.ItemID
inner join (select distinct CityID from Stock where CityID IS NOT NULL) as City on 1=1
left join Stock on Stock.ItemID = Item.ID and Stock.CityID = City.CityID
group by BundleItem.BundleID, City.CityID
) as BundleSummary on BundleSummary.BundleID = Bundle.ID
inner join BundleCategory on BundleCategory.BundleID = Bundle.ID
where BundleSummary.InStock = 1 -- remove this to show out of stock bundles
) as qry1
where CityID=1 and CategoryID=8;
Я также сгенерировал скрипт для создания схемы базы данных и заполнения ее образцами данных. Подумал, что это может быть полезно всем, кто использует это решение для расследования своих собственных проблем.
Схема
create table Item (
ID int,
Name varchar(255),
Description varchar(255),
PictureID int,
SellingPrice DECIMAL(12,4),
Cost DECIMAL(12,4),
SupplierID int,
CategoryID int
);
insert into Item values (1, 'Movie Ticket', '', NULL, 10, 2, NULL, 24);
insert into Item values (2, 'Box of Chocolates', '', NULL, 5, 1, NULL, 4);
insert into Item values (3, 'Teddy Bear', '', NULL, 15, 3, NULL, 2);
insert into Item values (4, 'Roses', '', NULL, 10, 4, NULL, 8);
create table Bundle (
ID int,
Name varchar(255),
Description varchar(255),
PictureID int
);
insert into Bundle values (1, 'Valentine Pack', 'Blah-blah tasty buy me', NULL);
create table Stock (
ItemID int,
CityID int,
Quantity int,
IsLimitless bit
);
insert into Stock values (1, 1, 25, false);
insert into Stock values (1, 2, 11, false);
insert into Stock values (2, 1, 84, false);
insert into Stock values (3, 1, 33, false);
insert into Stock values (4, 1, 5, true);
insert into Stock values (4, 3, 1, true);
create table BundleItem (
BundleID int,
ItemID int,
ItemAmount int,
PriceModifier DECIMAL(12,4)
);
insert into BundleItem values (1, 1, 2, 1.25);
insert into BundleItem values (1, 2, 1, 1);
insert into BundleItem values (1, 3, 1, 1);
insert into BundleItem values (1, 4, 5, 0.75);
create table BundleCategory (
BundleID int,
CategoryID int
);
insert into BundleCategory values (1, 4); -- Sweets
insert into BundleCategory values (1, 2); -- Toys
insert into BundleCategory values (1, 8); -- Flowers
Комментарии:
1. Вау! Очень сложное решение, но, насколько показывают мои тесты, оно определенно работает. В настоящее время я заполняю свою базу данных для выполнения более обширных тестов. Показывает мне, насколько я опасаюсь объединений: D Спасибо за ваши усилия, я действительно ценю это! И я обязательно предоставлю схемы для моих будущих вопросов.