日期:2014-05-16  浏览次数:20991 次

求一排序显示语句
例表: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