如何解决问题？ [英] How to solve the problem?

问题描述

With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[date] >= T2.[date]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

< b>我尝试过：

What I have tried:

<pre>WITH CTE AS
(
    SELECT FETCHNEXT.*
    , RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID)
    FROM FETCHNEXT 
)
	SELECT * 
		FROM CTE
		WHERE RN = 1
	UNION ALL
	
	SELECT * 
		FROM CTE
		WHERE RN = 2
	UNION ALL
	
	SELECT * 
		FROM CTE
		WHERE RN = 3

推荐答案

您似乎正在回收每个组（ID）中的记录，以确保所有ID具有相同数量的记录。首先，您需要知道您需要多少个：

You appear to be recycling the records from each group (ID) to ensure that all IDs have the same number of records. So firstly you need to know how many of each you are going to need:

declare @maxrows int = (SELECT MAX(C) FROM (SELECT ID, COUNT(*) AS C FROM Demo GROUP BY ID) AS X)

。使用临时文件生成额外行，例如（可能有更好的方法）

. Use a temporary file to generate "extra" rows e.g. (there are probably better ways to do this)

select * INTO #temp from Demo 
declare @continue bit = 1
WHILE @continue = 1
BEGIN
	IF (SELECT MAX(C) FROM (SELECT ID, COUNT(*) AS C FROM #temp GROUP BY ID) AS X WHERE C < @maxrows) < @maxrows
		INSERT INTO #temp select * from #Demo
	ELSE
		SET @continue = 0
END

现在，您可以查询临时表，在每个ID组中分配行号并获得效果你想要通过仔细选择ORDER BY ...

Now you can query the temporary table assigning a row number within each ID group and get the effect you want by choosing the ORDER BY carefully...

;with cte AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rn, *
	FROM #temp
)
SELECT ID, NAMES, DATEUSED
FROM cte
WHERE rn <= @maxrows
ORDER BY rn, ID

给出结果

ID      NAMES   DATEUSED
1	A	NULL
2	D	NULL
3	G	NULL
1	B	NULL
2	E	NULL
3	H	NULL
1	C	NULL
2	F	NULL
3	I	NULL
1	A	NULL
2	D	NULL
3	J	NULL
1	B	NULL
2	E	NULL
3	K	NULL

注意事项：注意分号; 在声明CTE时。您需要这样做以避免错误

Point to note: Notice the semi-colon ; when declaring the CTE. You will need that to avoid error

Msg 319, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

您可以将分号放在前一个语句的末尾但是如果在此之后和CTE之前添加任何代码，您将再次收到错误。因此，最佳做法是将分号与CTE紧密对齐 - 就像我在这里一样。

You could put the semicolon at the end of the previous statement but if you add any code after that and before the CTE you will get the error again. So it's best practice to put the semicolon right tight up against the CTE - as I have here.

这篇关于如何解决问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！