加入4个表,如何简化这个? [英] JOIN 4 tables, how to simplify this?

查看:109
本文介绍了加入4个表,如何简化这个?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,我简化了表格,尽可能简单易懂,我需要计算所有库存余额。 4个表格如下:

 产品 
prtID prtQty
1 1
2 4
3 4

stockInOut (I = In,O = Out)
IOID prtType prtID IOQty IODate
1 I 1 6 2013-08-07
2 O 2 1 2013-08-08
3 O 1 1 2013-08-08

卖出
sellID prtID sellQty sellDate
1 1 2 2013-08-05
2 2 2 2013-08-10

拒绝
rejID prtID rejQty rejDate
1 1 1 2013-08-09
2 2 1 2013-08-10





以下是计算结果:

 Balance = prtQty + IOQty(prtType = I) -  IOQty(prtType = O) -  sellQty  -  rejID 





prtID的示例余额= 1

余额= 1 + 6  -  1  -  2  -  1 





我只为2桌做过,而且已经非常复杂了,我希望有一个简单的方法可以做到这一点:)

这就是我到目前为止所做的:

 SELECT prtID,SUM(IOQty)+(prtQty)AS余额
FROM产品P
LEFT JOIN stockInOut IO ON P.prtID = IO.prtID
WHERE prtType ='I'和DATE(IODate)BETWEEN'2013-08-01'''2013-08-31'
GROUP BY prtID
UNION ALL
SELECT prtID,prtQty FROM product
WHERE prtID NOT IN
(SELECT prtID FROM stockInOut WHERE prtType ='I'和DATE(IODate)BETWEEN'2013-08-01 'AND'2013-08-31'DER BY BY prt_ID





预期输出:

<前lang = HTML> prtID余额
1 3
2 0
3 4







*第一个字段(ID)是autoNumber。

*日期格式:yyyy-MM-dd



任何建议?

解决方案

我是否简化了你的qu ERY?我不确定......;)



  DECLARE  < span class =code-sdkkeyword> @ product   TABLE (prtID  INT   IDENTITY  1  1 ),prtQty  INT 
INSERT INTO @ product (prtQty)
SELECT 1
UNION ALL SELECT < span class =code-digit> 4
UNION ALL SELECT 4

DECLARE @ stockInOut (IOID INT IDENTITY 1 1 ),prtType VARCHAR 2 ),prtID INT ,IOQty INT ,IODate DATETIME - (I = In,O = Out)
INSERT INTO @ stockInOut (prtType,prtID,IOQty,IODate)
SELECT ' 1 6 ' 2013-08-07'
UNION ALL SELECT ' O' 2 1 ' 2013-08-08'
UNION ALL SELECT ' O' 1 1 ' 2013-08-08'

DECLARE @ sell TABLE (sellID INT IDENTITY 1 1 ),prtID INT , sellQty INT ,sellDate DATETIME
INSERT INTO @ sell (prtID,sellQty,sellDate)
SELECT 1 2 ' 2013-08-05'
UNION ALL SELECT 2 2 ' 2013-08-10'

DECLARE @ reject TABLE (rejID < span class =code-keyword> INT IDENTITY 1 1 ),prtID INT ,rejQty INT ,sellDate DATETIME
INSERT INTO @ reject (prtID,rejQty,sellDate)
SELECT 1 1 ' 2013-08- 09'
UNION 所有 SELECT 2 1 ' 2013-08-10'


- Balance = prtQty + IOQty(prtType = I) - IOQty(prtType = O) - sellQty - rejQty
- - 最后一次查询
SELECT prtID,SUM(余额) AS 余额
FROM
- 第二个查询
SELECT prtID,MAX(prtQty)+ MAX(IQty) - MAX( COALESCE (OQty, 0 )) - MAX( COALESCE (sellQty, 0 )) - MAX( COALESCE (rejQty, 0 )) AS 余额
FROM
- 第一个查询
SELECT P.prtID, P.prtQty, CASE WHEN SIO.prtType = ' 我' 那么 SIO.IOQty ELSE 0 END AS IQty,
CASE WHEN SIO.prtType = ' O' 那么 SIO.IOQty ELSE 0 END AS OQty,S.sellQty,R.rejQty,SIO.IODate
FROM @ product < span class =code-keyword> AS P
LEFT JOIN < span class =code-sdkkeyword> @ stockInOut AS SIO ON P.prtID = SIO.prtID
LEFT JOIN @ sell AS S ON S.prtID = SIO.prtID
LEFT JOIN @ reject AS R ON R.prtID = SIO.prtID
AS T
WHERE IODate BETWEEN ' 2013-08-01' AND ' 2013-08-31' IODate IS NULL
GROUP BY prtID
AS D
GROUP BY prtID
ORDER BY prtID





