在一个表中有photo字段,可能为空,也可能不为空,要求一条sql语句,能够先显示字段不为空的记录,然后列出字段为空的记录,
如
id photo name number
1 jack 123
2 shmit 789
3 pic\pic.gif join 456要求结果显示 pic\pic.gif join 456
jack 123
shmit 789
如
id photo name number
1 jack 123
2 shmit 789
3 pic\pic.gif join 456要求结果显示 pic\pic.gif join 456
jack 123
shmit 789
insert tab
select 1,'','jack','123' union all
select 2,'','shmit','789' union all
select 3,'pic\pic.gif','join','456'
select * from tab order by photo desc
drop table tabid photo name number
----------- -------------------- ---------- ------
3 pic\pic.gif join 456
1 jack 123
2 shmit 789
select * from AA and photo<>''
union all
select * from AA where and photo=''
感觉很弱智
insert tab
select 1,'','jack','123' union all
select 2,'','shmit','789' union all
select 3,'pic\pic.gif','join','456'
select * from tab where photo <>'' union all
select * from tab where photo=''
drop table tabid photo name number
----------- -------------------- ---------- ------
3 pic\pic.gif join 456
1 jack 123
2 shmit 789(3 行受影响)
create table #T(id int,photo varchar(11),name varchar(5),number int)
insert #T
select 1,'','jack',123 union all
select 2,'','shmit',789 union all
select 3,'pic\pic.gif','join',456--查询成本:39.43%
select * from #T order by case photo when '' then 1 else 0 end--查询成本:60.57%
select * from #T where photo<>''
union all
select * from #T where photo=''--删除测试
drop table #T
create table #T(id int,photo varchar(11),name varchar(5),number int)
insert #T
select 1,'','jack',123 union all
select 2,'','shmit',789 union all
select 3,'pic\pic.gif','join',456--查询成本:39.43%
select * from #T order by case photo when '' then 1 else 0 end--查询成本:60.57%
select * from #T where photo<>''
union all
select * from #T where photo=''--删除测试
drop table #T
我上MSN怎么没见到你.牛!!!!!!!
这样也可以!