首页 新闻 赞助 找找看

关于 group_concat() 的问题

0
悬赏园豆:10 [待解决问题]

1、首先上测试的表结构和数据的 sql 吧


 Navicat Premium Data Transfer

 Source Server         : 本机
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80012
 File Encoding         : 65001

 Date: 11/03/2019 23:26:12
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `actor_id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '演员名称',
  PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='演员表';

-- ----------------------------
-- Records of actor
-- ----------------------------
BEGIN;
INSERT INTO `actor` VALUES (4, '古月方源');
INSERT INTO `actor` VALUES (5, '凤九歌');
INSERT INTO `actor` VALUES (6, '吴帅');
INSERT INTO `actor` VALUES (8, '龙公');
INSERT INTO `actor` VALUES (9, '星宿仙尊');
COMMIT;

-- ----------------------------
-- Table structure for genre
-- ----------------------------
DROP TABLE IF EXISTS `genre`;
CREATE TABLE `genre` (
  `genre_id` int(11) NOT NULL AUTO_INCREMENT,
  `genre_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of genre
-- ----------------------------
BEGIN;
INSERT INTO `genre` VALUES (1, '科幻');
INSERT INTO `genre` VALUES (2, '剧情');
INSERT INTO `genre` VALUES (3, '喜剧');
INSERT INTO `genre` VALUES (4, '恐怖');
COMMIT;

-- ----------------------------
-- Table structure for movie
-- ----------------------------
DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie` (
  `movie_id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`movie_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8 COMMENT='电影表';

-- ----------------------------
-- Records of movie
-- ----------------------------
BEGIN;
INSERT INTO `movie` VALUES (1, '蛊真人');
INSERT INTO `movie` VALUES (2, '教父');
INSERT INTO `movie` VALUES (3, '七武士');
INSERT INTO `movie` VALUES (4, '天堂电影院');
COMMIT;

-- ----------------------------
-- Table structure for movie_actor
-- ----------------------------
DROP TABLE IF EXISTS `movie_actor`;
CREATE TABLE `movie_actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
  `actor_id` int(11) NOT NULL COMMENT '演员id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='电影、演员中间表';

-- ----------------------------
-- Records of movie_actor
-- ----------------------------
BEGIN;
INSERT INTO `movie_actor` VALUES (1, 1, 4);
INSERT INTO `movie_actor` VALUES (2, 1, 5);
INSERT INTO `movie_actor` VALUES (3, 1, 5);
INSERT INTO `movie_actor` VALUES (4, 1, 8);
INSERT INTO `movie_actor` VALUES (5, 1, 9);
INSERT INTO `movie_actor` VALUES (6, 2, 3);
INSERT INTO `movie_actor` VALUES (7, 3, 5);
INSERT INTO `movie_actor` VALUES (8, 2, 9);
INSERT INTO `movie_actor` VALUES (9, 3, 8);
INSERT INTO `movie_actor` VALUES (10, 3, 9);
INSERT INTO `movie_actor` VALUES (11, 4, 8);
INSERT INTO `movie_actor` VALUES (12, 5, 4);
COMMIT;

-- ----------------------------
-- Table structure for movie_genre
-- ----------------------------
DROP TABLE IF EXISTS `movie_genre`;
CREATE TABLE `movie_genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_id` int(11) NOT NULL DEFAULT '0' COMMENT '电影id',
  `genre_id` int(11) NOT NULL DEFAULT '0' COMMENT '分类id',
  PRIMARY KEY (`id`),
  KEY `asset_id` (`movie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='电影、分类中间表';

-- ----------------------------
-- Records of movie_genre
-- ----------------------------
BEGIN;
INSERT INTO `movie_genre` VALUES (1, 1, 2);
INSERT INTO `movie_genre` VALUES (2, 1, 4);
INSERT INTO `movie_genre` VALUES (3, 2, 2);
INSERT INTO `movie_genre` VALUES (4, 3, 2);
INSERT INTO `movie_genre` VALUES (5, 3, 4);
INSERT INTO `movie_genre` VALUES (6, 3, 3);
INSERT INTO `movie_genre` VALUES (7, 4, 2);
INSERT INTO `movie_genre` VALUES (8, 4, 3);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

2、重现一下我的操作

2.1 查询电影和分类

2.2 将分类合并成一行

这一步应该也没什么问题。

2.3 再加入一列

这里就出问题了。求大神帮忙解答一下。

3、还有一个问题

还有一个问题就是,如上面的 sql, 在新版的 MySQL 中可能会出现如下错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.

同样的 sql, 在公司没问题,但是在我个人电脑上就会报错,我的 MySQL 版本是 8.0.12, 网上查了一下,说是:

MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样),那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询,这些查询将引用组中未命名的非聚合列,而不是在功能上依赖于它们。

Yoooshiki的主页 Yoooshiki | 初学一级 | 园豆:3
提问于:2019-03-15 13:56
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册