SQL 在另一列中选择具有相同id但不同值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21162812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select rows with same id but different value in another column
提问by josef_skywalker
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 |
+------+------+
回答by Yuck
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 ARIDNRvalue that occurs 1+ times in the data, then get all columns from the same table based on that set of values.
这个想法是使用内部查询来识别具有ARIDNR在数据中出现 1+ 次的值的记录，然后根据该组值从同一个表中获取所有列。
回答by Hasan Shouman
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
)
回答by Segfault
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.
将同一张表连接回自身。使用内部联接，以便丢弃不匹配的行。在连接集中，表中的另一行中将有具有匹配 ARIDNR 的行具有不同的 LIEFNR。允许那些 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
)
回答by Thys
This is an old questionyet 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|A
1|B
2|B
2|A
回答by Madhur Sodhi
Select A.ARIDNR,A.LIEFNR
from Table A
join Table B
on A.ARIDNR = B.ARIDNR
and A.LIEFNR<> B.LIEFNR
group by A.ARIDNR,A.LIEFNR
回答by Tejas Patel
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.
谢谢。
回答by MANIVANNAN
Use this
用这个
select * from (
SELECT ARIDNR,LIEFNR,row_number() over
(partition by ARIDNR order by ARIDNR) as RowNum) a
where a.RowNum >1
回答by radhason power
$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);
}
