这个问题的确有难度，
1、需要产生递增的编号用来表示名次
2、相同的数值要有相同的名次
3、本来一个select * from table order by a desc取出后用程序循环产生名次即可。但楼主要求使用mysql语句完成
MYSQL：先建表、插入数据
CREATE TABLE test4 (
id tinyint(4) NOT NULL auto_increment,
a tinyint(4) NOT NULL default '0',
UNIQUE KEY id (id)
) TYPE=MyISAM;
INSERT INTO test4 VALUES (1, 2);
INSERT INTO test4 VALUES (2, 6);
INSERT INTO test4 VALUES (3, 4);
INSERT INTO test4 VALUES (4, 9);
INSERT INTO test4 VALUES (5, 5);
INSERT INTO test4 VALUES (6, 0);
INSERT INTO test4 VALUES (7, 6);
INSERT INTO test4 VALUES (8, 5);
INSERT INTO test4 VALUES (9, 0);
DROP TABLE testtemp; # 删除临时表
CREATE TABLE testtemp ( # 创建临时表
id tinyint(4) NOT NULL auto_increment,
a tinyint(4) NOT NULL default '0',
UNIQUE KEY id (id)
) TYPE=MyISAM;
insert into testtemp select DISTINCT '',a from test4 order by a desc; # 向临时表插入不重复数据数据，利用自增字段产生编号
select b.id as level,a.id,a.a from test4 a,testtemp b where a.a=b.a; # 连接两表产生输出
PHP:
<?php
$sql = array(
"DROP TABLE testtemp",
"CREATE TABLE testtemp (
id tinyint(4) NOT NULL auto_increment,
a tinyint(4) NOT NULL default '0',
UNIQUE KEY id (id)
) TYPE=MyISAM;
",
"insert into testtemp select DISTINCT '',a from test4 order by a desc",
"select b.id as level,a.id,a.a from test4 a,testtemp b where a.a=b.a"
);
$conn = mysql_connect();
mysql_select_db("test");
foreach($sql as $v)
$rs = mysql_query($v);
mysql_result_all($rs);
/** 仿odbc_result_all函数 **/
function mysql_result_all($result,$format="") {
echo "<table $format><tr>";
for($i=0;$i<mysql_num_fields($result);$i++) {
echo "<th>".mysql_field_name($result,$i)."</th>";
}
echo "</tr>";
while($row = mysql_fetch_row($result)) {
echo "</tr>";
for($i=0;$i<mysql_num_fields($result);$i++) {
echo "<td>".$row[$i]."</td>";
}
echo "</tr>";
}
echo "</table>";
}
?>
输出结果:
level id a
1 4 9
2 2 6
2 7 6
3 5 5
3 8 5
4 3 4
5 1 2
6 6 0
6 9 0
