oracle 如何使用SQL查找表中的第一列和第一行
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/853409/
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
How to find a first column and first row in a table using SQL
提问by Arc
I was asked this question and could not come up with the answer.
我被问到这个问题,无法想出答案。
Is there a way to find the entry at the first row and first column in a table?
有没有办法在表格的第一行和第一列找到条目?
(In matrix notation, that would be the [1,1]th
location)
(在矩阵表示法中,这将是[1,1]th
位置)
回答by Quassnoi
In a couple of words: a relational table is not a matrix.
简而言之:关系表不是矩阵。
In relational world, 1'st row
makes sense only when you define some order on your dataset. There is no such thing as "implicitly first record".
在关系世界中,1'st row
只有当您在数据集上定义某种顺序时才有意义。没有“隐式第一个记录”这样的东西。
Assuming you order your rows on the values of the first column, it's this:
假设您根据第一列的值对行进行排序,就是这样:
SELECT column1
FROM table
ORDER BY
column1
LIMIT 1
Also note that different columns in the table belong to different domains
.
还要注意,表中不同的列属于不同的domains
.
The point of having the table is to define some relation
between these domains
, that's why the whole thing is called "relational database", and the tables themselves are sometimes called "relations".
拥有表的重点是relation
在这些之间定义一些domains
,这就是为什么整个事物被称为“关系数据库”,而表本身有时被称为“关系”。
The column order also has no special meaning in a relational table.
列顺序在关系表中也没有特殊意义。
It's assumed you always refer to the column by name.
假设您总是按名称引用列。
If replacing the order of a column affects the result of your query, it is considered a poor design.
如果替换列的顺序会影响您的查询结果,则被认为是一个糟糕的设计。
There are things like SELECT *
and ORDER BY 1
, but they generally should be avoided.
有像SELECT *
and 之类的东西ORDER BY 1
,但通常应该避免它们。
回答by IK.
There were a lot of good feedback related to the "first row" part of thge question. But as far as the "first column" goes, you can try one of the following options:
有很多与问题的“第一行”部分相关的良好反馈。但就“第一列”而言,您可以尝试以下选项之一:
You can use database data dictionary to get the first column of the table. In Oracle, for example the query would be:
select column_name from user_tab_columns where table_name='YOUR_TABLE_NAME' and column_id=1
If you use JDBC or some other database access API, most of these APIS would allow you to do
"select *"
and then you could get first column by accessing zero (or first) element of you result set.
您可以使用数据库数据字典来获取表的第一列。例如,在 Oracle 中,查询将是:
select column_name from user_tab_columns where table_name='YOUR_TABLE_NAME' and column_id=1
如果您使用 JDBC 或其他一些数据库访问 API,大多数这些 APIS 将允许您这样做
"select *"
,然后您可以通过访问结果集的零(或第一个)元素来获得第一列。
回答by Tmdean
Conceptually, SQL tables are unordered. You need to specify columns to order by in order for the idea of the "first row" to be meaningful.
从概念上讲,SQL 表是无序的。您需要指定要排序的列,以使“第一行”的想法有意义。
This is easy if your DBMS supports LIMIT.
如果您的 DBMS 支持 LIMIT,这很容易。
SELECT * FROM table ORDER BY column LIMIT 1
Otherwise you can use TOP, but if your order by column has duplicate values for the top item, you might get more rows than you ask for (depending on your DBMS, in MS-SQL, it won't do this unless you specify WITH TIES).
否则,您可以使用 TOP,但如果您的按列排序的顶部项目具有重复的值,您可能会得到比您要求的更多的行(取决于您的 DBMS,在 MS-SQL 中,除非您指定 WITH领带)。
SELECT TOP 1 * FROM Table ORDER BY column
回答by Andrew Duffy
A relational table doesn't have a defined row order, so there is no first row. You can certainly select the first result from a query using whatever LIMIT
equivalent your database offers.
关系表没有定义的行顺序,因此没有第一行。您当然可以使用LIMIT
您的数据库提供的任何等效项从查询中选择第一个结果。
Some databases that use ISAM-type tables will appear to return a consistent row order when you run queries without an ordered cursor, but deleting and inserting can easily change that.
当您在没有有序游标的情况下运行查询时,一些使用 ISAM 类型表的数据库似乎会返回一致的行顺序,但删除和插入可以轻松改变这一点。
回答by HLGEM
If you are trying to find the first record entered into the table, then you need to have set up a DateEntered field at the time of table creation, then you can find the first record by seraching for the earliest date. Ordering on the identity field if you have one is a way to get the earlier records but you can't really guarantee that transaction which created ID 1 really got inserted to the table earlier than the transaction that generated ID 2. You only know that transaction started first, not finished first. Using a date field is the only way to truly be sure. Over the years, I've learned to design for these things, you can't query information later that you didn't store in the first place.
如果您要查找输入到表中的第一条记录,那么您需要在创建表时设置一个DateEntered 字段,然后您可以通过搜索最早的日期来查找第一条记录。对标识字段进行排序是一种获取较早记录的方法,但您不能真正保证创建 ID 1 的事务确实比生成 ID 2 的事务更早地插入到表中。您只知道该事务先开始,没有先完成。使用日期字段是真正确定的唯一方法。多年来,我已经学会了为这些东西设计,你以后不能查询你一开始没有存储的信息。
回答by codeulike
Select top 1 Column1
From MyTable
Order by Column1