如何以这种方式获取数据 [英] How to get Data in this way

问题描述

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

推荐答案

我不确定您的结果是否正确。但是，你可以这样开始：

I am not sure if your results are correct. However, you can start something like this:

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

尝试这些实时示例 http://www.sqlfiddle.com/#!3/c3e84/2 [ ^ ]

Try among these, live examples http://www.sqlfiddle.com/#!3/c3e84/2[^]

/*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

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