oracle 根据多行（序列号）的存在从 SQL 中选择所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3927872/
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 all rows from SQL based upon existence of multiple rows (sequence numbers)
提问by Pezz
Let's say I have table data similar to the following:
假设我有类似于以下的表数据：
123456 John Doe 1 Green 2001
234567 Jane Doe 1 Yellow 2001
234567 Jane Doe 2 Red 2001
345678 Jim Doe 1 Red 2001
What I am attempting to do is only isolate the records for Jane Doe based upon the fact that she has more than one row in this table. (More that one sequence number) I cannot isolate based upon ID, names, colors, years, etc... The number 1 in the sequence tells me that is the first record and I need to be able to display that record, as well as the number 2 record -- The change record.
我试图做的只是根据 Jane Doe 在此表中有多于一行这一事实来隔离记录。（更多的是一个序列号）我无法根据 ID、姓名、颜色、年份等进行隔离......序列中的数字 1 告诉我这是第一条记录，我也需要能够显示该记录作为第 2 条记录——更改记录。
If the table is called users, and the fields called ID, fname, lname, seq_no, color, date. How would I write the code to select only records that have more than one row in this table? For Example:
如果表叫users，字段叫ID、fname、lname、seq_no、color、date。我将如何编写代码以仅选择该表中具有多于一行的记录？例如：
I want the query to display this only based upon the existence of the multiple rows:
我希望查询仅根据多行的存在来显示：
234567 Jane Doe 1 Yellow 2001
234567 Jane Doe 2 Red 2001
In PL/SQL
在 PL/SQL 中
回答by Larry Lustig
First, to find the IDs for records with multiple rows you would use:
首先，要查找具有多行的记录的 ID，您将使用：
SELECT ID FROM table GROUP BY ID HAVING COUNT(*) > 1
So you could get all the records for all those people with
所以你可以得到所有这些人的所有记录
SELECT * FROM table WHERE ID IN (SELECT ID FROM table GROUP BY ID HAVING COUNT(*) > 1)
If you know that the second sequence ID will always be "2" and that the "2" record will never be deleted, you might find something like:
如果您知道第二个序列 ID 将始终为“2”并且“2”记录永远不会被删除，您可能会发现如下内容：
SELECT * FROM table WHERE ID IN (SELECT ID FROM table WHERE SequenceID = 2)
to be faster, but you better be sure the requirements are guaranteed to be met in your database (and you would want a compound index on (SequenceID, ID)).
更快，但您最好确保数据库中满足要求（并且您希望在 (SequenceID, ID) 上使用复合索引）。
回答by erbsock
Try something like the following. It's a single tablescan, as opposed to 2 like the others.
尝试类似以下内容。这是一个单一的表扫描，而不是像其他的 2 个。
SELECT * FROM (
SELECT t1.*, COUNT(name) OVER (PARTITION BY name) mycount FROM TABLE t1
)
WHERE mycount >1;
回答by Randy
Check out the HAVINGclause for a summary query. You can specify stuff like
查看HAVING摘要查询的子句。你可以指定像
HAVING COUNT(*) >= 2
and so forth.
等等。
回答by kevpie
INNER JOIN
内部联接
JOIN:
加入：
SELECT u1.ID, u1.fname, u1.lname, u1.seq_no, u1.color, u1.date
FROM users u1 JOIN users u2 ON (u1.ID = u2.ID and u2.seq_no = 2)
WHERE:
在哪里：
SELECT u1.ID, u1.fname, u1.lname, u1.seq_no, u1.color, u1.date
FROM users u1, thetable u2
WHERE
u1.ID = u2.ID AND
u2.seq_no = 2
