将相同的金额组合在一起，但在不同的列中找到不同的金额

Question

我需要创建一个查询，在不同列中至少有两个不同金额的情况下，查找一个或多个相同金额。请参阅下面的输出示例：

Name total_amount  amount
a1      1000         200
a1      1000         200
a1      1000          0
a1      1000         100
a2      3400         300
a2      3400         100
a2      3400         300
a3      5000         500
a3      5000          0
a3      5000         500
a3      5000         200

数字可以是负数也可以是正数。我试过：

Select name, total_amount, amount 
from table1
where total_amount IN (Select total_amount from table1 where amount IN (Select distinct amount from table1 group by amount) 
group by total_amount 
having count(*) > 1)
order by total_amount

欢迎任何建议，提前谢谢！

Answer 1

您可以使用exists:

select t1.*
from table1 t1
where exists (select 1 
              from table1 t2 
              where t2.total_amount = t1.total_amount and t1.amount <> t2.amount
             );