结果:

 prtID余额
1 3
2 0
3 4


我声称使用正确的表格结构可以让它变得更容易。



产品
prtID
1
2
3


stockInOut
IOID prtType prtID IOQty IODate
1初始1 1 2013-01-01
2初始2 4 2013-01-01
3初始3 4 2013-01 -01
4卖出1 -2 2013-08-05
5在1 6 6 2013-08-07
6出2 -1 2013-08-08
7出1 -1 2013-08-08
8拒绝1 -1 2013-08-09
9卖出2 -2 2013-08-10
10拒绝2 -1 2013-08-10



现在您可以通过此查询获得余额:

选择PrtID,总和(IOQty)余额
来自StockInOut
分组由PrtID
按PrtID排序


以下解决方案适用于上述查询中提供的数据。



 选择 P.prtID 
,ISNULL(P.prtQty, 0 )+ ISNULL (IOS.IOQty, 0 ) - ISNULL(IOO.IOQty, 0 ) - ISNULL(S.sellQty , 0 ) - ISNULL(R.rejQty, 0 ' 余额'

- ,ISNULL(P.prtQty,0)prtQty,ISNULL(IOS.IOQty,0)IOS_IOQty
- ,ISNULL(IOO.IOQty,0)IOO_IOQty,ISNULL(S.sellQty,0)sellQty,ISNULL(R.rejQty) ,0)rejQty
来自 T_Product P
加入 T_stockInOut IOS ON P.prtID = IOS.prtID AND IOS.prtType = ' 我'
加入 T_stockInOut IOO ON P.prtID = IOO.prtID AND IOO.prtType = ' O'
< span class =code-keyword> Left Join T_Sell S ON P.prtID = S.prtID
加入 T_Re ject R ON P.prtID = R.prtID





结果是如下所示



 prtID余额
1 3
2 0
3 4


Hi everyone, i simplified the table as simple as possible to easy understanding, and i need to calculate the all stock balance. 4 tables as below:

product
   prtID   prtQty
     1        1
     2        4
     3        4

stockInOut (I=In, O=Out)
  IOID  prtType  prtID    IOQty      IODate
   1       I      1        6       2013-08-07
   2       O      2        1       2013-08-08
   3       O      1        1       2013-08-08

sell
  sellID   prtID    sellQty      sellDate
    1        1         2        2013-08-05
    2        2         2        2013-08-10

reject
  rejID    prtID    rejQty       rejDate
    1        1        1        2013-08-09
    2        2        1        2013-08-10



Here is the calculation:

Balance = prtQty + IOQty(prtType=I) - IOQty(prtType=O) - sellQty - rejID



Example balance for prtID = 1

Balance = 1 + 6 - 1 - 2 - 1



I only done for 2 table and it's already very complicated, i wish there is a simple way of doing this :)
this is what i have done so far:

