加入4个表,如何简化这个? [英] JOIN 4 tables, how to simplify this?
问题描述
大家好,我简化了表格,尽可能简单易懂,我需要计算所有库存余额。 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屋!