mysql中left join写法

最新推荐文章于 2024-08-23 10:05:44 发布

mvini

最新推荐文章于 2024-08-23 10:05:44 发布

阅读量1k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Big_Pig_Pig/article/details/103674712

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

		DB::table('employees as e')
            ->join('dept_emp as de',function($join){
                $join->on('de.emp_no = e.emp_no')
                    ->where('de.is_del','=',0)
                    ->where('de.is_del','=',0);
            },'left')  // 如果不写的默认inner
            ->where('e.is_del','=',0)
            ->select()
            ->groupBy()
            ->orderBy()
            ->limit()
            ->get();

        DB::table('employees as e')
            ->select('e.emp_no', 'e.first_name')
            ->leftJoin('dept_emp as de', function($join)
            {
                $join->on('de.is_del','=',0)
                    ->on('de.is_del','=',0);
            })
            ->get();


        //原生sql

        $sql = "SELECT e.`emp_no`,e.`first_name`,e.`birth_date`,e.`gender`,e.`hire_date`,e.`last_name`,de.`from_date`,de.`to_date`,d.`dept_name`
                FROM `employees` AS e
                LEFT JOIN `dept_emp` AS de ON(de.`emp_no` = e.`emp_no`, de.`emp_no` = e.`emp_no`)
                LEFT JOIN `departments` AS d ON(d.`dept_no` = de.`dept_no`,d.`dept_no` = de.`dept_no`)
                
                WHERE ".$where."
                
                GROUP BY e.`emp_no`,e.`first_name`,e.`birth_date`,e.`gender`,e.`hire_date`,e.`last_name`,de.`from_date`,de.`to_date`,d.`dept_name`
                ORDER BY e.`emp_no` DESC
                LIMIT  100";

        $data = DB::select($sql);