SELECT prtID, SUM(IOQty)+(prtQty) AS balance
FROM product P
LEFT JOIN stockInOut IO ON P.prtID= IO.prtID
WHERE prtType = 'I' AND DATE(IODate) BETWEEN '2013-08-01' AND '2013-08-31'
GROUP BY prtID
UNION ALL 
SELECT prtID, prtQty FROM product 
WHERE prtID NOT IN 
(SELECT prtID FROM stockInOut WHERE prtType = 'I' AND DATE(IODate) BETWEEN '2013-08-01' AND '2013-08-31' ORDER BY prt_ID



Expected Output:

prtID   balance
  1       3
  2       0
  3       4




* first field(ID) is the autoNumber.
* Date Format: yyyy-MM-dd

any advice?

解决方案

Do i simplified your query? I'm not sure... ;)

DECLARE @product TABLE (prtID INT IDENTITY(1,1), prtQty INT)
INSERT INTO @product (prtQty)
SELECT 1
UNION ALL SELECT 4
UNION ALL SELECT 4
 
DECLARE @stockInOut TABLE (IOID INT IDENTITY(1,1), prtType VARCHAR(2), prtID INT, IOQty INT, IODate DATETIME) --(I=In, O=Out)
INSERT INTO @stockInOut (prtType, prtID, IOQty, IODate)
SELECT 'I', 1, 6, '2013-08-07'
UNION ALL SELECT 'O', 2, 1, '2013-08-08'
UNION ALL SELECT 'O', 1, 1, '2013-08-08'
 
DECLARE @sell TABLE (sellID INT IDENTITY(1,1), prtID INT, sellQty INT, sellDate DATETIME)
INSERT INTO @sell (prtID, sellQty, sellDate)
SELECT 1, 2, '2013-08-05'
UNION ALL SELECT 2, 2, '2013-08-10'
 
DECLARE @reject TABLE (rejID INT IDENTITY(1,1), prtID INT, rejQty INT, sellDate DATETIME)
INSERT INTO @reject (prtID, rejQty, sellDate)
SELECT 1, 1, '2013-08-09'
UNION ALL SELECT 2, 1, '2013-08-10'


--Balance = prtQty + IOQty(prtType=I) - IOQty(prtType=O) - sellQty - rejQty
--last query
SELECT prtID, SUM(Balance) AS Balance
FROM (
        --second query
	SELECT prtID, MAX(prtQty) + MAX(IQty) - MAX(COALESCE(OQty,0)) - MAX(COALESCE(sellQty,0)) - MAX(COALESCE(rejQty,0)) AS Balance
	FROM (
		--first query
		SELECT P.prtID, P.prtQty, CASE WHEN SIO.prtType = 'I' THEN SIO.IOQty ELSE 0 END AS IQty,
				CASE WHEN SIO.prtType = 'O' THEN SIO.IOQty ELSE 0 END AS OQty, S.sellQty, R.rejQty, SIO.IODate
		FROM @product AS P 
			LEFT JOIN @stockInOut AS SIO ON P.prtID = SIO.prtID
			LEFT JOIN @sell AS S ON S.prtID = SIO.prtID
			LEFT JOIN @reject AS R ON R.prtID = SIO.prtID
		) AS T
		WHERE IODate BETWEEN '2013-08-01' AND '2013-08-31'	OR IODate IS NULL
		GROUP BY prtID
) AS D
GROUP BY prtID
ORDER BY prtID



Result:

prtID   Balance
1	3
2	0
3	4


I would claim that you could make it a lot easier with the correct table structure.

product
   prtID   
     1
     2
     3

 
stockInOut 
  IOID  prtType  prtID    IOQty   IODate
  1     Initial  1         1      2013-01-01
  2     Initial  2         4      2013-01-01
  3     Initial  3         4      2013-01-01
  4     Sell     1        -2      2013-08-05
  5     In       1         6      2013-08-07
  6     Out      2        -1      2013-08-08
  7     Out      1        -1      2013-08-08
  8     Reject   1        -1      2013-08-09
  9     Sell     2        -2      2013-08-10
 10     Reject   2        -1      2013-08-10


Now you get the balance by this query:

Select  PrtID,Sum(IOQty) Balance
From StockInOut
Group by PrtID
Order by PrtID


The below solution will hold good for data provided as in the mentioned query.

Select P.prtID
,ISNULL(P.prtQty,0) + ISNULL(IOS.IOQty,0) - ISNULL(IOO.IOQty,0) - ISNULL(S.sellQty,0) - ISNULL(R.rejQty,0) 'Balance'

--,ISNULL(P.prtQty,0) prtQty , ISNULL(IOS.IOQty,0)IOS_IOQty
--,ISNULL(IOO.IOQty,0) IOO_IOQty, ISNULL(S.sellQty,0) sellQty, ISNULL(R.rejQty,0)rejQty
from T_Product P
Left Join T_stockInOut IOS ON P.prtID = IOS.prtID AND IOS.prtType = 'I'
Left Join T_stockInOut IOO ON P.prtID = IOO.prtID AND IOO.prtType =  'O'
Left Join T_Sell S ON P.prtID = S.prtID
Left Join T_Reject R ON P.prtID = R.prtID



The result is as below

prtID   Balance
1   3
2   0
3   4


这篇关于加入4个表,如何简化这个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