SQL查询问题 [英] SQL query problem

问题描述

我正在从表中获取一些数据.假设我的查询是这样的

I am fetching some data from a table. Suppose my query is like this

select ename from emp where job="clerk"

这也会返回重复的 ename，但按照它们输入的顺序.

This is returning duplicate ename too but in order in which they have entered.

我将其修改为

select distinct ename from emp where job="clerk"

我这样做是为了避免重复值.但是 ename 是按字母升序排列的.但我希望按照我输入数据的顺序进行，因为它不使用不同的数据.

I did this to avoid duplicate values.But ename are coming in alphabetically ascending order. But i want this in the order in which i have entered data as it was coming without using distinct.

推荐答案

SQL 表中的数据没有固有的顺序.如果你想要一个订单，你必须使用 ORDER BY 来指定它.如果您使用 DISTINCT，则只能按您选择的列排序！

Data in SQL tables have no inherent order. If you want an order, you have to specify it using ORDER BY. If you use DISTINCT, you can only order by columns that you select!

如果您有 PK，这将是一个近似值:

If you have a PK, this would be an approximation:

select distinct id,ename from emp where job="clerk" order by id desc

来自较旧的 answer(我知道这会有所帮助):

From an older answer (I knew this would be helpful):

除非您指定 ORDER BY，否则无法保证哪些行将首先返回.

Unless you specify ORDER BY, there are no guarantees about what rows will be returned first.

但在实践中，顺序通常会匹配聚集索引顺序，并且不会因调用而异.不要依赖这种行为.

But in practice, the order will typically match the clustered index order, and will not vary between calls. Don't rely on this behaviour.

MySQL 的 LIMIT 关键字是否保证返回数据的顺序?

没有默认的排序顺序.即使表有聚集索引，您不能保证按该顺序获得结果.你如果您需要特定的订单，则必须使用 order by 子句.

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

处理默认排序顺序的 SQL 最佳实践

....

这篇关于SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！