mysql同一个表比较_mysql – 如何比较同一个表中的记录列？

这是我的测试表数据：

测试

ID Name Payment_Date Fee Amt

1 BankA 2016-04-01 100 20000

2 BankB 2016-04-02 200 10000

3 BankA 2016-04-03 100 20000

4 BankB 2016-04-04 300 20000

我试图比较每个数据记录的字段名称,费用和金额,以查看是否有相同的值.如果它们具有相同的值,我想在那些记录上标记“Y”.这是预期的结果

ID Name Payment_Date Fee Amt SameDataExistYN

1 BankA 2016-04-01 100 20000 Y

2 BankB 2016-04-02 200 10000 N

3 BankA 2016-04-03 100 20000 Y

4 BankB 2016-04-04 300 20000 N

我在下面尝试了这两种方法.但我正在寻找任何其他解决方案,所以我可以选择最适合我的工作.

方法1.

select t.*, iif((select count(*) from testing where name=t.name and fee=t.fee and amt=t.amt)=1,'N','Y') as SameDataExistYN from testing t

方法2.

select t.*, case when ((b.Name = t.Name)

and (b.Fee = t.Fee) and (b.Amt = t.Amt)) then 'Y' else 'N' end as SameDataExistYN

from testing t

left join ( select Name, Fee, Amt

from testing

Group By Name, Fee, Amt

Having count(*)>1 ) as b on b.Name = t.Name

and b.Fee = t.Fee

and b.Amt = t.Amt