Mysql排序问题:某数值字段的记录值有0也有其它整数,在SQL语句排序时怎么把这个字段值数字较小但不是0的记录排在前面,是0的放在后面,
例如,表:
id fieldValue
1 0
2 4
3 2
4 3记录集排序后顺序是
id fieldValue
3 2
4 3
2 4
1 0向高手请教怎么实现?
例如,表:
id fieldValue
1 0
2 4
3 2
4 3记录集排序后顺序是
id fieldValue
3 2
4 3
2 4
1 0向高手请教怎么实现?
--测试数据
use
SQL_Test
go
create table tt(
id bigint not null primary key,
fieldValue bigint not null
)
insert into tt
select '1','0'
union all
select '2','4'
union all
select '3','2'
union all
select '4','3'--查询
select * from tt order by fieldValue desc--结果
2 4
4 3
3 2
1 0
use
SQL_Test
go
create table tt(
id bigint not null primary key,
fieldValue bigint not null
)
insert into tt
select '1','0'
union all
select '2','4'
union all
select '3','2'
union all
select '4','3'
--这样建立临时表就可以了,呵呵我只能想到这个了。
exec('create table #temp(
id bigint not null,
fieldValue bigint not null
)
insert into #temp
select * from tt where fieldValue<>0 order by fieldValue
insert into #temp select * from tt where fieldValue=0
select * from #temp
drop table #temp')
应该是
select * from 表 order by fieldValue=0, fieldValue测试代码
$conn = mysql_connect();
mysql_select_db('test');$sql = <<< SQL
create table tbl (
id bigint not null primary key,
fieldValue bigint not null
)
SQL;
//mysql_query($sql) or die(mysql_error());$sql = <<< SQL
insert into tbl (id, fieldValue) values (1, 0),
(2, 4),
(3, 2),
(4, 3)
SQL;
//mysql_query($sql) or die(mysql_error());$rs = mysql_query("select * from tbl order by fieldValue=0, fieldValue");
while($row = mysql_fetch_assoc($rs)) {
echo "$row[id] $row[fieldValue] <br />";
}3 2
4 3
2 4
1 0