如何使用RANK从表中筛选出第一个和最后一个条目

Question

我有这个数据：

Id   Date  Value
'a'  2000  55
'a'  2001  3
'a'  2012  2
'a'  2014  5
'b'  1999  10
'b'  2014  110
'b'  2015  8
'c'  2011  4
'c'  2012  33

我想过滤掉第一个和最后一个

ID   Date  Value
'a'  2001  3
'a'  2012  2
'b'  2014  110 

我尝试将order by（RANK（）OVER（PARTITION by[Id]order by Date…））与本文结合使用(http://blog.sqlauthority.com/2008/03/02/sql-server-how-to-retrieve-top-and-bottom-rows-together-using-t-sql/)但我不能让它工作。

[更新]
这3个答案看起来都不错。但我不是SQL专家，所以我的问题是，如果表有大约800000行，并且任何列上都没有索引，那么哪一个性能最快。

Answer 1

您可以使用row_number两次来确定min和max日期，然后进行相应的筛选：

with cte as (
  select id, [date], value, 
    row_number() over (partition by id order by [date]) minrn,
    row_number() over (partition by id order by [date] desc) maxrn
  from data
  )
select id, [date], value
from cte
where minrn != 1 and maxrn != 1

• SQL Fiddle演示

---

这里有另一种使用min和max而不需要使用排名函数的方法：

with cte as (
  select id, min([date]) mindate, max([date]) maxdate
  from data
  group by id 
  )
select * 
from data d
where not exists (
    select 1
    from cte c
    where d.id = c.id and d.[date] in (c.mindate, c.maxdate))

• 更多Fiddle

Answer 2

这里有一个类似的row_number和count的解决方案：

SELECT id,
       dat,
       value
FROM   (SELECT *,
               ROW_NUMBER()
                 OVER(
                   partition BY id
                   ORDER BY dat)    rnk,
               COUNT(*)
                 OVER (
                   partition BY id) cnt
        FROM   @table) t
WHERE  rnk NOT IN( 1, cnt ) 

Answer 3

您可以使用EXISTS:

SELECT *
FROM Table1 a
WHERE EXISTS (SELECT 1
                  FROM Table1 b
                  WHERE a.ID = b.ID
                   AND b.Date < a.Date
                  )
  AND EXISTS (SELECT 1
                  FROM Table1 b
                  WHERE a.ID = b.ID
                   AND b.Date > a.Date
                  )

演示：SQL Fiddle