取出数量最多的行 排在前面 mysql 语句 网友教的 有大用

CREATE TABLE `users` (

  `id` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `area` varchar(50) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`, `area`) VALUES

(1, '张三', '河南'),

(2, '李四', '河北'),

(3, '王五', '河南'),

(4, '赵六', '陕西'),

(5, '孙七', '河南'),

(6, '李八', '陕西');

ALTER TABLE `users`

  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

1.select  * from  users  ORDER BY  FIELD(area,'河南','陕西','河北') asc;

2.(select group_concat(area) from (select area from users  group by area order by count(*) desc  ) tb1)

也返回了   河南,陕西,河北

用2替换1中的汉字  不行

3. select  * from  users  ORDER BY  area in((select group_concat(area) from (select * from users  group by area order by count(*) asc  ) as tb1)) ;

下面的 php 代码 执行肯定是正确的

<?php

$servername = "localhost";

$username = "root";

$password = "123456";

$dbname = "test";

// 创建连接

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("连接失败: " . $conn->connect_error);

}

$sql = "select  group_concat(area) as area  from ((select concat(\"'\",area,\"'\") as area from users  group by area order by count(*) desc)) tb1";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    //查询到orderby条件

    $res = $result -> fetch_all(MYSQLI_ASSOC);

    $area=$res['0']['area'];

    //再次查询

    $sql="select  * from  users  ORDER BY  FIELD(area,$area) asc";

    $result = $conn->query($sql);

    $res = $result -> fetch_all(MYSQLI_ASSOC);

    var_dump($res);

}

$conn->close();