如何在SQL中计算两个task_list查询的行数总和（含重复）

我需要在SQL中计算两个不同查询的行数总和（包含重复数据），具体场景如下：

• 第一个查询：获取当前员工被分配的任务，对应SQL及结果：

$sql = "SELECT * FROM `task_list` where task_id in (SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}') order by strftime('%s',date_created) desc";
$qry = $conn->query($sql);
$i = 1;
while($row = $qry->fetchArray()):

该查询返回24行数据。

• 第二个查询：获取当前部门的任务，对应SQL及结果：

$sql = "SELECT * FROM `task_list` where department_id = '{$_SESSION['department_id']}' order by strftime('%s',date_created) desc";
$qry = $conn->query($sql);
$i = 1;
while($row = $qry->fetchArray()):

该查询返回8行数据。

涉及的表结构 #

task_list表 #

CREATE TABLE "task_list" (
    "task_id"   INTEGER NOT NULL,
    "task_code" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "description"   TEXT NOT NULL,
    "department_id" INTEGER NOT NULL,
    "employee_id"   INTEGER,
    "status"    INTEGER NOT NULL DEFAULT 1,
    "date_created"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date_updated"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date"  TEXT,
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE SET NULL,
    FOREIGN KEY("department_id") REFERENCES "department_list"("department_id") on DELETE CASCADE,
    PRIMARY KEY("task_id" AUTOINCREMENT)
);

task_assignees表 #

CREATE TABLE "task_assignees" (
    "task_id"   INTEGER NOT NULL,
    "employee_id"   INTEGER NOT NULL,
    FOREIGN KEY("task_id") REFERENCES "task_list"("task_id") on DELETE CASCADE,
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE CASCADE
);

我希望得到两个查询行数的总和（24+8=32）作为单个结果，但编写的如下查询报错：

$task=$conn->query("SELECT sum(count) as `total_count` from(SELECT count(task_id) as `count` FROM `task_list` where task_id in(SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}')) UNION ALL (SELECT count(task_id) as `count` FROM `task_list` where department_id = '{$_SESSION['department_id']}')")->fetchArray()['total_count'];
echo $task > 0 ? number_format($total_count) : 0 ;

问题修复方案 #

错误原因 #

1. SQL语法错误：UNION ALL连接的两个子查询未被整体包裹在括号内，导致外层SUM无法正确识别数据源；且SQLite要求子查询必须指定别名。
2. PHP变量错误：输出时使用了未定义的$total_count，应该用查询结果赋值的$task变量。

修复后的代码 #

$task = $conn->query("
    SELECT SUM(count) AS `total_count` 
    FROM (
        SELECT COUNT(task_id) AS `count` 
        FROM `task_list` 
        WHERE task_id IN (SELECT task_id FROM `task_assignees` WHERE employee_id = '{$_SESSION['employee_id']}')
        UNION ALL
        SELECT COUNT(task_id) AS `count` 
        FROM `task_list` 
        WHERE department_id = '{$_SESSION['department_id']}'
    ) AS subquery
")->fetchArray()['total_count'];

echo $task > 0 ? number_format($task) : 0;

更高效的替代写法 #

如果不需要单独获取两个查询的计数，直接用两个子查询的计数相加，无需外层SUM，代码更简洁：

$task = $conn->query("
    SELECT 
        (SELECT COUNT(task_id) FROM `task_list` WHERE task_id IN (SELECT task_id FROM `task_assignees` WHERE employee_id = '{$_SESSION['employee_id']}')) +
        (SELECT COUNT(task_id) FROM `task_list` WHERE department_id = '{$_SESSION['department_id']}') AS `total_count`
")->fetchArray()['total_count'];

echo $task > 0 ? number_format($task) : 0;

内容的提问来源于stack exchange，提问作者Chirag Dixit