基于多行（序列号）的存在从SQL中选择所有行 [英] Select all rows from SQL based upon existence of multiple rows (sequence numbers)

问题描述

假设我的表格数据类似于以下内容：

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

我尝试做的只是根据她在此表中有多个行的事实来隔离Jane Doe的记录。 （更多一个序列号）
我不能根据ID，名称，颜色，年份等隔离
序列中的数字1告诉我，这是第一条记录，我需要能够显示该记录，以及数字2记录 - 更改记录。

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.

如果表称为users，并且字段名为ID，fname，lname ，seq_no，color，date。我如何编写代码以仅选择在此表中有多行的记录？例如：

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:

我希望查询仅基于多行的存在显示：

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

$ b b

在PL / SQL

In PL/SQL

推荐答案

首先，要查找多行记录的ID， p>

First, to find the IDs for records with multiple rows you would use:

 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)

如果你知道第二个序列ID永远是2，并且2记录永远不会被删除，你可能会发现：

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:

 SELECT * FROM table WHERE ID IN (SELECT ID FROM table WHERE SequenceID = 2)

更快，但是你最好确保在数据库中满足要求（并且你想要一个复合索引SequenceID，ID））。

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)).

这篇关于基于多行（序列号）的存在从SQL中选择所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！