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

sql查询的问题?

gaopei1122 2008-01-23 02:44:21
表结构
a b
1 5
0 2
1 4
1 7
0 11
如上表,我想查询的结果是,先把a=1的列安b的降序排列,后面在把a<>1的列安b降序排列,
这个语句改怎么写呀
...全文
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
先把a=1的列安b的降序排列,后面在把a <> 1的列安b降序排列,

---

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
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告