本文介绍了如何以这种方式获取数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有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


这篇关于如何以这种方式获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 11:41
查看更多