****** sql的排序问题，进来有分******

[lijun_xiao2009]

select Sh_dTGold.Tid,Sh_dTGold.Tprice,Sh_dTGold.Tquantity,Sh_dTGold.Tstate,Sh_Game.Gname,Sh_Server.Sname
from Sh_TGold,Sh_Game,Sh_Server
where Sh_dTGold.Gid = Sh_Game.Gid
and Sh_dTGold.Sid = Sh_Server.Sid
and Sh_dTGold.Gid =1 order by Sh_Server.Sname, Tquantity desc
我想先按Sh_Server.Sname，再按Tquantity 倒序，但这样写不可以，是怎么回事

[SoCsy]

大牛都回答过了，那我接分吧，多给点，O(∩_∩)O谢谢

[duanzhi1984]

你说有分的！

[abuying]

如果是2005的话，可以使用row_number()
--2000,须有结构与数据来测试
uid class
----------- -----------
6 6
5 5
4 4
3 3
2 2
1 1
按uid升序，class降序，想得到下列结果
uid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1

select uid,class from
(select id=row_number()over (order by uid ASC),uid from tb ) a inner join
(select id=row_number()over (order by class deSC),class from tb ) b
on a.id=b.id

就可以得到！

类推

select a.Sname,b.Tquantity,a.Tid,a.Tprice,a.Tstate,a.Gname
 from 
(select id1=row_number() over(order by Sh_Server.Sname asc)
,Sh_dTGold.Tid,Sh_dTGold.Tprice,Sh_dTGold.Tstate,Sh_Game.Gname,Sh_Server.Sname  --这里少了Sh_dTGold.Tquantity
from Sh_TGold,Sh_Game,Sh_Server  
where Sh_dTGold.Gid = Sh_Game.Gid  and Sh_dTGold.Sid = Sh_Server.Sid  
and Sh_dTGold.Gid =1) A 
inner join
(select id2=row_number() over(order by Tquantity desc)
,Sh_dTGold.Tid,Sh_dTGold.Tprice,Sh_dTGold.Tquantity,Sh_dTGold.Tstate　--这里少了Sh_Server.Sname
from Sh_TGold,Sh_Game,Sh_Server  
where Sh_dTGold.Gid = Sh_Game.Gid  and Sh_dTGold.Sid = Sh_Server.Sid  
and Sh_dTGold.Gid =1) B
on A.id1=B.id2
--注a.Tid,a.Tprice,a.Tstate,a.Gname顺序可能会打乱

[jiao3630]

[habingwang]

Tquantity字段若同时存在多个表中，使用此字段的地方要写明是哪个表的。

[seiferbb]

你试一下先把查询建成一个视图然后再在视图里面排，应该就可以了。我之前也是这样，不知道为什么，视图就可以，查询就不行。

[叶子]

order by Sh_Server.Sname, Sh_dTGold.Tquantity desc

[Mr_Nice]

[Quote=引用楼主 lijun_xiao2009 的回复:]
select Sh_dTGold.Tid,Sh_dTGold.Tprice,Sh_dTGold.Tquantity,Sh_dTGold.Tstate,Sh_Game.Gname,Sh_Server.Sname
from Sh_TGold,Sh_Game,Sh_Server
where Sh_dTGold.Gid = Sh_Game.Gid
and Sh_dTGold.Sid = Sh……
[/Quote]


order by Sh_Server.Sname, Tquantity desc 地方已经是排序了。 还请lz发上数据和要求的结果看看。

[shizheyangde]

应该没问题

[lijun_xiao2009]

[Quote=引用 15 楼 abuying 的回复:]
如果是2005的话，可以使用row_number()
--2000,须有结构与数据来测试
uid class
----------- -----------
6 6
5 5
4 4
3 3
2 2
1 1
按uid升序，class降序，想得到下列结果
uid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 ……
[/Quote]

你这是很好，可我的是2000数据库，不可以用row_number()函数

[claro]

了解，可以构造一个临时表并插入序号。

[Sweet-Tang]

DECLARE @tmp_Table TABLE (id int identity(1,1),Tid int )

声时临时表

[SQLCenter]

天啊，这叫排序？

这叫重新分配，拆数据。

[fpzgm]

[Quote=引用 22 楼 lijun_xiao2009 的回复:]
引用 15 楼 abuying 的回复:
如果是2005的话，可以使用row_number()
--2000,须有结构与数据来测试
uid class
----------- -----------
6 6
5 5
4 4
3 3
2 2
1 1
按uid升序，class降序，想得到下列结果
uid class
----------- -----------
1 6
……
[/Quote]

这已经不是排序了，是重新构造数据了

[siegebaoniu]

哎，sql2000.....

[Metrosexual]

学习下

[fengyun142415]

应该可以啊

[siegebaoniu]

/*
if object_id('tempt') is not null drop table tempt
--go
create table tempt (uid int,class int)
insert tempt 
select 6, 1 union all
select 5, 2 union all
select 4, 3 union all
select 3, 4 union all
select 2, 5 union all
select 1, 6 

*/

DECLARE @tmp_a TABLE (id int identity(1,1),uid int )
insert @tmp_a
select uid from tempt order by uid

DECLARE @tmp_b TABLE (id int identity(1,1),class int )
insert @tmp_b
select class from tempt order by class desc


select a.uid,b.class
from @tmp_a a
inner join @tmp_b b on a.id=b.id

/*
1	6
2	5
3	4
4	3
5	2
6	1
*/

[andy_liucj]

select Sh_dTGold.Tid,Sh_dTGold.Tprice,Sh_dTGold.Tquantity,Sh_dTGold.Tstate,Sh_Game.Gname,Sh_Server.Sname  
from Sh_TGold,Sh_Game,Sh_Server  
where Sh_dTGold.Gid = Sh_Game.Gid  
and Sh_dTGold.Sid = Sh_Server.Sid  
and Sh_dTGold.Gid =1 order by Sh_Server.Sname, Tquantity desc

说个跟排序无关的
Sh_dTGold.Gid = Sh_Game.Gid
and Sh_dTGold.Sid = Sh_Server.Sid
and Sh_dTGold.Gid =1

不就是
Sh_Game.Gid=1 and
Sh_Server.Sid =1 and
Sh_dTGold.Gid =1

[jianwensheng]

什么咯，优先第一个的排序，第二个字段只会在第一个字段的值相同的时候才会有效果