本文介绍了如何以这种方式获取数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2张桌子
在一张桌子上一个UID有它的价值
在第二张桌子里一个uid有多个值。
i想要这样的数据。
表1
I have 2 tables
in one table one UID has its value
in second table one uid has multiple value.
i want to data like this.
Table 1
UID Bill
U123 5000
U125 4500
表2
Table 2
UID Collection
U123 2000
U123 2500
U125 1250
U123 200
我想要结果
I want result
UID Bill Coll Outstd
U123 5000 2000 0
U123 5000 2500 0
U123 5000 200 300
U125 4500 1250 2250
推荐答案
--Get Collections as Sum
SELECT
#Table1.UId,
SUM(#Table2.Collection) AS Collection
INTO
#Temp
FROM
#Table1 INNER JOIN #Table2 ON
#Table1.UId = #Table2.UId
GROUP BY #Table1.UId
--Get Actual Results
SELECT
#Table1.UId,
#Table1.Bill,
#Table2.Collection,
(#Table1.Bill - #Temp.Collection) AS OutStanding
FROM
#Table1 INNER JOIN #Table2 ON
#Table1.UId = #Table2.UId
INNER JOIN #Temp ON
#Table2.UId = #Temp.UId
ORDER BY
#Table1.UId
DROP TABLE #Temp
/*Soution 1: As wanted*/
WITH [Collection]
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY UID) AS RowId, Coll.*
FROM Table2 AS Coll
),
LastCollection
AS
(
SELECT MAX(RowId)AS RowId, UID AS LastId
FROM [Collection]
GROUP BY UID
)
SELECT
Bill.*,
Coll.[Collection] AS Coll,
CASE
WHEN(LastColl.RowId IS NULL) THEN(SELECT 0)
ELSE(
-- total bill - (sum of previous collections and current row collection)
COALESCE(Bill.Bill, 0) - COALESCE((SELECT SUM(PreColl.Collection)
FROM [Collection] AS PreColl
WHERE PreColl.UID = Coll.UID
AND PreColl.RowId <= Coll.RowId), 0)
)
END AS Outstd
FROM Table1 AS Bill
INNER JOIN [Collection] AS Coll ON Bill.UID = Coll.UID
LEFT JOIN LastCollection AS LastColl ON Coll.RowId = LastColl.RowId;
或更有趣
or little more interesting
/*Soution 2: improved*/
WITH [Collection]
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY UID) AS RowId, Coll.*
FROM Table2 AS Coll
)
SELECT Bill.*, Coll.Collection AS Coll,
-- total bill - (sum of previous collections and current row collection)
COALESCE(Bill.Bill, 0) - COALESCE((SELECT SUM(PreColl.Collection)
FROM [Collection] AS PreColl
WHERE PreColl.UID = Coll.UID
AND PreColl.RowId <= Coll.RowId), 0)AS Outstd
FROM Table1 AS Bill
INNER JOIN [Collection] AS Coll ON Bill.UID = Coll.UID;
但如果
$ b $会更好b
But it would be better if
/*Soution 3: I like it*/
SELECT AllUser.UID,
COALESCE(TtlBill.Bill, 0) AS Bill,
COALESCE(TtlCollection.Coll, 0) AS Coll,
COALESCE(TtlBill.Bill, 0) - COALESCE(TtlCollection.Coll, 0) AS Outstd
--total usser
FROM(
SELECT *
FROM(
SELECT UID FROM Table1
UNION ALL
SELECT UID FROM Table2
) AS Users
GROUP BY UID
) AS AllUser
--total Bill
LEFT JOIN(
SELECT UID, SUM(Bill) AS Bill
FROM Table1
GROUP BY UID
) AS TtlBill
ON AllUser.UID = TtlBill.UID
--total Collection
LEFT JOIN(
SELECT UID, SUM(Collection) AS Coll
FROM Table2
GROUP BY UID
) AS TtlCollection
ON AllUser.UID = TtlCollection.UID;
select *, OutStd --(put ur logic here)
from tbl1
left outer join tbl2 on tbl.UID =UID
这篇关于如何以这种方式获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!