Complex select query for SQL Server
I have a temp table in SQL server like below
╔═══════╦═════════╦══════╦═════════╗
║Serial ║ Account ║ Due ║ Balance ║
║1 ║ A1 ║ 1000 ║ 3100 ║
║2 ║ A1 ║ 1000 ║ 3100 ║
║3 ║ A1 ║ 1000 ║ 3100 ║
║4 ║ A1 ║ 1000 ║ 3100 ║
║1 ║ A2 ║ 100 ║ 3100 ║
║2 ║ A2 ║ 100 ║ 3100 ║
║1 ║ B1 ║ 1000 ║ 1100 ║
║2 ║ B1 ║ 1000 ║ 1100 ║
║1 ║ B2 ║ 100 ║ 1100 ║
║2 ║ B2 ║ 100 ║ 1100 ║
╚═══════╩═════════╩══════╩═════════╝
I want to identify the rows which due would be collected. A1 and A2 Due will be collected from 3100 and B1 and B2 due will be collected from 1100.
Firstly I have used cumulative Due as following
╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due ║ Cumulative ║ Balance ║
║1 ║ A1 ║ 1000 ║ 1000 ║ 3100 ║
║2 ║ A1 ║ 1000 ║ 2000 ║ 3100 ║
║3 ║ A1 ║ 1000 ║ 3000 ║ 3100 ║
║4 ║ A1 ║ 1000 ║ 4000 ║ 3100 ║
║1 ║ A2 ║ 100 ║ 100 ║ 3100 ║
║2 ║ A2 ║ 100 ║ 200 ║ 3100 ║
║1 ║ B1 ║ 1000 ║ 1000 ║ 1100 ║
║2 ║ B1 ║ 1000 ║ 2000 ║ 1100 ║
║1 ║ B2 ║ 100 ║ 100 ║ 1100 ║
║2 ║ B2 ║ 100 ║ 200 ║ 1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╝
Now I want to select following rows as output
╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due ║ Cumulative ║ Balance ║
║1 ║ A1 ║ 1000 ║ 1000 ║ 3100 ║
║2 ║ A1 ║ 1000 ║ 2000 ║ 3100 ║
║3 ║ A1 ║ 1000 ║ 3000 ║ 3100 ║
║1 ║ A2 ║ 100 ║ 100 ║ 3100 ║
║1 ║ B1 ║ 1000 ║ 1000 ║ 1100 ║
║1 ║ B2 ║ 100 ║ 100 ║ 1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╩
Here is where I am stuck. How can I select those rows without using cursor or loop. All I want to do this with select statement and window functions. Thanks.
Possible Solution : If the table can be updated as following then the problem would be solved.
╔═══════╔═════════╦══════╦═══════════════════╦
║Serial ║ Account ║ Due ║ Balance Remaining ║
║1 ║ A1 ║ 1000 ║ 3100 ║
║2 ║ A1 ║ 1000 ║ 2100 ║
║3 ║ A1 ║ 1000 ║ 1100 ║
║4 ║ A1 ║ 1000 ║ 100 ║
║1 ║ A2 ║ 100 ║ 100 ║
║2 ║ A2 ║ 100 ║ 0 ║
║1 ║ B1 ║ 1000 ║ 1100 ║
║2 ║ B1 ║ 1000 ║ 100 ║
║1 ║ B2 ║ 100 ║ 100 ║
║2 ║ B2 ║ 100 ║ 0 ║
╚═══════╚═════════╩══════╩═══════════════════╩
The cases Balance Remaining is equal/greater than Due we update it with difference else it will remain as before. Problem is here to update rows by partitioning between A & B.
UPDATE I am providing link with new data set to express my requirement more clearly. new dataset
最后，用更新查询来解决这个问题。
UPDATE A
SET
A.Balance = @Balance
, @PreBalance = @Balance
, @Balance = ( CASE WHEN (@Balance IS NULL OR @AccountType <> A.AccountType)
THEN
CASE WHEN A.Balance - A.Due >= 0
THEN A.Balance
ELSE A.Balance + A.Due
END
ELSE
CASE WHEN @Balance - A.Due >= 0 AND (@Flag = 1 OR @AccountNO <> A.AccountNO)
THEN @Balance
ELSE @Balance + A.Due
END
END) - A.Due
, A.FLAG = @Flag
, @AccountNO = CASE WHEN A.Flag = 0 THEN A.AccountNO ELSE 'NoDueFoundForAcc' END
, @Flag = CASE WHEN @AccountType = A.AccountType
THEN
CASE WHEN @PreBalance = @Balance
THEN 0
ELSE 1
END
ELSE
CASE WHEN A.Balance - A.Due >= 0
THEN 1
ELSE 0
END
END
, @AccountType = A.AccountType
FROM #tempTable A
SELECT * FROM #tempTable A WHERE A.Flag = 1
Very simple
select * from account
where (Balance-(Select sum(ac.Due) from account ac where
ac.SerialNo<=account.SerialNo and ac.Account =account.Account )>0)
Update
there is no relation between A1 and A2 that say that balance 3100 is to be share between A1 and A2 and not with B1.
So you have to specify some where that a1 and a2 are on same group
there is suggested option for you
Add group no column in you table and give same no for A1 and A2, other same no for B1 and B2. Then add Priority column that specifies A1 should deduct first due then if balance left a2 will get chance
then query will be
SELECT *
FROM account
WHERE
( Balance - ( SELECT
SUM(ac.Due)
FROM
account ac
WHERE
( ac.GroupNo = account.GroupNo
AND ( ( ac.Account = account.Account
AND ( ac.SerialNo <= account.SerialNo )
)
OR ac.Prioirty < account.Prioirty
)
)
) > 0 )
为了得到这个输出，你可以使用如下的简单查询：
╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due ║ Cumulative ║ Balance ║
║1 ║ A1 ║ 1000 ║ 1000 ║ 3100 ║
║2 ║ A1 ║ 1000 ║ 2000 ║ 3100 ║
║3 ║ A1 ║ 1000 ║ 3000 ║ 3100 ║
║1 ║ A2 ║ 100 ║ 100 ║ 3100 ║
║1 ║ B1 ║ 1000 ║ 1000 ║ 1100 ║
║1 ║ B2 ║ 100 ║ 100 ║ 1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╩
select Serial,Account,Due,Cumulative,Balance from
(Select *, row_number() over(Partition by Account, order by serial number desc) as r from Temp) t
where t.r>1
上一篇: 使用xor reg，reg优于mov reg，0吗？
下一篇: SQL Server的复杂选择查询