在另一列中选择id相同但值不同的行

I tried for hours and read many posts but I still can't figure out how to handle this request:

我试了几个小时，看了很多帖子，但我还是不知道如何处理这个请求:

I have a table like this:

我有一张这样的桌子:

+------+------+
|ARIDNR|LIEFNR|
+------+------+
|1     |A     |
+------+------+
|2     |A     |
+------+------+
|3     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+

I would like to select the ARIDNR that occurs more than once with the different LIEFNR.

我想选择在不同的LIEFNR中出现多次的ARIDNR。

The output should be something like:

输出应该如下所示:

+------+------+
|ARIDNR|LIEFNR|
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |A     |
+------+------+
|2     |B     |
+------+------+

7 个解决方案

#1

This ought to do it:

应该这样做:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

其思想是使用内部查询来标识数据中出现1+次的ARIDNR值的记录，然后根据该值集从同一表中获取所有列。

#2

Try this please. I checked it and it's working:

请试试这个。我检查了一下，它起作用了:

SELECT *
FROM Table
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM Table
    GROUP BY ARIDNR
    HAVING COUNT(distinct LIEFNR) > 1
)

#3

Join the same table back to itself. Use an inner join so that rows that don't match are discarded. In the joined set, there will be rows that have a matching ARIDNR in another row in the table with a different LIEFNR. Allow those ARIDNR to appear in the final set.

将相同的表连接回自己。使用内部连接，以便不匹配的行被丢弃。在已连接的集合中，将会有在表中另一行中具有与不同的LIEFNR匹配的ARIDNR的行。允许那些ARIDNR出现在最终的集合中。

SELECT * FROM YourTable WHERE ARIDNR IN (
    SELECT a.ARIDNR FROM YourTable a
    JOIN YourTable b on b.ARIDNR = a.ARIDNR AND b.LIEFNR <> a.LIEFNR
)

#4

Use this

使用这个

select * from (
 SELECT ARIDNR,LIEFNR,row_number() over 
     (partition by ARIDNR order by ARIDNR) as RowNum) a
where a.RowNum >1

#5

You can simply achieve it by

你可以简单地实现它

SELECT *
FROM test
WHERE ARIDNR IN
    (SELECT ARIDNR FROM test
     GROUP BY ARIDNR
     HAVING COUNT(*) > 1)
GROUP BY ARIDNR, LIEFNR;

Thanks.

谢谢。

#6

This is an old question yet I find that I also need a solution for this from time to time. The previous answers are all good and works well, I just personally prefer using CTE, for example:

这是一个老问题，但我发现我也需要解决这个问题。前面的答案都很好，效果也不错，我个人比较喜欢使用CTE，例如:

DECLARE @T TABLE (ARIDNR INT, LIEFNR varchar(5)) --table variable for loading sample data
INSERT INTO @T (ARIDNR, LIEFNR) VALUES (1,'A'),(2,'A'),(3,'A'),(1,'B'),(2,'B'); --add your sample data to it
WITH duplicates AS --the CTE portion to find the duplicates
(
    SELECT ARIDNR FROM @T GROUP BY ARIDNR HAVING COUNT(*) > 1
)
SELECT t.* FROM @T t --shows results from main table
INNER JOIN duplicates d on t.ARIDNR = d.ARIDNR --where the main table can be joined to the duplicates CTE

Yields the following results:

会产生以下结果:

1|A
1|B
2|B
2|A

1 B | 1 | 2 | 2 |

#7

-1

$sql="SELECT * FROM TABLE_NAME WHERE item_id=".$item_id;

$query=mysql_query($sql);

while($myrow=mysql_fetch_array($query)) {

echo   print_r($myrow,1);


}

#1