中间一条是什么概念?首尾条如下
select a.* from t a join (select typename,min(typedate)as typedate from t group by typedate)b on a.typename=b.typename and a.typedate=b.typedate
union all
select a.* from t a join (select typename,max(typedate)as typedate from t group by typedate)b on a.typename=b.typename and a.typedate=b.typedate
select a.* from t a join (select typename,min(typedate)as typedate from t group by typedate)b on a.typename=b.typename and a.typedate=b.typedate
union all
select a.* from t a join (select typename,max(typedate)as typedate from t group by typedate)b on a.typename=b.typename and a.typedate=b.typedate
如果是偶数,中间这条怎么定啊?
select * from meal_soft a where (select count(1) from meal_soft where m_name=a.m_name and m_date>=a.m_date)<=1 or (select count(1) from meal_soft where m_name=a.m_name and m_date<=a.m_date)<=1
select * from tb a where (select count(1) from tb where typename=a.typename and typedate>=a.typedate)<=1 or (select count(1) from tb where typename=a.typename and typedate<=a.typedate)<=1
--表中没主键?
select id=identity(int),* into #t from 原表select a.*
from #t a,(
select a=min(id), --第一条记录的id
b=min(id), --最后一条记录的id
c=min(id)+(max(id)-min(id))/2 --中间一条记录的id
from #t
group by typename
)b where a.id in(b.a,b.b,b.c)
order by a.typename,a.id
--表中没主键?
select id=identity(int),* into #t from 原表select a.*
from #t a,(
select typename,
a=min(id), --第一条记录的id
b=min(id), --最后一条记录的id
c=min(id)+(max(id)-min(id))/2 --中间一条记录的id
from #t
group by typename
)b where a.typename=b.typename
and a.id in(b.a,b.b,b.c)
order by a.typename,a.id--如果是按typedate顺序,如果每个typename的typedate不唯一,则也要用临时表
select id=identity(int),* into #t from 原表 order by typename,typedateselect a.*
from #t a,(
select typename,
a=min(id), --第一条记录的id
b=min(id), --最后一条记录的id
c=min(id)+(max(id)-min(id))/2 --中间一条记录的id
from #t
group by typename
)b where a.typename=b.typename
and a.id in(b.a,b.b,b.c)
order by a.typename,a.id
--如果是按typedate顺序,每个typename的typedate唯一,则可以直接用
select a.*
from 原表 a,(
select typename,
a=min(typedate), --第一条记录的id
b=min(typedate) --最后一条记录的id
from 原表
group by typename
)b where a.typename=b.typename and
a.typedate in(b.a,b.b,(select top 1 typedate from(select top 50 percent typedate from 原表 where typename=a.typename order by typedate)aa order by typedate desc))
order by a.typename,a.typedate
select a.*
from 原表 a,(
select
a=min(id), --第一条记录的id
b=max(id) --最后一条记录的id
from 原表
group by typename
)b where a.id in(b.a,b.b,(select top 1 id from(select top 50 percent id from 原表 where typename=a.typename order by id)aa order by id desc))
order by a.typename,a.id
select a.*
from #t a,(
select a=min(id), --第一条记录的id
b=min(id), --最后一条记录的id
c=min(id)+(max(id)-min(id))/2 --中间一条记录的id
from #t
group by typename
)b where a.id in(b.a,b.b,b.c)
order by a.typename,a.id