收藏的一些sql语句

一、获取列表，不管是否存在都需要显示，
场景：一组有男10个人男生，女生5个人，二组3个女生，三组2个男生，列出三组男女的且要求都需要存在
表数据：

sql语句：

SELECT t.group_name,t.sex,COUNT(demo.sex) FROM (
SELECT demo.group_name,g.sex FROM `demo` JOIN (SELECT * FROM (SELECT "男" AS sex UNION SELECT "女" AS sex) e) g
 GROUP BY demo.group_name,g.sex
 ) t LEFT JOIN demo ON demo.`sex` = t.sex AND demo.`group_name` =t.group_name
 GROUP BY t.group_name,t.sex

运行结果

larave代码

$q_sql = DB::table(DB::raw("(SELECT * FROM (SELECT '男' AS sex UNION SELECT '女' AS sex) e) g"));
        $q1_sql = DB::table("demo")
            ->joinSub($q_sql,"g",function ($f){

            })
            ->selectRaw("demo.group_name,g.sex")
            ->groupBy("demo.group_name","g.sex")
        ;

        $ret = DB::table(DB::raw("(".DB::raw($q1_sql->toSql().") t")))->mergeBindings($q1_sql)
        ->leftJoin("demo",function (Builder $builder){
            $builder->on("demo.sex", "=", "t.sex")
                ->on("demo.group_name", "=", "t.group_name");
        })
            ->selectRaw(" t.group_name,t.sex,COUNT(demo.sex) as count_1")
            ->groupBy("t.group_name","t.sex")
            ->get()
            ->toArray()

        ;

打印结果