15,076
edits
排除 MySQL 技術疑難問題: Difference between revisions
Jump to navigation
Jump to search
→錯誤 1235: 這個版本的 MySQL 尚未支持 'LIMIT & IN/ALL/ANY/SOME 子查詢'
m (→SQL 和查詢錯誤) |
|||
| Line 311: | Line 311: | ||
$ file plain_text.sql | $ file plain_text.sql | ||
plain_text.sql：UTF-8 Unicode 文本，帶有非常長的行 | plain_text.sql：UTF-8 Unicode 文本，帶有非常長的行 | ||
</pre> | |||
=== 錯誤 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column === | |||
訊息 | |||
<pre> | |||
查詢發生錯誤 (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by | |||
</pre> | |||
遇到錯誤的查詢 | |||
<pre> | |||
-- 建立範例資料表 | |||
CREATE TABLE posts ( | |||
id INT, | |||
post_id VARCHAR(10), | |||
author VARCHAR(50), | |||
content TEXT, | |||
likes INT, | |||
post_time DATETIME | |||
); | |||
-- 插入範例資料 | |||
INSERT INTO posts VALUES | |||
(1, 'A123', 'Alice', '第一篇文章', 10, '2024-01-01'), | |||
(2, 'A123', 'Bob', '推推', 5, '2024-01-02'), | |||
(3, 'A123', 'Bob', '讚讚', 3, '2024-01-03'); | |||
</pre> | |||
解決方案：修正後的查詢 (1) SELECT 中的每個欄位要麼放在 GROUP BY 中、或 (2) 使用彙總函數 (如 MAX, MIN, SUM, COUNT, GROUP_CONCAT) | |||
<pre> | |||
-- 方法一: 把所有欄位加入 GROUP BY | |||
SELECT | |||
id, | |||
post_id, | |||
author, | |||
content, | |||
likes | |||
FROM posts | |||
GROUP BY post_id, author, id, content, likes; | |||
-- 方法二: 對非 GROUP BY 的欄位使用彙總函數 | |||
SELECT | |||
MAX(id) as id, | |||
post_id, | |||
author, | |||
GROUP_CONCAT(content) as contents, | |||
SUM(likes) as total_likes | |||
FROM posts | |||
GROUP BY post_id, author; | |||
</pre> | </pre> | ||