求一排序显示语句
例表:T
ID Name Da
1 aaaa 2
2 aaaa 3
3 aaaa 4
4 bbbb 5
5 bbbb 6
6 cccc 7
7 cccc 12
8 dddd 8
9 dddd 10
想要的结果:
7 cccc 12
9 dddd 10
5 bbbb 6
3 aaaa 4
8 dddd 8
6 cccc 7
5 bbbb 5
2 aaaa 3
1 aaaa 2
------解决方案--------------------不好做,
select * from (
SELECT *,1 as cs from tt5 a where da=
(select top 1 da from tt5 where name=a.name order by da desc) )
UNION select * from (
SELECT *,2 as cs from tt5 a where da=
(select top 1 da from tt5 where name=a.name order by da asc))
union
select * from (
select a.*,3 as cs from tt5 a left join (
SELECT name,max(da) as ma,min(da) as mi from tt5 group by name) b
on (a.name=b.name) where (a.da> mi) and (a.da <ma))
ORDER BY cs, da DESC
如果同一NAME中有N个数的话,SQL语句会很长,上述示例在同一NAME中,只有
3个数。
------解决方案--------------------以下語句在ACCESS中測試OK
Select ID, Name, Da
From (Select *, (Select Count(*) From T3 Where Name = A.Name And Da > = A.Da) As OrderID From T3 A) B
Order By OrderID, Da Desc