MySQL选择查询结果错误与COUNT（）和

Vickysw：

在Mysql中，我使用COUNT（）聚合函数统计了用于分页的总计记录，并且出于显示数据的目的，我使用的是*而不是COUNT（），但是我得到了不同的总计和返回的行数。

查询1：

SELECT
  COUNT(*)
FROM
  change_request
INNER JOIN task_main_management ON cr_project_id = project_id
LEFT JOIN all_module AS am
ON
  am.m_id = task_m_id
WHERE
  cr_tenent_id = '100' AND cr_entity_id = '100' AND cr_project_id = 'PROJ_310820200746367254' AND cr_type = '2' AND change_request.cr_id = '16' AND task_main_management.cr_id = 'CHRQ_020920200807413904'
GROUP BY
  change_request.cr_project_id

返回结果为2。

但是当我在选择中使用*而不是COUNT（*）时，它给了我单行。

鲑鱼

这是预期的行为。

ariaDB [sandbox]> select * from t;
+------+--------------------+------------+------------+----------+
| id   | venue              | city       | date       | time     |
+------+--------------------+------------+------------+----------+
|    1 | Waldorf Hotel      | London     | 2020-01-01 | 07:00:00 |
|    2 | Waldorf Hotel      | London     | 2020-01-02 | 07:00:00 |
|    3 | Heathrow           | London     | 2020-01-02 | 14:00:00 |
|    4 | Lennon Airport     | Liverpool  | 2020-01-02 | 16:00:00 |
|    5 | Port of Liverpool  | Liverpool  | 2020-01-02 | 19:30:00 |
|    6 | Port of Liverpool  | Liverpool  | 2020-01-03 | 07:00:00 |
|    7 | Port of Liverpool  | Liverpool  | 2020-01-04 | 07:00:00 |
|    8 | Port of Liverpool  | Liverpool  | 2020-01-05 | 07:00:00 |
|    9 | Port of Liverpool  | Liverpool  | 2020-01-06 | 07:00:00 |
|   10 | Manchester Airport | Manchester | 2020-01-06 | 12:40:00 |
|   11 | Heathrow           | London     | 2020-01-06 | 14:40:00 |
+------+--------------------+------------+------------+----------+
11 rows in set (0.001 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM T WHERE CITY = 'LONDON' GROUP BY CITY;
+------+---------------+--------+------------+----------+
| id   | venue         | city   | date       | time     |
+------+---------------+--------+------------+----------+
|    1 | Waldorf Hotel | London | 2020-01-01 | 07:00:00 |
+------+---------------+--------+------------+----------+
1 row in set (0.001 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT COUNT(*)
    -> FROM T
    -> WHERE CITY = 'LONDON'
    -> GROUP BY CITY;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.001 sec)

没有聚合函数的group by将返回1行非确定值；具有聚合函数的group by将返回1行，但没有要聚合的列将返回where子句过滤的行的计数。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。