• 主页
• 基础类
• 应用实例
• 新技术前沿

# sql查询的问题？

gaopei1122 2008-01-23 02:44:21

a b
1 5
0 2
1 4
1 7
0 11

...全文
41 点赞 收藏 10

10 条回复

gaopei1122 2008-01-23

yangjiexi 2008-01-23
``````
declare @tb table(a int,b int)
insert into @tb
select 1,5
union
select 0,2
union
select 1,4
union
select 1,7
union
select 0,11

select * from @tb
order by case when a=1 then 0 else 1 end,b desc

``````

tre_sdlpq 2008-01-23

a b
1 5
0 2
1 4
1 7
0 11

select * from 表
where a=1
order by b desc

union
select * from 表
where a <> 1
order by b desc

-狙击手- 2008-01-23
``````declare @t table(a int,b int)
insert @t select
1,     5 union select
0,     2 union select
1 ,    4 union select
1   ,  7 union select
0    , 11

-- a=1 asc ,a<> 1 desc
select *
from @t
order by case when a = 1 then  -b else b end desc

/*
a           b
----------- -----------
0           11
0           2
1           4
1           5
1           7

（所影响的行数为 5 行）
*/``````

ORARichard 2008-01-23
``select   *   from   tb   order   by   abs(1-a),b   desc --不用那个负号``

-狙击手- 2008-01-23

－－－

B　全降序　，　我还以为是a = 1　时升，＜＞　1　时降呢

kk19840210 2008-01-23
``````
declare @t table (a int,b int)
insert into @t values(1,5)
insert into @t values(0,2)
insert into @t values(1,4)
insert into @t values(1,7)
insert into @t values(0,11)

select * from
(
select top 100 PERCENT * from @t where a=1 order by b desc
) a
union all
select * from
(
select top 100 PERCENT * from @t where a<>1 order by b
) b

a           b
----------- -----------
1           5
1           4
1           7
0           2
0           11

(5 行受影响)
``````

-狙击手- 2008-01-23

declare @t table(a int,b int)
insert @t select
1, 5 union select
0, 2 union select
1 , 4 union select
1 , 7 union select
0 , 11

``````select *
from @t
order by case when a = 1 then  0 else 1 end ,b desc

/*

a           b
----------- -----------
1           7
1           5
1           4
0           11
0           2

（所影响的行数为 5 行）

*/``````

ORARichard 2008-01-23
select * from tb order by -abs(1-a),b desc

-狙击手- 2008-01-23
``````select *
from t
order by case when a = 1 then 9999999 - b else b end desc``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区