mysql - 如何在mysql中获取计数以及表的所有行？

Question

这是我的查询:

select location_id, location_name, display_name from location ;

结果是:

+---------------+------------------------------------+----------------+
| location_id   | location_name                      | display_name   |
+---------------+------------------------------------+----------------+
|             1 | L1 PQR MULTIPLEX AND ENTERTAINMENT | L1 PQR         |
|             2 | Cinepolis                          | Cinepolis KP   |
|             3 | PVR FORUM MALL                     | PVR KP         |
|           333 | PRASHANTH CINEMAS                  | PRASHANTH MP   |
|          4555 | RAVI CINEMAS                       | RAVI KP        |
|        323213 | ASIAN GPR MULTIPLEX                | ASIAN KPHB     |
| 5000721013770 | PVR CENTRAL                        | PVR PUNJAGUTTA |
| 5000721017325 | PVR INORBIT                        | PVR HITECH     |
| 5000981019820 | TIVOLI CINEMAS                     | TIVOLI SC      |
| 5300181011396 | Central Panjaguttaddd              | ddd            |
+---------------+------------------------------------+----------------+
10 rows in set (0.00 sec)

我还需要在这个查询中使用 LIMIT 10 的计数，我已经尝试过

select count(*) as count, location_id, location_name, display_name from location limit 10;

结果是:

+-------+-------------+------------------------------------+--------------+
| count | location_id | location_name                      | display_name |
+-------+-------------+------------------------------------+--------------+
|    50 |           1 | L1 PQR MULTIPLEX AND ENTERTAINMENT | L1 PQR       |
+-------+-------------+------------------------------------+--------------+
1 row in set (0.00 sec)

为什么它只获取一条记录？？

如何获取计数和记录？？

Answer 1

select 中的count(*) 将查询转换为聚合查询。如果没有 group by，这将只返回一行。实际上，在大多数数据库中，它会返回一个错误，因为 SQL 引擎不知道如何处理剩余的列。在 MySQL 中，它们获得不确定的值。

如果你想要每一行的总计数，我建议你使用连接来获得结果:

select lc.cnt as count, l.location_id, l.location_name, l.display_name
from location l cross join
     (select count(*) as cnt from location) lc
limit 10;

如果您真的想要在每一行上有一个序号，那么您就误解了 count()。为此，使用一个变量:

select (@rn := @rn + 1) as count, l.location_id, l.location_name, l.display_name
from location l cross join
     (select @rn := 0) vars
limit 10;