这个select查询排序要怎么实现？(100)

N

NewLearner

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-27

• #1

我要实现一个查询当 字段a=0时 id正排序，字段a<>0时 id 倒排序 的一个查询集合要求排序后的记录如下：id 字段a1　　05　　07　　08　　09　　46　　34　　23　　52　　1

 

N

NewLearner

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-28

• #2

楼上的答非所问。完全没理解我的问题。

 

E

Ekin

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-27

• #3

bs

 

W

wfymqj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-28

• #4

不知道用几条语句完成可以不CREATE VIEW TempMeterasselect id,字段a 表1 where (字段a=0) order by idINSERT INTO TempMeter (id,字段a)select id,字段a 表1 where (字段a<>0) order by id DESCselect * from TempMeter

 

S

szhcracker

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-28

• #5

Select id, a From t1 Where a = 0 Order By idUnion AllSelect id, a From t1 Where a <> 0 Order By id Desc

 

Z

zbdzjx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-28

• #6

很简单：首要条件，id>0。order by case when 字段a =0 then 0 else 1,case when 字段a =0 then id else 1/id

 

S

skydrag

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-28

• #7

上面写过的几种方法都基本上都可以实现，我也来写一段！select ID,字段a FROM (select case when 字段a =0 then id else 1/id end as TmpOrd,id ,字段a，ID from 表名)TmpTable order by 字段a,TmpOrd

 

Z

zkktom

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-29

• #8

如果出现－1呢 我楼上的就错了吧

 

H

HoyCoolboy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-29

• #9

同意szhcracker

 

X

xnnfan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-29

• #10

来晚了一步，来自：szhcracker, 时间：2009-9-28 16:45:16, ID：3977676Select id, a From t1 Where a = 0 Order By idUnion AllSelect id, a From t1 Where a <> 0 Order By id Desc 支持 szhcracker的说法All可以根据自已的要求用与不用

 

X

xiaoxiangqing

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-29

• #11

select id,字段a from tablea order by case when 字段a =0 then 0 else 1,case when 字段a =0 then id else -id end