玛丽亚DB组由id DESC与在魔法

Question

尝试在"GROUP BY"中获取"ORDER BY id DESC"以仅获取最后一条注释。 当我将"IN"与>1 个元素一起使用时，它可以正常工作，但使用一个元素或没有"IN"。 我的MariaDB版本是10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04。

以下是示例：56xxx - 真 id，55xxx 假 ID

MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid`='6576') GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 55811 |
+-------+
1 row in set (0.00 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6576')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 55811 |
+-------+
1 row in set (0.00 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6576','6576')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 56218 |
+-------+
1 row in set (0.00 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6576','-1')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 56218 |
+-------+
1 row in set (0.01 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6576')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 55811 |
+-------+
1 row in set (0.00 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6576','6577')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 56199 |
| 56218 |
+-------+
2 rows in set (0.00 sec)
MariaDB [shop]> SELECT `commentsapi_comment`.`id` FROM `commentsapi_comment`
WHERE (`commentsapi_comment`.`orderid` IN ('6577')) GROUP BY orderid DESC;
+-------+
| id    |
+-------+
| 55813 |
+-------+
1 row in set (0.01 sec)

谁知道这其中的原因呢？

Answer 1

这是对GROUP BY的无效使用。 较新的版本会向你吐口水。

当SELECT(例如，id(中存在不在GROUP BY(只有orderid(中的非聚合列时，所选项目会随机选择。

建议您将id更改为

orderid, MIN(id), MAX(id), COUNT(*), GROUP_CONCAT(id)

IN是红鲱鱼，不是原因。

如果您需要所有列，而不仅仅是id，则需要在问题中说明。 有很多关于"grouwise-max"的问答。