我遇到了一个让我有点困惑的 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

该字段是一个 CHAR，所以我不确定要使用哪种聚合， 或者(对我的理解来说更重要)为什么需要一个。

正如我所说，我不确定我是否理解为什么会发生这种情况。我假设我 不像我想的那样理解 group by ;)

这不是我的查询，我正在将 MySQL 原型(prototype)系统转换为 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`);