**
mysql数据库对自定义排序的写法
**
create table hz_temp(
id int primary key not null,
name varchar(32),
age int,
hobby varchar(32)
)
insert into hz_temp(id,name,age,hobby) values(1,‘张三’,15,‘打篮球’)
insert into hz_temp(id,name,age,hobby) values(2,‘张三’,15,‘打羽毛球’)
insert into hz_temp(id,name,age,hobby) values(3,‘李四’,14,‘打乒乓球’)
insert into hz_temp(id,name,age,hobby) values(4,‘王五’,16,‘写作’)
insert into hz_temp(id,name,age,hobby) values(5,‘王五’,16,‘唱歌’)
执行SQL:
select name,age,hobby from hz_temp
系统默认的排序:
自定义排序执行SQL:
select name,age,hobby from hz_temp order by field(hobby,‘唱歌’,‘打乒乓球’,‘打篮球’,‘写作’,‘打羽毛球’)
运行结果:
**
db2数据库对自定义排序的写法
**
create table hz_temp(
id int primary key not null,
name varchar(32),
age int,
hobby varchar(32)
)
insert into hz_temp(id,name,age,hobby) values(1,‘张三’,15,‘打篮球’)
insert into hz_temp(id,name,age,hobby) values(2,‘张三’,15,‘打羽毛球’)
insert into hz_temp(id,name,age,hobby) values(3,‘李四’,14,‘打乒乓球’)
insert into hz_temp(id,name,age,hobby) values(4,‘王五’,16,‘写作’)
insert into hz_temp(id,name,age,hobby) values(5,‘王五’,16,‘唱歌’)
执行SQL语句:
select name,age,hobby from hz_temp order by decode(hobby,‘唱歌’,‘1’,‘打乒乓球’,‘2’,‘打篮球’,‘3’,‘写作’,‘4’,‘打羽毛球’,‘5’)
运行结果: