MySQL的视图语句,就拿一个实例讲。我想把 role表和privilege表拼接。下面是我两张数据表的视图。
SELECT a.* ,GROUP_CONCAT(b.pri_name) FROM 前缀(没有就不加)_role a LEFT JOIN 前缀(没有就不加)_privilege b ON FIND_IN_SET(b.id,a.par_id_list) GROUP BY a.id;
SELECT a.* b.pri_name FROM role a LEFT JOIN privilege b ON FIND_IN_SET(b.id,a.par_id_list) GROUP BY a.id;
//将a表所有字段列出,b表的pri_name字段,给他们分组到a.id下面。
SELECT a.* GROUP_CONCAT(b.pri_name) FROM role a LEFT JOIN privilege b ON FIND_IN_SET(b.id,a.par_id_list) GROUP BY a.id;
//将b.表pri_name字段全部显示出来。
ThinkPHP 的视图方法。上图是有问题的join-> 是错的,正确的应该是 join()->,写的时候粗心了。
正确的语句:
$list = $role->field('a.*,GROUP_CONCAT(b.pri_name) pri_name')->alias('a')->join('LEFT JOIN car_privilege b ON FIND_IN_SET(b.id,a.par_id_list)')->limit($Page->firstRow.','.$Page->listRows)->group('a.id')->select();
Tags: None