表 T_PaymentSID(不重复) Amount Is_Credit
========================================
1 150.00 0
2 100.00 0
3 100.00 0
4 100.00 1
5 100.00 1在上面的表中,
Is_Credit =0 表示应收项
Is_Credit =1 表示付款数我需要找出所有"还未被付款"的记录, 上面表中,隐含表示: SID=2,3的记录 已经被 SID= 4,5 的记录结帐了. 结帐的记录之间没有一个对应的结帐勾对的标志,所以只能用金额对冲算出在本例中, 我要找出的满足我的条件的记录是只有 SID =1 的如下一条记录.SID Amount Is_Credit
========================================
1 150.00 0
如何用一条 SQL语句 或者 自定义函数/存储过程 实现这个过滤查询?谢谢 各位高手!
Is_Credit =0 表示应收项
Is_Credit =1 表示付款项
declare @t table(SID int,Amount money,Is_Credit bit)
insert into @t
select 1, 150.00, 0 union all
select 2, 100.00, 0 union all
select 3, 100.00, 0 union all
select 4, 100.00, 1 union all
select 5, 100.00, 0 union all
select 6, 100.00, 0--query
select
sid,amount,is_credit
from @t a
where is_credit=0 and
(select count(9) from @t where is_credit=1 and amount=a.amount)<=(select count(9) from @t where is_credit=0 and amount=a.amount and sid<a.sid)--result
/*
sid amount is_credit
--- ------ ---------
1 150.00 0
3 100.00 0
5 100.00 0
6 100.00 0
*/
Declare @TEST Table(SID Int, Amount Money, Is_Credit Bit)
Insert Into @TEST Select 1, 150.00, 0
Union All Select 2, 100.00, 0
Union All Select 3, 100.00, 0
Union All Select 4, 100.00, 1
Union All Select 5, 100.00, 1
--測試
Select * From @TEST A
Where (Select Count(SID) From @TEST Where Amount = A.Amount And SID <= A.SID And Is_Credit = 0)
> (Select Count(SID) From @TEST Where Amount = A.Amount And Is_Credit = 1)
And Is_Credit = 0
--結果
/*
SID Amount Is_Credit
1 150.0000 0
*/
Declare @TEST Table(SID Int, Amount Money, Is_Credit Bit)
Insert Into @TEST Select 1, 150.00, 0
Union All Select 2, 100.00, 0
Union All Select 3, 100.00, 0
Union All Select 4, 100.00, 1 --測試
Select * From @TEST A
Where (Select Count(SID) From @TEST Where Amount = A.Amount And SID <= A.SID And Is_Credit = 0)
> (Select Count(SID) From @TEST Where Amount = A.Amount And Is_Credit = 1)
And Is_Credit = 0
--結果
/*
SID Amount Is_Credit
1 150.0000 0
3 100.0000 0*/