我有一张表,我想找到满足特定月份条件的第一条和最后一条记录。
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'
只有在将查询结果按字段排序后,第一个和最后一个才有意义。
要获取第一条记录:
select col1 from tab1 order by col1 asc limit 1;
获取最后一条记录:
select col1 from tab1 order by col1 desc limit 1;
那么可以考虑类似这样的方法:
select 'first', f1, f2, f3, f4 from tbl
order by f1 asc, f2 asc
limit 1
union all
select 'last', f1, f2, f3, f4 from tbl
order by f1 desc, f2 desc
limit 1
在 where
子句中可以随意添加任何条件,但是 order by
的基本前提是反转两个 select
部分的顺序。
limit
子句将只获取两种情况下的第一行。由于您已经颠倒了排序顺序,这恰好是第二个 select
集合中集合的最后一行。
如果仅有一个满足条件的行且不希望返回两次,请使用 union
而不是 union all
。
SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and ORDER BY
。 - piotr_czselect * from table
where id = (select id from tab1 order by col1 asc limit 1) or
id = (select id from tab1 order by col1 desc limit 1);
试试这个:例如你想根据group_col对表进行分组,并获取value_col的第一个和最后一个值:
select substring_index(group_concat(value_col), ',',1) as 'first',
substring_index(group_concat(value_col), ',',-1) as 'last'
from table
group by group_col
SELECT * FROM (
SELECT first_name, LENGTH(first_name) FROM Employees
ORDER BY LENGTH(first_name) ASC
FETCH FIRST 1 rows ONLY)
UNION
SELECT * FROM (
SELECT first_name, LENGTH(first_name) FROM Employees
ORDER BY LENGTH(first_name) DESC
FETCH FIRST 1 rows ONLY)
ORDER BY 2 desc;