GROUP_CONCAT 使用限制排序方式 "where子句"中的未知列"p.id"

Question

这是我的表格:

+----+---------+------------+----------+
| id | message | projectID  | noteType |
+----+---------+------------+----------+
|  1 |       1 |    125 |   update |
|  2 |       2 |    125 |   update |
|  3 |       3 |    125 |   update |
|  4 |       4 |    125 |   update |
|  5 |       5 |    125 |   update |
|  6 |       6 |    125 |   update |

我的查询使用以下建议:

SELECT `p`.`id`, `proName`, `p`.`proType`, `p`.`priority`, 
`p`.`busSegment`, `p`.`portfolio`, `p`.`description`, 
(SELECT group_concat('<li>', `message`, '</li>') AS temp FROM (SELECT 
projectID, message FROM notes where projectID = p.id AND noteType = 
'update' ORDER BY id DESC LIMIT 3) three_messages GROUP BY projectID) as 
updates
FROM `projects` as `p` 
WHERE `p`.`id` = 125

错误:

Error Code: 1054. Unknown column 'p.id' in 'where clause'

返回所有记录。由于某些原因，LIMIT 3不能工作。

建议查询可自行完成。

SELECT group_concat('<li>', `message`, '</li>') AS updates
FROM (
SELECT projectID, message
FROM notes  where projectID = 125 AND noteType = 'update'
ORDER BY id DESC
LIMIT 3
) three_messages
GROUP BY projectID;
+---------------------+
| updates             |
+---------------------+
| 6,5,4               |
+---------------------+

Answer 1

我猜你有超过项目id = 125在你的实际查询和每一个你想要3个结果。在这种情况下，你可能需要做一些排序。我在这里用row_number()函数来做。

这里是小提琴https://www.db-fiddle.com/f/uqSzoth466RX86c5dCkfDR/0

with t as(select a.*,
row_number() over
(partition by project_id order by message desc) as rn
from mytable a
where note_type = 'update')
SELECT project_id,
group_concat('<li>', `message`, '</li>' ORDER BY id DESC) AS updates
from t where rn <=3 group by project_id;

Answer 2

LIMIT适用于分组后的行。

您可以在子查询中使用它并对该子查询的结果进行分组:

SELECT group_concat('<li>', `message`, '</li>' ORDER BY id DESC) AS updates
FROM (
SELECT id, projectID, message
FROM notes  where projectID = 125 AND noteType = 'update'
ORDER BY id DESC
LIMIT 3
) three_messages
GROUP BY projectID

在我看来你确实想要:

group_concat('<li>', `message`, '</li>' ORDER BY id DESC SEPARATOR '')

，而不是在列表项之间放逗号。

我也认为使用id来排序是一个坏主意;如果您想要最新的笔记，请使用时间戳。想象一下，如果您的数据库被黑客入侵，您必须从备份中进行恢复。恢复之后，再次启动系统并添加注释。然后查看应用程序日志并恢复丢失的一些注释;如果要对id进行排序，则必须增加恢复后添加的所有id，以便为恢复后的id腾出空间。如果能插入一个时间戳，那就更好了。