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 条评论
你解决了吗,我也遇到了同样的问题。卡壳了一下午
建议尝试做成存储过程然后调用,之前我尝试在DB::select()中执行复数的sql语句时也是死活会报语法错误,后来没办法只好做成存储过程在用DB::select() 去调用了