Laravel sql语句GROUP 的 BUG,请大家来看看
DB::select(select a.ID,a.UserName,group_concat(b.ModID,'-',b.RightFlag) as Mod_Flag from user a left join userright b on a.id=b.userid where IsDel = 0 GROUP BY a.ID)。 Mysql执行这一段代码正常,放到 Laravel 里执行报错,用groupBy()写法也报错,去掉group_concat(b.ModID,'-',b.RightFlag) as Mod_Flag和GROUP BY a.ID就不报错,这是BUG嘛?


数据结构:
CREATE TABLE user (ID mediumint(9) unsigned NOT NULL AUTO_INCREMENT,UserName varchar(20) NOT NULL DEFAULT '111',Password varchar(255) NOT NULL,BaseUserType tinyint(4) NOT NULL DEFAULT '0',TrueName varchar(30) DEFAULT NULL,Sex tinyint(4) NOT NULL DEFAULT '1',Birthday date DEFAULT NULL,Phone varchar(12) DEFAULT NULL,EMail varchar(255) DEFAULT NULL,CardNO varchar(18) DEFAULT NULL,IsDel tinyint(4) NOT NULL DEFAULT '0',LastLoginTime datetime DEFAULT NULL,LoginIP varchar(15) DEFAULT NULL,Remarks text,remember_token varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,created_at timestamp NULL DEFAULT NULL,updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (ID),
KEY BaseUserType (BaseUserType),
KEY Phone (Phone),
KEY IsDel (IsDel),
KEY UserName (UserName) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
CREATE TABLE roleright (ID mediumint(9) NOT NULL AUTO_INCREMENT,RoleID smallint(6) NOT NULL,ModID smallint(6) NOT NULL,RightFlag tinyint(4) DEFAULT NULL,
PRIMARY KEY (ID),
KEY RoleID (RoleID),
KEY ModID (ModID)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
CREATE TABLE userright (ID mediumint(9) unsigned NOT NULL AUTO_INCREMENT,UserID smallint(6) unsigned NOT NULL,ModID smallint(6) NOT NULL,RightFlag tinyint(4) DEFAULT NULL,
PRIMARY KEY (ID),
KEY ModID (ModID),
KEY UserID (UserID)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
2 Comments
你解决了吗,我也遇到了同样的问题。卡壳了一下午
建议尝试做成存储过程然后调用,之前我尝试在DB::select()中执行复数的sql语句时也是死活会报语法错误,后来没办法只好做成存储过程在用DB::select() 去调用了