有表T
id Uid Str
1 5 aaa
2 5 bbb
3 5 ccc
4 6 ddd
5 6 eee
6 8 ggg
7 8 ttt
8 2 uuu
9 2 hhh要得到结果
id Uid Str
9 2 hhh
1 5 aaa
4 6 ddd
6 8 ggg
8 2 uuu
2 5 bbb
5 6 eee
7 8 ttt
3 5 ccc
主要是Uid的规律
id Uid Str
1 5 aaa
2 5 bbb
3 5 ccc
4 6 ddd
5 6 eee
6 8 ggg
7 8 ttt
8 2 uuu
9 2 hhh要得到结果
id Uid Str
9 2 hhh
1 5 aaa
4 6 ddd
6 8 ggg
8 2 uuu
2 5 bbb
5 6 eee
7 8 ttt
3 5 ccc
主要是Uid的规律
解决方案 »
- 请教各位大神,下载了一个软件,是JAVA编写的,需要连接数据库的SQL server 2000 sa 密码为空
- 数据库字符集转换
- 关于一个查询方面的问题,请各位多多指教!先谢了!
- 通过 sql要实现下面的结果
- java连接sqlserver2000出错
- 怎样在XP、2003、2000上安装MDAC 2.5 ?
- SQL05中如何实现这样的查询
- 谁有威盾数据库方面的资料
- sqlserver 2000性能监测问题,请进!
- 怎么单机应用SQL?
- sql server7.0英文版 数据库的属性:Code Page:cp 1252 而另一台sql server7.0英文版Code Page:cp 936 why
- 百思不得其解的分组问题.
insert into @t select 1,5,'aaa'
insert into @t select 2,5,'bbb'
insert into @t select 3,5,'ccc'
insert into @t select 4,6,'ddd'
insert into @t select 5,6,'eee'
insert into @t select 6,8,'ggg'
insert into @t select 7,8,'ttt'
insert into @t select 8,2,'uuu'
insert into @t select 9,2,'hhh'select a.* from @t a order by (select count(*) from @t where Uid=a.Uid and id<=a.id),uid/*
id Uid Str
----------- ----------- ----
8 2 uuu
1 5 aaa
4 6 ddd
6 8 ggg
9 2 hhh
2 5 bbb
5 6 eee
7 8 ttt
3 5 ccc
*/
难道第一行还有其他的说法么??
难道第一行还有其他的说法么??
select count(*) from @t where Uid=a.Uid and id<=a.id
虽然如结果不是很符合请问高手
以上的结果,假设没有id字段,而是按Str排,又是怎样?
id Uid Str
9 2 hhh
1 5 aaa
4 6 ddd
6 8 ggg
8 2 uuu
2 5 bbb
5 6 eee
7 8 ttt
3 5 ccc现有结果
8 2 uuu
1 5 aaa
4 6 ddd
6 8 ggg
9 2 hhh
2 5 bbb
5 6 eee
7 8 ttt
3 5 ccc注意Str字段,也是有排序的
select a.* from @t a order by (select count(*) from @t where Uid=a.Uid and id<=a.id),uid換成select a.* from @t a order by (select count(*) from @t b where b.Uid=a.Uid and b.str<a.str),a.uid就可以了. 如果要給分給1 樓多點.
回itblog(^ω^) :因为目前所输入的数据比较有序,所以换一下小于号就可以得到结果但是如果
declare @t table(id int,Uid int,Str varchar(4))
insert into @t select 1,5,'bbb'
insert into @t select 2,5,'aaa'
insert into @t select 3,5,'ccc'
insert into @t select 4,6,'ddd'
insert into @t select 5,6,'eee'
insert into @t select 6,8,'ggg'
insert into @t select 7,8,'ttt'
insert into @t select 8,2,'uuu'
insert into @t select 9,2,'hhh'
insert into @t select 10,2,'iii'select a.* from @t a order by (select count(*) from @t where Uid=a.Uid and id>=a.id),uid结果
id Uid Str
----------- ----------- ----
10 2 iii
3 5 ccc
5 6 eee
7 8 ttt
9 2 hhh
2 5 aaa
4 6 ddd
6 8 ggg
8 2 uuu
1 5 bbb但要求结果是
id Uid Str
----------- ----------- ----
9 2 hhh
2 5 aaa
5 6 eee
7 8 ttt
10 2 iii
1 5 bbb
4 6 ddd
6 8 ggg
8 2 uuu
3 5 ccc
请看Uid相同的Str