如果两行具有相同的amt，则将“平衡”列值设为“Y”，如果不在SQL查询中，则将“N”设置为“N” [英] Get 'balanced' column value as 'Y' if both rows has same amt and 'N' if not in SQL query

问题描述

如果Credit和Debit amt列匹配，如何获得Balanced列值'Y'如果不匹配则如何获得'N'



Pymtid ID名称类型AMT

CHT 1 A CR 100

CHT1 1 A DR 100

BHT 2 B CR 100

BHT1 2 B DR 90

DHT 3 C CR 150

DHT1 3 C DR 150





必填结果：





Pymtid ID名称类型AMT平衡

CHT 1 A CR 100 Y

CHT1 1 A DR 100 Y

BHT 2 B CR 100 N

BHT1 2 B DR 90 N

DHT 3 C CR 150 Y

DHT1 3 C DR 150 Y



我的尝试：

How to get the "Balanced" column value 'Y' if the Credit and Debit amt column matches and 'N' if they mismatch

Pymtid ID Name Type AMT
CHT 1 A CR 100
CHT1 1 A DR 100
BHT 2 B CR 100
BHT1 2 B DR 90
DHT 3 C CR 150
DHT1 3 C DR 150


Required Result :


Pymtid ID Name Type AMT Balanced
CHT 1 A CR 100 Y
CHT1 1 A DR 100 Y
BHT 2 B CR 100 N
BHT1 2 B DR 90 N
DHT 3 C CR 150 Y
DHT1 3 C DR 150 Y

What I have tried:

select 
case when 
(T.Amt = (select  P.Amt 
from BankData as p  
where P.Type = 'cr'
and p.ID = T.ID and t.Type = 'DR')) then 'Y' 
else 'N' end AS 'Balance', 
Name, *
 from BankData AS t

推荐答案

请尝试：

Please try:

SELECT  b1.*, 
   CASE WHEN (b1.amt=b2.amt) THEN 'Y'
   ELSE 'N' END AS 'Balance'
FROM bankdata b1
JOIN bankdata b2
ON b1.id=b2.id AND (b1.type<>b2.type)

说明：

我们需要bankdata的所有列（在查询别名b1中）和一个值为'Y'或'N'的列（称为'balance'）取决于具有相同id但不同类型的记录具有或不具有相同的数量。

因此，我们用自己加入bankdata表（但现在使用别名b2）。连接的限制是两个别名中的id是相同的，但是类型的值是不同的（因此，如果b1中的type的值是CR，那么b2中的值必须与CR不同（即DR） ，如果b1中的类型值是b2中的DR，则必须与DR不同（即CR）。

'balance'列的值可以使用CASE进行评估，具体取决于两个别名中的金额相等或不同。

Explanation:
We need all the columns of bankdata (in the query alias b1) and one more column (called 'balance') with the value of 'Y' or 'N' depending that the record with the same id but distinct type has or not has the same amount.
Thus, we JOIN the bankdata table with it self (but now with alias b2). The condicion for the join is that the id in both alias are the same but that the value of the type are distinct (so, if the value of type in b1 is CR then in b2 must to be distinct of CR (that is DR), and if the value of type in b1 is DR in b2 must to be distinct of DR (that is CR)).
The value of the 'balance' column can be evaluated with a CASE depending that the amount would be equal or distinct in the two alias.

首先，您可以创建一个包含交易总和的视图，其代码如下：

In first place you can create a view that contains the sum of the transactions with a code like:

CREATE VIEW [dbo].[bankDataGrouped]
AS
SELECT      ID, Name, Type, CustId, SUM(Amt) AS amt
FROM         dbo.BankData
GROUP BY ID, Name, Type, CustId
ORDER BY ID, Name, Type, CustId

GO

（然后你可以查询'bankDataGrouped'到obtein记录总和）



然后使用bankdata而不是使用bankdata与

(Then you can query 'bankDataGrouped' to obtein the sum of the records)

Then instead of use bankdata use bankDataGrouped with

SELECT  b1.*, 
   CASE WHEN (b1.amt=b2.amt) THEN 'Y'
   ELSE 'N' END AS 'Balance',
   customer.address
FROM bankdatagrouped b1
JOIN bankdatagrouped b2
	ON b1.id=b2.id AND (b1.type<>b2.type)
JOIN customer 
	ON b1.custid=customer.custid

你可以加入bankdatagrouped和客户。



如果这样做，请将我的anwser标记为'anwser accepted'。

you can JOIN both with bankdatagrouped and customer.

If this works please mark my anwser as 'anwser accepted'.

这篇关于如果两行具有相同的amt，则将“平衡”列值设为“Y”，如果不在SQL查询中，则将“N”设置为“N”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！