一、获取列表,不管是否存在都需要显示,
场景:一组有男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()
;
打印结果