SQL UNION 和 UNION ALL 以及在laravel的使用

一 、SQL UNION 和 UNION ALL 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

二、 在laravel的使用

如果这是想合并两个结果集,

$fields = [
            'rdr.id as drawing_id',
            'rdr.ctime',
            'rdr.code',
            'rdr.name',
            'rdr.source',
            'u.name as creator_name',
            'rdc.name as category_name',
            'rdc.owner',
            'rdc.id as category_id',
            'rdr.image_orgin_name',
            'rdr.image_name',
            'rdr.image_path',
            'rdr.comment',
            'rdg.name as group_name',
        ];
        $fields_rpd = $fields_rppf = $fields;
        $fields_rpd[] = 'rpd.practice_id';
        $fields_rpd[] = 'rpd.description';

        $fields_rppf[] = 'rppf.practice_id';
        $fields_rppf[] = 'rppf.description';

        $where = [];
        $where[] = ['rdr.status', '=', 1]; 
$where_rpd = $where_rppf = $where;
        if (!empty($input['practice_id'])){
            $where_rpd[] = ['rpd.practice_id', '=', $input['practice_id']];
            $where_rppf[] = ['rppf.practice_id', '=', $input['practice_id']];
        }       
$builder_rpd = DB::table(config('alias.rpd') . ' as rpd')->select($fields_rpd)
            ->leftJoin($this->table . ' as rdr', 'rdr.id', '=', 'rpd.drawing_id')
            ->leftJoin(config('alias.rrad') . ' as u', 'u.id', '=', 'rdr.creator_id')
            ->leftJoin(config('alias.rdc') . ' as rdc', 'rdc.id', '=', 'rdr.category_id')
            ->leftJoin(config('alias.rdg') . ' as rdg', 'rdg.id', '=', 'rdr.group_id')
            ->where($where_rpd);

        $builder_rppf = DB::table(config('alias.rppf') . ' as rppf')->select($fields_rppf)
            ->leftJoin($this->table . ' as rdr', 'rdr.id', '=', 'rppf.img_id')
            ->leftJoin(config('alias.rrad') . ' as u', 'u.id', '=', 'rdr.creator_id')
            ->leftJoin(config('alias.rdc') . ' as rdc', 'rdc.id', '=', 'rdr.category_id')
            ->leftJoin(config('alias.rdg') . ' as rdg', 'rdg.id', '=', 'rdr.group_id')
            ->where($where_rppf)
            ->union($builder_rpd);
        $obj_list = $builder_rppf->get();

上面生成的sql,如下

    ( SELECT
`rdr`.`id` AS `drawing_id`,
`rdr`.`ctime`,
`rdr`.`code`,
`rdr`.`name`,
`rdr`.`source`,
`u`.`name` AS `creator_name`,
`rdc`.`name` AS `category_name`,
`rdc`.`owner`,
`rdc`.`id` AS `category_id`,
`rdr`.`image_orgin_name`,
`rdr`.`image_name`,
`rdr`.`image_path`,
`rdr`.`comment`,
`rdg`.`name` AS `group_name`,
`rppf`.`practice_id`,
`rppf`.`description` 
FROM
    `ruis_practice_practice_field` AS `rppf`
    LEFT JOIN `ruis_drawing` AS `rdr` ON `rdr`.`id` = `rppf`.`img_id`
    LEFT JOIN `ruis_rbac_admin` AS `u` ON `u`.`id` = `rdr`.`creator_id`
    LEFT JOIN `ruis_drawing_category` AS `rdc` ON `rdc`.`id` = `rdr`.`category_id`
    LEFT JOIN `ruis_drawing_group` AS `rdg` ON `rdg`.`id` = `rdr`.`group_id` 
WHERE
    ( `rdr`.`status` = 1 AND `rppf`.`practice_id` = 26 ) 
    ) UNION
    (
SELECT
    `rdr`.`id` AS `drawing_id`,
    `rdr`.`ctime`,
    `rdr`.`code`,
    `rdr`.`name`,
    `rdr`.`source`,
    `u`.`name` AS `creator_name`,
    `rdc`.`name` AS `category_name`,
    `rdc`.`owner`,
    `rdc`.`id` AS `category_id`,
    `rdr`.`image_orgin_name`,
    `rdr`.`image_name`,
    `rdr`.`image_path`,
    `rdr`.`comment`,
    `rdg`.`name` AS `group_name`,
    `rpd`.`practice_id`,
    `rpd`.`description` 
FROM
    `ruis_practice_drawing` AS `rpd`
    LEFT JOIN `ruis_drawing` AS `rdr` ON `rdr`.`id` = `rpd`.`drawing_id`
    LEFT JOIN `ruis_rbac_admin` AS `u` ON `u`.`id` = `rdr`.`creator_id`
    LEFT JOIN `ruis_drawing_category` AS `rdc` ON `rdc`.`id` = `rdr`.`category_id`
    LEFT JOIN `ruis_drawing_group` AS `rdg` ON `rdg`.`id` = `rdr`.`group_id` 
WHERE
    ( `rdr`.`status` = 1 AND `rpd`.`practice_id` = 26 ) 
    ) 

如果需要对合并的结果集再做操作,比如 排序,分页等,需要如下操作

