Рейтинг:
8
Bryian Tan
Вот пример, надеюсь, он вас загорелся! Предполагая, что дата заказа уникальна.
DECLARE @tblproducts TABLE (
product_barcode varchar(50) PRIMARY KEY NOT NULL,
product_name varchar(50) NOT NULL,
product_sprice int NOT NULL,
product_type varchar(50) NOT NULL,
product_supplier varchar(50) NOT NULL,
product_unit varchar (50) NOT NULL
)
INSERT INTO @tblproducts
SELECT 'ninja1', 'My Ninja product 1', '123', 'abc','GoGO', 100 UNION
SELECT 'ninja2', 'My Ninja product 2', '123', 'yyy','JO JO', 200 UNION
SELECT 'ninja3', 'My Ninja product 3', '222', 'xxx','XX JO', 111
DECLARE @tblorder TABLE (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
order_qty int NOT NULL,
order_date varchar(100) NOT NULL,
product_code varchar(50)
)
DECLARE @tblstocks TABLE (
stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
stocks_qty int NOT NULL,
stocks_status varchar(50) NOT NULL,
stocks_date varchar(100) NOT NULL,
stocks_month varchar(100) NOT NULL,
stocks_year varchar(100) NOT NULL,
stocks_expiration varchar(100) NOT NULL,
product_barcode varchar(50) NOT NULL
)
INSERT INTO @tblstocks
SELECT 100,'STOCK IN', '1/1/2018','Jan',2018,'1/1/2099','ninja1' UNION
SELECT 40,'STOCK IN', '2/1/2018','Jan',2018,'1/1/2099','ninja2' UNION
SELECT 30,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja1' UNION
SELECT 50,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja3' UNION
SELECT 50,'STOCK OUT', '3/1/2018','Feb',2018,'1/1/2099','ninja1'
INSERT INTO @tblorder
SELECT 5,'1/3/2018','ninja1' UNION
SELECT 35,'2/3/2018','ninja1' UNION
SELECT 15,'2/3/2018','ninja2' UNION
SELECT 15,'3/3/2018','ninja2'
;WITH cteSumStocks AS (
SELECT product_barcode, SUM(stocks_qty) 'TotalStock' FROM @tblstocks WHERE stocks_status='STOCK IN'
GROUP BY product_barcode
)
, cteSumOrders AS (
SELECT product_code, SUM(order_qty) 'TotalOrder' FROM @tblorder
GROUP BY product_code
) SELECT s.product_barcode, p.product_name, s.TotalStock, o.TotalOrder, s.TotalStock - o.TotalOrder 'AvailableStocks'
FROM cteSumStocks s
JOIN cteSumOrders o
ON s.product_barcode = o.product_code
JOIN @tblproducts p
ON s.product_barcode = p.product_barcode
Выход:
barcode name TotalStock TotalOrder AvailableStocks
ninja1 My Ninja product 1 130 40 90
ninja2 My Ninja product 2 40 30 10
John Th
Спасибо тебе, Брайан Тан! Это работает. Да благословит тебя Бог.
John Th
Как насчет того, чтобы я присоединился к tblproducts, чтобы получить название продукта? Каким будет запрос? Я попробовал сделать запрос ниже, но он не работает.
;С суммами в виде (
Выберите ts.product_barcode, tp.product_name, SUM(stocks_qty) 'TotalStock' из tblstocks как ts INNER JOIN tblproducts как tp ON tp.product_barcode = ts.product_barcode, где ts.stocks_status='STOCK IN'
Группа по ts.product_barcode, tp.product_name
)
, суммирует как (
Выберите product_code, SUM(order_qty) 'TotalOrder' из tblorder
Группа по product_code
) Выберите s.product_barcode, С. TotalStock, о'.TotalOrder, С. TotalStock - о. TotalOrder 'AvailableStocks от sumStocks с
Присоединяйтесь к sumOrders o
На S.product_barcode = о.product_code;
Bryian Tan
смотрите обновленное решение.
Bryian Tan
Пожалуйста. Не забудьте отметить это как решение, если это поможет, чтобы мы могли закрыть эту тему.
John Th
Можно ли получить, если TotalOrder равен 0?
John Th
Я имею в виду вот что;
Всего запасов - 130
Общий заказ - 0
AvailableStocks - 130
Можно ли его вернуть? Даже общий порядок равен 0?? Если да, то какой будет запрос? Спасибо!
Bryian Tan
Вы можете использовать левое соединение. смотрите пример ниже.
DECLARE @tblproducts TABLE (
product_barcode varchar(50) PRIMARY KEY NOT NULL,
product_name varchar(50) NOT NULL,
product_sprice int NOT NULL,
product_type varchar(50) NOT NULL,
product_supplier varchar(50) NOT NULL,
product_unit varchar (50) NOT NULL
)
INSERT INTO @tblproducts
SELECT 'ninja1', 'My Ninja product 1', '123', 'abc','GoGO', 100 UNION
SELECT 'ninja2', 'My Ninja product 2', '123', 'yyy','JO JO', 200 UNION
SELECT 'ninja3', 'My Ninja product 3', '222', 'xxx','XX JO', 111
DECLARE @tblorder TABLE (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
order_qty int NOT NULL,
order_date varchar(100) NOT NULL,
product_code varchar(50)
)
DECLARE @tblstocks TABLE (
stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
stocks_qty int NOT NULL,
stocks_status varchar(50) NOT NULL,
stocks_date varchar(100) NOT NULL,
stocks_month varchar(100) NOT NULL,
stocks_year varchar(100) NOT NULL,
stocks_expiration varchar(100) NOT NULL,
product_barcode varchar(50) NOT NULL
)
INSERT INTO @tblstocks
SELECT 100,'STOCK IN', '1/1/2018','Jan',2018,'1/1/2099','ninja1' UNION
SELECT 40,'STOCK IN', '2/1/2018','Jan',2018,'1/1/2099','ninja2' UNION
SELECT 30,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja1' UNION
SELECT 50,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja3' UNION
SELECT 50,'STOCK OUT', '3/1/2018','Feb',2018,'1/1/2099','ninja1'
INSERT INTO @tblorder
-- SELECT 5,'1/3/2018','ninja1' UNION
--SELECT 35,'2/3/2018','ninja1' UNION
--SELECT 15,'2/3/2018','ninja2' UNION
SELECT 15,'3/3/2018','ninja2'
;WITH cteSumStocks AS (
SELECT product_barcode, SUM(stocks_qty) 'TotalStock' FROM @tblstocks WHERE stocks_status='STOCK IN'
GROUP BY product_barcode
)
, cteSumOrders AS (
SELECT product_code, SUM(order_qty) 'TotalOrder' FROM @tblorder
GROUP BY product_code
) SELECT s.product_barcode, p.product_name, s.TotalStock, ISNULL(o.TotalOrder, 0) TotalOrder,
s.TotalStock - ISNULL(o.TotalOrder, 0) 'AvailableStocks'
FROM cteSumStocks s
LEFT JOIN cteSumOrders o
ON s.product_barcode = o.product_code
JOIN @tblproducts p
ON s.product_barcode = p.product_barcode
John Th
Спасибо тебе, Брайан!
John Th
Как насчет этого Брайана? Как я могу получить, если размер равен нулю, а запасы равны 0? Я хочу вернуть его, даже если там нет ни размера, ни запасов.
Я пробовал этот код ниже, но он извлекает только те продукты, которые имеют размер.
Таблицы:
CREATE TABLE tblproducts (
product_code varchar(50) NOT NULL PRIMARY KEY,
product_name varchar(100) NOT NULL,
product_supplier varchar(50) NOT NULL,
product_price int NOT NULL
);<pre>
--Table Structure for Products Stock In
<pre>CREATE TABLE tblproducts_stocksin (
stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
stocksin_size varchar(30) NOT NULL,
stocksin_stocks int NOT NULL,
stocksin_status varchar(50) NOT NULL,
stocksin_Date varchar(100) NOT NULL,
stocksin_Month varchar(100) NOT NULL,
stocksin_Year varchar(100) NOT NULL,
product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) ON DELETE CASCADE
);<pre>
What I have tried:
<pre>;WITH sumStocksIn AS (
SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM tblproducts_stocksin WHERE stocksin_status='STOCK IN'
GROUP BY product_code, stocksin_size
)
, sumStocksOut AS (
SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM tblproducts_stocksout
GROUP BY product_code, stocksout_size
) SELECT s.product_code, p.product_name, p.product_supplier, ISNULL(stocksin_size, '') 'Size', s.TotalStocks - ISNULL(o.TotalOrder, 0) 'Stocks'
FROM sumStocksIn s
LEFT JOIN sumStocksOut o
ON s.product_code = o.product_code
JOIN tblproducts p
ON s.product_code = p.product_code;<pre>
John Th
Пример вывода:
Product Code Name Supplier Size Stocks
P-0001 Ninja Nike 5 10
P-0002 Ninja1 Nike<pre>
Bryian Tan
Как этот возможный размер равен нулю, поскольку это обязательное поле? Вы можете предоставить некоторые примеры данных для каждой таблицы.
John Th
Что же мне делать? Я хочу вот так: https://ibb.co/fK8Bnx
Значение столбца "запасы" в datagridview должно быть минус в tblstocksout.
Я пробовал его, но он извлекает только те продукты, которые имеют запасы и размер.
Пример вывода:
https://ibb.co/ccioZc
Как вы можете видеть, значение столбца "акции" было изменено на 24. Как я могу включить продукты, у которых нет запасов и размера? Спасибо!
John Th
DECLARE @tblproducts TABLE (
product_code varchar(50) NOT NULL PRIMARY KEY,
product_name varchar(100) NOT NULL,
product_supplier varchar(50) NOT NULL,
product_price int NOT NULL
)
INSERT INTO @tblproducts
SELECT 'P-0001', 'LeBron 15 Ashes', 'Nike', 1500 UNION
SELECT 'P-0002', 'KD 10 AS', 'Nike', 1500 UNION
SELECT 'P-0003', 'Harden 2.0 Ignite', 'Adidas' 1800
DECLARE @tblorder TABLE (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
order_qty int NOT NULL,
order_size varchar(30) NOT NULL,
order_status varchar(50) NOT NULL,
order_Date varchar(100) NOT NULL,
product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) NOT NULL
)
DECLARE @tblstocks TABLE (
stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
stocksin_stocks int NOT NULL,
stocksin_size int NOT NULL,
stocksin_status varchar(50) NOT NULL,
stocksin_Date varchar(100) NOT NULL,
stocksin_Month varchar(100) NOT NULL,
stocksin_Year varchar(100) NOT NULL,
product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) ON DELETE CASCADE
)
INSERT INTO @tblstocks
SELECT 20,5,'STOCK IN', '1/1/2018','Jan',2018,'P-0001' UNION
SELECT 5,5,'STOCK IN', '1/3/2018','Jan',2018,'P-0001' UNION
SELECT 15,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001' UNION
SELECT 5,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001'
INSERT INTO @tblorder
-- SELECT 1,5, 'STOCK OUT', '1/3/2018','P-0001'
;WITH sumStocksIn AS (
SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM tblproducts_stocksin WHERE stocksin_status='STOCK IN'
GROUP BY product_code, stocksin_size
) , sumStocksOut AS (
SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM tblproducts_stocksout
GROUP BY product_code, stocksout_size)
SELECT s.product_code, p.product_name, p.product_supplier, s.stocksin_size, s.TotalStocks - ISNULL(o.TotalOrder, 0) 'Stocks'
FROM sumStocksIn s
LEFT JOIN sumStocksOut o
ON s.product_code = o.product_code
JOIN tblproducts p
ON s.product_code = p.product_code;
John Th
Помогите мне, пожалуйста. @BryianTan
Bryian Tan
Затем соединение нужно начать с таблицы tblproducts. Вот вам пример.
DECLARE @tblproducts TABLE (
product_code varchar(50) NOT NULL PRIMARY KEY,
product_name varchar(100) NOT NULL,
product_supplier varchar(50) NOT NULL,
product_price int NOT NULL
)
INSERT INTO @tblproducts
SELECT 'P-0001', 'LeBron 15 Ashes', 'Nike', 1500 UNION
SELECT 'P-0002', 'KD 10 AS', 'Nike', 1500 UNION
SELECT 'P-0003', 'Harden 2.0 Ignite', 'Adidas', 1800
DECLARE @tblorder TABLE (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
stocksout_qty int NOT NULL,
stocksout_size varchar(30) NOT NULL,
order_status varchar(50) NOT NULL,
order_Date varchar(100) NOT NULL,
product_code varchar(50)
)
DECLARE @tblstocks TABLE (
stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
stocksin_stocks int NOT NULL,
stocksin_size int NOT NULL,
stocksin_status varchar(50) NOT NULL,
stocksin_Date varchar(100) NOT NULL,
stocksin_Month varchar(100) NOT NULL,
stocksin_Year varchar(100) NOT NULL,
product_code varchar(50)
)
INSERT INTO @tblstocks
SELECT 20,5,'STOCK IN', '1/1/2018','Jan',2018,'P-0001' UNION
SELECT 5,5,'STOCK IN', '1/3/2018','Jan',2018,'P-0001' UNION
SELECT 15,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001' UNION
SELECT 5,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001'
INSERT INTO @tblorder
SELECT 1,5, 'STOCK OUT', '1/3/2018','P-0001'
;WITH sumStocksIn AS (
SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM @tblstocks WHERE stocksin_status='STOCK IN'
GROUP BY product_code, stocksin_size
) , sumStocksOut AS (
SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM @tblorder
GROUP BY product_code, stocksout_size)
SELECT
p.product_code, p.product_name, p.product_supplier, ISNULL(s.stocksin_size,0) 'Size',
ISNULL(s.TotalStocks,0) - ISNULL(o.TotalOrder, 0) 'Stocks'
FROM @tblproducts p
LEFT JOIN sumStocksOut o ON p.product_code = o.product_code
LEFT JOIN sumStocksIn s ON o.product_code = s.product_code
John Th
Спасибо тебе @BryianTan! :)