如何从sql查询中获取第一条和最后一条记录?

[警告:可能不是最有效的方法]:

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1)

UNION ALL

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC    
LIMIT 1)

您可能希望尝试此操作,可能比执行两个查询更快:

select <some columns>
from (
    SELECT <some columns>,
           row_number() over (order by date desc) as rn,
           count(*) over () as total_count
    FROM mytable
    <maybe some joins here>
    WHERE <various conditions>
) t
where rn = 1
   or rn = total_count
ORDER BY date DESC

SELECT <some columns> FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC
LIMIT 1

最后记录:

SELECT <some columns> FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1

最后记录:

SELECT * FROM `aboutus` order by id desc limit 1

SELECT * FROM `aboutus` order by id asc limit 1

到目前为止,在所有公开的do方式中,必须进行两次扫描,一次扫描第一行,一次扫描最后一行。

使用窗口函数“ROW_NUMBER()OVER(…)”加上“WITH querys”,您只能扫描一次并获取两个项目。

https://www.postgresql.org/docs/9.6/static/functions-window.html

如有疑问: https://www.postgresql.org/docs/9.6/static/queries-with.html

例子:

WITH scan_plan AS (
SELECT
    <some columns>,
    ROW_NUMBER() OVER (ORDER BY date DESC) AS first_row, /*It's logical required to be the same as major query*/
    ROW_NUMBER() OVER (ORDER BY date ASC) AS last_row /*It's rigth, needs to be the inverse*/
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC)

SELECT
    <some columns>
FROM scan_plan
WHERE scan_plan.first_row = 1 OR scan_plan.last_row = 1;

试着从两方面进行解释和分析。

SELECT <rows> FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) 
UNION
SELECT <rows> FROM TABLE_NAME WHERE ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME)

或

SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) 
                            OR ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME)

我知道这是一个有7年历史的线程,但问题几乎是相同的,公认的答案是我开始使用的线程,并最终优化为以下线程,在我的情况下,这些线程会持续返回 85毫秒 +-5ms,带<一些专栏>作为索引整型字段的。

注1 :接受答案中的UNION ALL示例也有效,但在我的情况下,在300ms+-20ms时性能较差。

注2

select
  (select <some_column> from <some_table>
    order by <some_field> limit 1)        as oldest,
  (select <some_column> from <some_table> 
    order by <some_field> desc limit 1)   as newest
;

我注意到op引用了可能的连接。我不需要为了自己的目的而包含连接(只是在相当动态的视图中获取当前的低ID和高ID),但是使用这个模型,最早和最新的子查询应该能够是完全成熟的查询。还没有测试,所以不确定它是否有效或是最佳的。

我确实测试了这个模型(上面可能已经建议过),它可能更容易加入,但性能只是略低于上面示例的一半,在我的情况下,始终返回220ms+-10ms。

select oldest.<some_field> as old, 
       newest.<some_field> as new  
from
  (select <some_column> from <some_table>
    order by <some_field> limit 1)        as oldest,
  (select <some_column> from <some_table> 
    order by <some_field> desc limit 1)   as newest
;

只有一个查询 有好几列。

 SELECT
    FIRST_VALUE(timestamp) over w as created_dt,
    LAST_VALUE(timestamp) over w as last_update_dt,
    LAST_VALUE(action) over w as last_action
FROM events
WINDOW w as (ORDER BY timestamp ASC)

SELECT DISTINCT
    order_id,
    FIRST_VALUE(timestamp) over w as created_dt,
    LAST_VALUE(timestamp) over w as last_update_dt,
    LAST_VALUE(action) over w as last_action
    
FROM events as x
WINDOW w as (PARTITION BY order_id ORDER BY timestamp ASC)

select *
from {Table_Name}
where {x_column_name}=(
    select d.{x_column_name} 
    from (
        select rownum as rno,{x_column_name}
        from {Table_Name})d
        where d.rno=(
            select count(*)
            from {Table_Name}));

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;


-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

从这里得到: https://wiki.postgresql.org/wiki/First/last_(aggregate)

SELECT 
    MIN(Column), MAX(Column), UserId 
FROM 
    Table_Name
WHERE 
    (Conditions)
GROUP BY 
    UserId DESC

或

SELECT        
    MAX(Column) 
FROM            
    TableName
WHERE        
    (Filter)

UNION ALL

SELECT        
    MIN(Column)
FROM            
    TableName AS Tablename1
WHERE        
    (Filter)
ORDER BY 
    Column

为什么不使用 order by asc limit 1 反之亦然, order by desc limit 1 ?

SELECT TOP 1 * 
  FROM ViewAttendenceReport 
 WHERE EmployeeId = 4 
   AND AttendenceDate >='1/18/2020 00:00:00' 
   AND AttendenceDate <='1/18/2020 23:59:59'
 ORDER BY Intime ASC
 UNION
SELECT TOP 1 * 
  FROM ViewAttendenceReport 
 WHERE EmployeeId = 4 
   AND AttendenceDate >='1/18/2020 00:00:00' 
   AND AttendenceDate <='1/18/2020 23:59:59' 
 ORDER BY OutTime DESC; 

我认为这段代码是相同的,更容易阅读。

SELECT <some columns> 
FROM mytable
<maybe some joins here>
WHERE date >= (SELECT date from mytable)
OR date <= (SELECT date from mytable);