John Th Ответов: 2

Как вычесть два столбца из разных таблиц


Привет, ребята. Как вычесть эти два столбца из разных таблиц, основываясь на их кодах продуктов?

Это мои столы:

CREATE TABLE tblproducts (
	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
);


CREATE TABLE tblstocks (
	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 FOREIGN KEY REFERENCES tblproducts(product_barcode)
);


CREATE TABLE tblorder(
	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)
);



Я хочу вычесть акции.кол-во На order_qty. Как я это сделаю? Спасибо, ребята.

Что я уже пробовал:

SELECT SUM(ts.stocks_qty) - (SELECT SUM(tbo.order_qty) FROM tblorder AS tbo INNER JOIN tblstocks AS ts ON ts.product_barcode = tbo.product_code) AS Stocks FROM tblstocks AS ts INNER JOIN tblorder AS tbo ON tbo.product_code = ts.product_barcode WHERE ts.product_barcode = tbo.product_code AND ts.stocks_status = 'STOCK IN';

2 Ответов

Рейтинг:
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

смотрите обновленное решение.

John Th

Спасибо, Брайан.

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! :)

Рейтинг:
0

Santosh kumar Pithani

SELECT (SUM(ts.stocks_qty)-SUM(tbo.order_qty)) AS Stocks
   FROM tblstocks AS ts INNER JOIN tblorder AS tbo
      ON (tbo.product_code = ts.product_barcode)  WHERE ts.stocks_status = 'STOCK IN'
           GROUP BY tbo.product_code ;


John Th

Привет, Сантош. Предположим, что order_qty имеет значение 2 для P-0001(product_code), а stocks_qty имеет значение 35 для P-0001(product_code), поэтому запасы будут равны 33. Как я это сделаю? Я пробовал ваш запрос, но у меня есть 74 акции. Кстати, спасибо, что ответили.

Santosh kumar Pithani

Вы должны сделать группировку по ..подождите, я улучшу решение для вас.

John Th

Хорошо. Я буду ждать этого.

John Th

Спасибо, Сантош. Очень ценю ваше решение.

Santosh kumar Pithani

Добро Пожаловать Джон:)