统计Library1或Library2至少借1本书的学生总数的MySQL/Laravel实现

MySQL 实现 #

方案1：UNION 去重统计 #

利用UNION默认合并后去重的特性，先合并两个图书馆的借书用户ID，再统计总数量：

SELECT COUNT(*) AS total FROM (
    SELECT student_id FROM Library1
    UNION
    SELECT student_id FROM Library2
) AS borrowed_students;

方案2：EXISTS 关联判断 #

直接关联学生表，判断是否存在任意一个图书馆的借书记录，数据量大时性能更优：

SELECT COUNT(id) AS total FROM Students
WHERE EXISTS (SELECT 1 FROM Library1 WHERE Library1.student_id = Students.id)
   OR EXISTS (SELECT 1 FROM Library2 WHERE Library2.student_id = Students.id);

两种方案输出结果均为total = 3，符合预期。

Laravel DB 查询实现 #

对应UNION方案 #

use Illuminate\Support\Facades\DB;

$total = DB::table(function ($query) {
    $query->select('student_id')
        ->from('library1')
        ->union(DB::table('library2')->select('student_id'));
}, 'borrowed_students')
->count();

对应EXISTS方案 #

use Illuminate\Support\Facades\DB;

$total = DB::table('students')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('library1')
            ->whereColumn('library1.student_id', 'students.id');
    })
    ->orWhereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('library2')
            ->whereColumn('library2.student_id', 'students.id');
    })
    ->count();

内容的提问来源于stack exchange，提问作者Md. Sorwar Hossain