英文:
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
选择 * 从
或者不存在（选择 * 从
并且 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论