sql - 在SQL中，为什么 "select *, count(*) from sentGifts group by whenSent;"是可以的，但是当 "*"和 "count(*)"调换位置时，​​就报错了？

Question

在 SQL 中，使用表:

mysql>  select * from sentGifts;
+--------+------------+--------+------+---------------------+--------+
| sentID | whenSent   | fromID | toID | trytryWhen          | giftID |
+--------+------------+--------+------+---------------------+--------+
|      1 | 2010-04-24 |    123 |  456 | 2010-04-24 01:52:20 |    100 |
|      2 | 2010-04-24 |    123 | 4568 | 2010-04-24 01:56:04 |    100 |
|      3 | 2010-04-24 |    123 | NULL | NULL                |      1 |
|      4 | 2010-04-24 |   NULL |  111 | 2010-04-24 03:10:42 |      2 |
|      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |      6 |
|      6 | 2010-04-24 |     11 |  222 | 2010-04-24 03:54:49 |      6 |
|      7 | 2010-04-24 |      1 |    2 | 2010-04-24 03:58:45 |      6 |
+--------+------------+--------+------+---------------------+--------+
7 rows in set (0.00 sec)

以下是可以的:

mysql>  select *, count(*) from sentGifts group by whenSent;
+--------+------------+--------+------+---------------------+--------+----------+
| sentID | whenSent   | fromID | toID | trytryWhen          | giftID | count(*) |
+--------+------------+--------+------+---------------------+--------+----------+
|      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |      6 |        1 |
|      1 | 2010-04-24 |    123 |  456 | 2010-04-24 01:52:20 |    100 |        6 |
+--------+------------+--------+------+---------------------+--------+----------+
2 rows in set (0.00 sec)

但假设我们希望 count(*) 出现在第一列:

mysql>  select count(*), * from sentGifts group by whenSent;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from sentGifts group by whenSent' at line 1

它给出了一个错误。为什么会这样，有什么解决方法？

我意识到这是可以的:

mysql>  select count(*), whenSent from sentGifts group by whenSent;
+----------+------------+
| count(*) | whenSent   |
+----------+------------+
|        1 | 2010-03-03 |
|        6 | 2010-04-24 |
+----------+------------+
2 rows in set (0.00 sec)

但是上面给出错误的那个呢？谢谢。

Answer 1

我不确定为什么会报错，但是如果你这样写:

 SELECT COUNT(*), sentGifts.* FROM sentGifts GROUP BY whenSent

它不会给出那个错误。

即使没有分组依据，它也会因同样的错误而失败:

 SELECT whenSent, * FROM sentGifts 

根据documentation :

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference

所以我的猜测是一个非限定的 * 如果它出现必须紧跟在 SELECT 关键字之后。一般来说，你不应该这样做。它可能会在更高版本的 MySQL 中停止工作。

我还建议您阅读 documentation关于在使用 GROUP BY 时选择非聚合列:

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

所以你的结果是不确定的。不要这样做。

同时选择 * 是一种不好的做法。您应该明确列出您需要的列，即使是所有列。