$where_rpd=[];
$where_rppf = [];
$builder_rpd = DB::table(config('alias.rpd') . ' as rpd')->select($fields_rpd)
            ->leftJoin($this->table . ' as rdr', 'rdr.id', '=', 'rpd.drawing_id')
            ->leftJoin(config('alias.rrad') . ' as u', 'u.id', '=', 'rdr.creator_id')
            ->leftJoin(config('alias.rdc') . ' as rdc', 'rdc.id', '=', 'rdr.category_id')
            ->leftJoin(config('alias.rdg') . ' as rdg', 'rdg.id', '=', 'rdr.group_id')
            ->where($where_rpd);

        $builder_rppf = DB::table(config('alias.rppf') . ' as rppf')->select($fields_rppf)
            ->leftJoin($this->table . ' as rdr', 'rdr.id', '=', 'rppf.img_id')
            ->leftJoin(config('alias.rrad') . ' as u', 'u.id', '=', 'rdr.creator_id')
            ->leftJoin(config('alias.rdc') . ' as rdc', 'rdc.id', '=', 'rdr.category_id')
            ->leftJoin(config('alias.rdg') . ' as rdg', 'rdg.id', '=', 'rdr.group_id')
            ->where($where_rppf)
            ->union($builder_rpd);

        $sql = $builder_rppf->toSql();
        $builder_res = DB::table(DB::raw("($sql) as res"))->mergeBindings($builder_rppf);

        $input['total_records'] = $builder_res->count();
        $builder_res->forPage($input['page_no'],$input['page_size']);
        if (!empty($input['sort']) && !empty($input['order'])) $builder_res->orderBy('res.' . $input['sort'], $input['order']);
        $obj_list = $builder_res->get();

生成的如下sql:

SELECT
    * 
FROM
    (
        (
SELECT
    `rdr`.`id` AS `drawing_id`,
    `rdr`.`ctime`,
    `rdr`.`code`,
    `rdr`.`name`,
    `rdr`.`source`,
    `u`.`name` AS `creator_name`,
    `rdc`.`name` AS `category_name`,
    `rdc`.`owner`,
    `rdc`.`id` AS `category_id`,
    `rdr`.`image_orgin_name`,
    `rdr`.`image_name`,
    `rdr`.`image_path`,
    `rdr`.`comment`,
    `rdg`.`name` AS `group_name`,
    `rppf`.`practice_id`,
    `rppf`.`description` 
FROM
    `ruis_practice_practice_field` AS `rppf`
    LEFT JOIN `ruis_drawing` AS `rdr` ON `rdr`.`id` = `rppf`.`img_id`
    LEFT JOIN `ruis_rbac_admin` AS `u` ON `u`.`id` = `rdr`.`creator_id`
    LEFT JOIN `ruis_drawing_category` AS `rdc` ON `rdc`.`id` = `rdr`.`category_id`
    LEFT JOIN `ruis_drawing_group` AS `rdg` ON `rdg`.`id` = `rdr`.`group_id` 
WHERE
            ( `rdr`.`status` = ? AND `rppf`.`practice_id` = ? ) 
        ) UNION
    (
SELECT
    `rdr`.`id` AS `drawing_id`,
    `rdr`.`ctime`,
    `rdr`.`code`,
    `rdr`.`name`,
    `rdr`.`source`,
    `u`.`name` AS `creator_name`,
    `rdc`.`name` AS `category_name`,
    `rdc`.`owner`,
    `rdc`.`id` AS `category_id`,
    `rdr`.`image_orgin_name`,
    `rdr`.`image_name`,
    `rdr`.`image_path`,
    `rdr`.`comment`,
    `rdg`.`name` AS `group_name`,
    `rpd`.`practice_id`,
    `rpd`.`description` 
FROM
    `ruis_practice_drawing` AS `rpd`
    LEFT JOIN `ruis_drawing` AS `rdr` ON `rdr`.`id` = `rpd`.`drawing_id`
    LEFT JOIN `ruis_rbac_admin` AS `u` ON `u`.`id` = `rdr`.`creator_id`
    LEFT JOIN `ruis_drawing_category` AS `rdc` ON `rdc`.`id` = `rdr`.`category_id`
    LEFT JOIN `ruis_drawing_group` AS `rdg` ON `rdg`.`id` = `rdr`.`group_id` 
WHERE
            ( `rdr`.`status` = ? AND `rpd`.`practice_id` = ? ) 
        ) 
    ) AS res 
ORDER BY
    `res`.`ctime` DESC 
    LIMIT 8 OFFSET 0

上面是实际项目的sql,简单写法:

   $builder_t1 =  DB::table('table_1')
           ->select(['id','name','ctime']) 
           ->where('name','like','%lester%'); 
   $builder_t2 =  DB::table('table_2')->select(['id','name','ctime']) 
           ->where('name','like','%you%')
           ->union($builder_t1); 
     
   $querySql = $query->toSql(); 
   $obj_list = DB::table(DB::raw("($querySql) as res"))->mergeBindings($builder_t2) 
           ->orderBy('res.ctime','desc')
           ->forPage($startPage,$pageSize); 
unionlaravel

我来吐槽

*

*