选择语句以使用2个不同的代码进行重复记录搜索 [英] Select statement for duplicate record search using 2 different codes

问题描述

我正在尝试用SQL编写一条select语句，该语句将在数据库中搜索具有2个单独代码的重复记录.换句话说，它的伪代码将是

I am trying to write a select statement in SQL that will search the database for duplicate records that have 2 separate codes on it. In other words it pseudo code would be

在m_code = J或T的地方选择发票编号.这将给我2345个发票编号，一次是使用代码J，一次是使用代码T.

Select invoice numbers where the m_code = J or T. This would give me invoice number 2345 twice, once with code J and once with code T.

推荐答案

如果您要查找特定的代码，则可以使用带有HAVING和GROUP BY的WHERE子句过滤器:

If you were looking for specific code, then you could use a WHERE clause filter with a HAVING and GROUP BY:

select invoice
from yourtable
where m_code in ('J', 'T')
group by invoice
having count(distinct m_code) > 1;

但是，如果您想退回发票，并且发票上有任何两个重复的m_codes，则可以使用:

But if you want to return and invoices with any two duplicated m_codes then you could use:

select invoice
from yourtable
group by invoice
having count(distinct m_code) > 1;

请参见两个查询的带有演示的SQL提琴

这篇关于选择语句以使用2个不同的代码进行重复记录搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！