只返回所有其他列相同且金额为正的行。

huangapple go评论62阅读模式
英文:

Return only the row with positive amount if all other columns are the same

问题

我需要返回仅具有正数金额的行。 请参考下面的示例。我只需要正数金额23450的行。 我不能简单地过滤掉负数,因为只有满足提供的示例条件的负数才感兴趣。 SQL Server 15.0.41

示例:

日期 结算 AcctNum 名称 TCode 金额
一月 1日 Y 123456 Mark tabcd 23450
一月 1日 Y 123456 Mark tabcd -23450
一月 7日 Y 111111 Vic tabcd 446623
三月 20日 Y 999999 Yas tvfeh 7778
一月 1日 Y 123456 Mark tabcd 987123
八月 9日 Y 123456 Mark tabcd 678599
十二月 2日 Y 564432 Ali tgghy -987699

期望结果:

日期 结算 AcctNum 名称 TCode 金额
一月 1日 Y 123456 Mark tabcd 23450
一月 7日 Y 111111 Vic tccwe 446623
三月 20日 Y 999999 Yas tvfeh 7778
一月 1日 Y 123456 Mark tabcd 987123
八月 9日 Y 123456 Mark tabcd 678599
十二月 2日 Y 564432 Ali tgghy -987699
英文:

I have a query that returns several columns, Date, settle, acctNum, name, TCode, Amount.
The problem is that, there are records that are identical except for the Amount, which has the same absolute value on both records except that it is positive in one and negative in the other. I need to return only the row with the positive value for Amount. Would anyone have any suggestion? Please, see the below example. I need only the row with the positive amount 23450. I can't just filter out negatives because only negative that meet the conditions of the provided example are of interest. SQL Server 15.0.41

Example:

Date Settle AcctNum Name TCode Amount
Jan 1 Y 123456 Mark tabcd 23450
Jan 1 Y 123456 Mark tabcd -23450
Jan 7 Y 111111 Vic tabcd 446623
Mar 20 Y 999999 Yas tvfeh 7778
Jan 1 Y 123456 Mark tabcd 987123
Aug 9 Y 123456 Mark tabcd 678599
Dec 2 Y 564432 Ali tgghy -987699

Expected result:

Date Settle AcctNum Name TCode Amount
Jan 1 Y 123456 Mark tabcd 23450
Jan 7 Y 111111 Vic tccwe 446623
Mar 20 Y 999999 Yas tvfeh 7778
Jan 1 Y 123456 Mark tabcd 987123
Aug 9 Y 123456 Mark tabcd 678599
Dec 2 Y 564432 Ali tgghy -987699

答案1

得分: 2

你可以使用 NOT EXISTS 来跳过“重复”的行:

select *
from tablename t1
where not exists
  (select 1 from tablename t2
   where t2.Date = t1.Date
     and t2.Settle = t1.Settle
     and t2.AcctNum = t1.AcctNum
     and t2.Name = t1.Name
     and t2.TCode = t1.Tcode
     and t2.Amount = -t1.Amount and t1.Amount > 0)

或者使用 EXCEPT

select Date, Settle, AcctNum, Name, TCode, Amount
from tablename
except
select Date, Settle, AcctNum, Name, TCode, -Amount
from tablename
where Amount > 0
英文:

You can use NOT EXISTS to skip the "duplicate" rows:

select *
from tablename t1
where not exists
  (select 1 from tablename t2
   where t2.Date = t1.Date
     and t2.Settle = t1.Settle
     and t2.AcctNum = t1.AcctNum
     and t2.Name = t1.Name
     and t2.TCode = t1.Tcode
   	 and t2.Amount = -t1.Amount and t1.Amount > 0)

Or use EXCEPT:

select Date, Settle, AcctNum, Name, TCode, Amount
from tablename
except
select Date, Settle, AcctNum, Name, TCode, -Amount
from tablename
where Amount > 0

答案2

得分: 2

选择 * 从 A 其中 A.Amount >= 0
或者不存在(选择 * 从 B 其中 B.Amount = -A.Amount
并且 B.Date = A.Date 并且 B.settle = A.settle 并且 B.acctNum = A.acctNum
并且 B.name = A.name 并且 B.TCode = A.TCode )

英文:

Something like :

select * from <tableName> A where A.Amount >= 0 
or not exists(select * from <tableName> B where B.Amount = -A.Amount
	and B.Date = A.Date and B.settle = A.settle and B.acctNum = A.acctNum 
	and B.name = A.name and B.TCode = A.TCode )

答案3

得分: 2

你可以使用窗口计数,例如:

select *
from (
  select *, 
    Iif(Amount > 0, 1, 
      Count(*) over(partition by Settle, AcctNum, Name, TCode, Abs(amount))
    ) Valid
  from t
)t
where Valid = 1;
英文:

You could make use of a window count, such as:

select *
from (
  select *, 
    Iif(Amount > 0, 1, 
      Count(*) over(partition by Settle, AcctNum, Name, TCode, Abs(amount))
    ) Valid
  from t
)t
where Valid = 1;

huangapple
  • 本文由 发表于 2023年5月28日 02:52:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76348521.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定