如何从不同的表中减去两个列 [英] How to subtract two colums from different tables

问题描述

嘿伙计们。如何根据产品代码从不同的表中减去两列？



这些是我的表格：

 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 NU LL IDENTITY（1,1）PRIMARY KEY，
 order_qty int NOT NULL，
 order_date varchar（100）NOT NULL，
 product_code varchar（50）
）; 

我想在order_qty上减去stocks.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'; 

解决方案

 SELECT（SUM（ts.stocks_qty）-SUM（tbo.order_qty））AS股票
来自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; 
 

这是一个例子，希望它能解雇你！假设订单日期是唯一的。

  DECLARE   @ tblproducts  表（
 product_barcode  varchar （< span class =code-digit> 50 ） PRIMARY   KEY   NOT   NULL ，
 product_name  varchar （ 50 ） NOT   NULL ，
 product_sprice < span class =code-keyword> 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'，' 我的忍者产品1'，'  123'， '  abc'，'  GoGO'， 100   UNION  
  SELEC T  '  ninja2'，' 我的忍者产品2'，'  123'，'  yyy'，'  JO JO'， 200   UNION  
  SELECT  '  ninja3'， ' 我的忍者产品3'，'  222'，'  xxx'，< span class =code-string>'  XX JO'， 111  
 
  DECLARE   @ 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 （< span class =code-digit> 50 ）
）
 
  DECLARE   @ tblstocks   TABLE （
 stocks_id  in t   IDENTITY （ 1 ， 1 ） PRIMARY   KEY   NOT   NULL ，
 stocks_qty  int   NOT  < span class =code-keyword> NULL ，
 stocks_status  varchar （ 50 ） NOT   NULL ，
 stocks_date  varchar （ 100 ） NOT   NULL  ，
 stocks_month  varchar （ 100 ） NOT   NULL ，
 stocks_year  varchar （< span class =code-digit> 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' ，'  2月'， 2018 ，'  1/1/2099'，'   ninja3'  UNION  
  SELECT   50 ，'  STOCK OUT'，'  3/1/201 8'，'  2月'， 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' < span class =code-keyword> 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 

输出：

条形码名称TotalStock TotalOrder AvailableStocks 
 ninja1 My Ninja product 1 130 40 90 
 ninja2 My Ninja product 2 40 30 10 

Hey guys. How do I subtract the two columns from different tables base on their product codes?

These are my tables:

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)
);

I want to subtract the stocks.qty on order_qty. How will I do that? Thanks guys.

What I have tried:

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';

解决方案

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 ;

Here is an example, hopefully it will fired you up! Assuming the order date is unique.

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

Output:

barcode	name	                TotalStock	TotalOrder	AvailableStocks
ninja1	My Ninja product 1         130	            40	           90
ninja2	My Ninja product 2	    40	            30	           10

这篇关于如何从不同的表中减去两个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！