Mysql

錯誤：列“欄位”必須出現在 GROUP BY 子句中或在聚合函式中使用

  • June 12, 2018

我遇到了一個讓我有點迷惑的 SQL 問題。關於這個問題，我有兩個不同的問題：

  • 為什麼？
  • 我該如何解決它？

以下查詢：

SELECT subs.date_added,subs.subscribed_to,users.userid, users.username, users.email, users.avatar, users.fbuid, users.level, users.avatar_url, users.sex, users.dob, users.profile_hits, users.total_videos, users.subscribers, users.doj, users.extras, users.first_name, users.last_name, users.ban_status, users.usr_status, users.last_logged, users.country, users.user_filter_level, users.signup_ip, subs.userid as subscriber 
FROM cb_subscriptions as subs 
    LEFT JOIN cb_users AS users 
    ON subs.userid=users.userid 
WHERE subs.subscribed_to ='2960' 
GROUP BY subs.subscribed_to ,subs.userid 
ORDER BY subs.date_added ASC 
LIMIT 8;

…產生以下錯誤：

ERROR: column "users.userid" must appear in the GROUP BY clause or be used in an aggregate function

該欄位是 a CHAR，所以我不確定要使用哪種聚合，或者（對我的理解更重要）為什麼需要聚合。

正如我所說，我不確定我理解為什麼會發生這種情況。我假設我不像我想像的那樣理解group by;)

這不是我的查詢，我正在將 MySQL 中的原型系統轉換為 PostgreSQL。此查詢在 MySql*中確實有效。*這是否構成 MySql 中的錯誤，或 PostgreSQL 的缺點，或者只是解釋的差異？

當我在group by子句中添加此列時，它正在工作，但我不明白為什麼？

工作查詢

SELECT subs.date_added, users.userid, users.username, users.email, 
      users.avatar, users.fbuid, users.level, users.avatar_url, 
      users.sex, users.dob, users.profile_hits, users.total_videos,
      users.subscribers, users.doj, users.extras, users.first_name, 
      users.last_name, users.ban_status, users.usr_status, 
      users.last_logged, users.country, users.user_filter_level, 
      users.signup_ip, subs.userid as subscriber 
FROM cb_subscriptions as subs 
    LEFT JOIN cb_users AS users 
    ON subs.userid=users.userid 
WHERE subs.subscribed_to ='2960' 
GROUP BY users.userid, 
        subs.subscribed_to, 
        subs.userid, 
        subs.date_added 
ORDER BY subs.date_added ASC 
LIMIT 8;

cb_subscriptions DDL

CREATE TABLE `cb_subscriptions` (
   `subscription_id` int(225) NOT NULL,
   `userid` int(11) NOT NULL,
   `subscribed_to` mediumtext NOT NULL,
   `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `cb_subscriptions`
 ADD PRIMARY KEY (`subscription_id`);

ALTER TABLE `cb_subscriptions`
 MODIFY `subscription_id` int(225) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1

cb_users DDL

CREATE TABLE `cb_users` (
 `userid` bigint(20) NOT NULL,
 `fbuid` varchar(225) NOT NULL,
 `category` int(20) NOT NULL,
 `username` varchar(50) NOT NULL,
 `first_name` varchar(200) NOT NULL,
 `last_name` varchar(200) NOT NULL,
 `user_session_key` varchar(32) NOT NULL,
 `user_session_code` int(5) NOT NULL,
 `password` varchar(40) NOT NULL DEFAULT '',
 `email` varchar(80) NOT NULL DEFAULT '',
 `usr_status` enum('Ok','ToActivate') NOT NULL DEFAULT 'ToActivate',
 `msg_notify` enum('yes','no') NOT NULL DEFAULT 'yes',
 `avatar` varchar(225) NOT NULL DEFAULT '',
 `avatar_url` text NOT NULL,
 `sex` enum('male','female') NOT NULL DEFAULT 'male',
 `dob` date NOT NULL DEFAULT '0000-00-00',
 `country` varchar(20) NOT NULL DEFAULT 'PK',
 `level` int(6) NOT NULL DEFAULT '2',
 `avcode` varchar(32) NOT NULL,
 `doj` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `last_logged` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `num_visits` bigint(20) NOT NULL DEFAULT '0',
 `session` varchar(32) NOT NULL DEFAULT '',
 `ip` varchar(15) NOT NULL DEFAULT '',
 `signup_ip` varchar(15) NOT NULL DEFAULT '',
 `time_zone` tinyint(4) NOT NULL DEFAULT '0',
 `featured` enum('No','Yes') NOT NULL DEFAULT 'No',
 `featured_date` datetime NOT NULL,
 `profile_hits` bigint(20) DEFAULT '0',
 `total_watched` bigint(20) NOT NULL DEFAULT '0',
 `total_videos` bigint(20) NOT NULL,
 `total_comments` bigint(20) NOT NULL,
 `total_photos` bigint(255) NOT NULL,
 `total_collections` bigint(255) NOT NULL,
 `comments_count` bigint(20) NOT NULL,
 `last_commented` datetime NOT NULL,
 `voted` text NOT NULL,
 `likes` decimal(5,0) NOT NULL,
 `likes_count` int(6) NOT NULL,
 `ban_status` enum('yes','no') NOT NULL DEFAULT 'no',
 `upload` varchar(20) NOT NULL DEFAULT '1',
 `subscribers` bigint(225) NOT NULL DEFAULT '0',
 `total_subscriptions` bigint(255) NOT NULL,
 `background` mediumtext NOT NULL,
 `background_color` varchar(25) NOT NULL,
 `background_url` text NOT NULL,
 `background_repeat` enum('no-repeat','repeat','repeat-x','repeat-y') NOT NULL DEFAULT 'repeat',
 `background_attachement` enum('yes','no') NOT NULL DEFAULT 'no',
 `total_groups` bigint(20) NOT NULL,
 `last_active` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `banned_users` text NOT NULL,
 `welcome_email_sent` enum('yes','no') NOT NULL DEFAULT 'no',
 `total_downloads` bigint(255) NOT NULL,
 `is_subscribed` enum('yes','no') NOT NULL DEFAULT 'no',
 `album_privacy` enum('public','private','friends') NOT NULL DEFAULT 'private',
 `extras` text NOT NULL,
 `user_filter_level` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `cb_users`
 ADD PRIMARY KEY (`userid`),
 ADD KEY `ind_status_doj` (`doj`),
 ADD KEY `ind_status_id` (`userid`),
 ADD KEY `ind_hits_doj` (`profile_hits`,`doj`),
 ADD KEY `username` (`username`);

這不是我的查詢，我正在將 MySQL 中的原型系統轉換為 PostgreSQL。此查詢在 MySql 中確實有效。

這是否構成了MySql 中的一個錯誤，或者 PostgreSQL 的一個缺點，或者只是解釋的不同？

更像以上所有內容;）

這是否構成MySql 中的錯誤？

這是許多人認為是錯誤的“功能”。主要是因為它可能被濫用，就像您（或編寫第一個查詢的人）濫用它一樣。

該功能的正確使用是有一個“更短”的GROUP BY子句。任何在功能上依賴於列的GROUP BY列都可以從列表中省略，並且可以在聚合中和不聚合GROUP BY中自由使用。SELECT``ORDER BY

因此，如果 有唯一約束subs (subscribed_to, userid)，那麼您的第一個查詢是有效的 SQL - 根據某些（2011 年或 2013 年）添加的功能GROUP BY）。

您的 DDL 定義沒有這樣UNIQUE的約束，因此它被您的開發人員濫用。它產生正確結果（如果確實如此）的原因很可能是數據符合這樣的UNIQUE約束，儘管它沒有被強制執行。一個使用者只能訂閱一份雜誌（或任何指稱的東西subscribed_to）似乎是合理的。

您的數據也可能不符合這樣的約束，在這種情況下**，查詢會給出錯誤的結果——而您只是沒有註意到！**檢查您的數據和查詢的輸出（在 MySQL 中）。您是否為同一（雜誌或其他）和同一使用者訂閱了許多訂閱？在那種情況下，date_added返回哪個？並詢問業務使用者（誰知道查詢的要求），date_added應該返回哪個？最後一個（來自使用者和雜誌的眾多）？第一個？一個隨機的？

列表中的其餘列（來自users表）SELECT實際上正確顯示，因為兩個表之間的連接userid出現在GROUP BY列表中並且UNIQUE對users.

另請注意，MySQL 並沒有真正檢查所有這些事情（至少在 5.6 版之前）。正確使用該功能是開發人員的責任。而且由於要正確處理它是如此復雜，難怪它被廣氾濫用，在許多情況下給出不正確或不一致甚至根本不存在的結果。這就是為什麼許多人認為它是一個錯誤。（該特性在 MySQL 5.7 版本中得到了廣泛的改進。）

這是否構成PostgreSQL 的一個缺點。

並不真地。Postgres 實現了相同的功能，但略有不同——比 MySQL 更受限制。GROUP BYPostgres 僅在列具有PRIMARY KEY一個（或多個）表時才使用它。在這種情況下，您可以在不聚合的情況下使用SELECT和列表中的（這些表的）列的剩餘部分。ORDER BY它不適用於UNIQUE約束，也不適用於外鍵的（可證明的）約束。

這是否構成解釋上的差異？

如上所述，是的。兩個 DBMS 以不同的方式實現了該功能，因此對查詢的解釋也不同。

MySQL（最高 5.6）說*“我不在乎，我會給你一些結果。如果它們不正確，你負責）”*

Postgres 說：“我只會在我 100% 確定結果一致且正確的情況下給你結果。”

那麼，該怎麼做才能使查詢正常工作？

我們有兩個問題：

  • 對於users表格，很簡單：users.userid在GROUP BY列表中添加。
  • 對於subs表格和date_added列，了解使用者想要/期望的結果很重要。如果他們想要最早的日期（每個user和subscribed_to），則使用MIN(date_added). 如果他們想要最新的，請使用MAX(date_added).

查詢變為 - 並在所有 MySQL 和 Postgres 版本中正常工作：

SELECT MIN(subs.date_added) AS min_date_added, 
      users.userid, users.username,      --- any users column you need
      --- 
      subs.userid AS subscriber 
FROM cb_subscriptions AS subs 
    LEFT JOIN cb_users AS users 
    ON subs.userid = users.userid 
WHERE subs.subscribed_to = '2960' 
GROUP BY users.userid, 
        -- subs.subscribed_to,    -- not needed really, it's fixed in WHERE
        subs.userid 
ORDER BY min_date_added ASC 
LIMIT 8 ;

引用自：https://dba.stackexchange.com/questions/209371

相關問答

Mysql

PostgreSQL 到 MySQL 遷移以使用 WordPress 網站的數據庫 - 怎麼做？

  • May 3, 2020
檢視問答
Mysql

將 MySQL 轉換為 PostgreSQL 的問題

  • August 25, 2019
檢視問答
Mysql

遷移和規範化數據

  • June 1, 2012
檢視問答
Mysql

如何將大型 blob 表從 mysql 遷移到 postgresql？

  • August 1, 2011
檢視問答
Postgresql

Postgres 非確定性整理性能

  • September 12, 2022
檢視問答
Mysql

關於交易知識

  • August 9, 2022
檢視問答