mysql - 如何计算内部连接表中的行数

Question

我正在使用交叉引用表提取解决方案的所有信息。

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type

效果很好，我的结果符合预期。但是，我有另一个表包含该解决方案的任务，每个任务都有一个进度。我想指出该解决方案有多少任务，我已经尝试过:

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(*) FROM t WHERE t.progress < 100 AS task)
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution

但是我收到了这个错误:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS task) FROM _user_solution s INNER JOIN _users u ON s.uid = u.uid' at line 3

任何有关我如何计算此解决方案未完成的所有任务的想法都将不胜感激。

Answer 1

您需要将AS task 别名部分移到子查询之外；在右括号之外。

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(*) FROM _solution_tasks WHERE progress < 100) AS task
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution