php - 在mysql中首先对varchar整数进行排序

Question

我编写此代码是为了在 html 表中显示 wordpress 数据库表中的数据:

global $wpdb;
$table_name = $wpdb->prefix . 'mytable';
$stats = $wpdb->get_results("SELECT * FROM $table_name ORDER BY cast(alexacr as UNSIGNED) ASC");
$itemnum = $wpdb->get_var("SELECT COUNT(id) FROM $table_name");
echo '<span style="font-family: sans-serif;position:relative;float: left;font-size: 18px;"><em>'.$itemnum.' Sites</em></span>';
echo '<table>';
echo '<thead>';
echo '<th>Nome &amp; Link</th><th>Alexa CR</th>';
echo '</thead>';
echo '<tbody>';
foreach ($stats as $stat){
echo'<tr>';
echo '<td><a title="'.str_replace('_','',$stat->name).'" href="'.$stat->url.'" target="_blank">'.str_replace('_','',$stat->name).'</a></td><td>'.$stat->alexacr.'</td>';
echo'</tr>';
 }
echo '</tbody>'; 
echo '</table>';

alexacr 列设置为 varchar。alexacr 列的结果如下:

N/A
1
2
3
4

我怎样才能只对数字进行排序？

1
2
3
4
N/A

谢谢。

Answer 1

尝试

  SELECT 
        *
  FROM 
        Table1
  Order By 
        CASE WHEN alexacr like 'N/A' then 1 else 0 end ASC,
        LPAD(alexacr, 20, '0') ASC

演示地址:http://sqlfiddle.com/#!2/210007/1

有关 LPAD 的更多详细信息，请访问 http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad