如何从mysql结果中选择第一个和最后一个数据行？

SELECT * from User returns 75 users. Is it possible to select 1st user, and 75th user without doing while($row = mysql_fetch_assoc($result)) ?? and how?

SELECT * from User返回75个用户。是否可以选择第一个用户和第75个用户而不用while($ row = mysql_fetch_assoc($ result))??如何?

UPDATE

Just to be more clear: I need to have SELECT * as I need the first and 75th user before I do while mysql_fetch_assoc so ASC, DESC, LIMIT answers not required.

为了更清楚:我需要有SELECT *因为我需要第一个和第75个用户才能做到mysql_fetch_assoc所以ASC,DESC,LIMIT不需要回答。

6 个解决方案

#1

SELECT * from User LIMIT 1
UNION
SELECT * from User LIMIT 74,1

---

Edit

@Kay: PHP can't change the internal order of the resultset after it's created.

@Kay:PHP在创建后无法更改结果集的内部顺序。

If the query always returns 75 rows then the only way to access the 1st and the 75th before anything else would be to use mysql_data_seek which moves the internal result pointer:

如果查询总是返回75行,那么访问第1和第75之前的唯一方法就是使用mysql_data_seek来移动内部结果指针:

$result = mysql_query('SELECT * from User');

mysql_data_seek($result, 1);
$row1 = mysql_fetch_assoc($result);

mysql_data_seek($result, 75);
$row75 = mysql_fetch_assoc($result);

Note that if the above is followed by a while, the pointer must be reset to a suitable position.

请注意,如果上面跟着一段时间,指针必须重置到合适的位置。

#2

If you can sort it, you can.

如果你可以对它进行排序,你可以。

Select * from User order by [something] asc limit 1

and

Select * from User order by [something] desc limit 1

#3

Assuming you have 'id' as a primary key and you need the last one (not the 75th one) you could try something like:

假设你有'id'作为主键,你需要最后一个(不是第75个),你可以尝试这样的事情:

SELECT * FROM User WHERE id IN ((SELECT min(id) FROM user b), (SELECT max(id) FROM user c)) 

#4

SELECT 
(SELECT column FROM table WHERE [condition] ORDER BY column LIMIT 1) as 'first',
(SELECT column FROM table WHERE [condition] ORDER BY column DESC LIMIT 1) as 'last'

#5

SELECT  u.*
FROM    Users u
        INNER JOIN (
          SELECT MIN(UserID) AS UserID FROM Users
          UNION ALL SELECT MAX(UserID) FROM Users
        ) um ON um.UserID = u.UserID

Edit

I'm not sure I completely understand what you need but following gets the first and last user followed by everyone else.

我不确定我完全理解你需要什么,但是后面的第一个和最后一个用户跟着其他人。

SELECT  um.SortOrder, u.*
FROM    Users u
        INNER JOIN (
          SELECT 1 AS SortOrder, MIN(UserID) AS UserID FROM Users
          UNION ALL SELECT 2, MAX(UserID) FROM Users
        ) um ON um.UserID = u.UserID
UNION ALL
SELECT  3 AS SortOrder, u.*
FROM    Users u
        LEFT OUTER JOIN (
          SELECT MIN(UserID) AS UserID FROM Users
          UNION ALL SELECT MAX(UserID) FROM Users
        ) um ON um.UserID = u.UserID
WHERE   um.UserID IS NULL
ORDER BY
        SortOrder

#6

Well, the title of your question is a bit different from the explanation you gave.

好吧,你问题的标题与你给出的解释略有不同。

I say so cos if you want to select first and last row, it might be different to select 1st and 75th row cos in case the rows increase or reduce, the last might not be the 75th row.

如果你想选择第一行和最后一行,我就这么说,如果行增加或减少,选择第1行和第75行cos可能会有所不同,最后一行可能不是第75行。

To answer the part of the first and last row, i think you can do this.

要回答第一行和最后一行的部分,我想你可以做到这一点。

select distinct(id) from users where id in ((select max(id) from user), (select min(id) from users));

This query will work well in the hope that users are ordered by id.

此查询将很好地工作,希望用户按ID排序。

But if you are talking about the 1st and 75th row, then i'll settle with @Saul's answer .

但如果你在谈论第1和第75行,那么我将以@Saul的回答来解决。

Hope this helps.

希望这可以帮助。