php - 尝试将计数函数添加到复杂查询 PHP MYSQL

Question

我有一个复杂的查询，其中包含大量的内连接和左连接，我正在尝试使用 SQL 的 COUNT 函数获取某个表中的记录数。

我正在尝试使用 proposal_id 获取每个提案的“held_proposals”表中的记录数，因为我将使用 foreach 循环将数据输出到表中。

本质上，我想显示有多少学生“持有”反对每个提案的提案。

这是我的尝试:

SELECT p.proposal_id, p.proposal_title, p.description, u.user_record_id, u.forename, u.surname, c.course_title, h.*,
GROUP_CONCAT(DISTINCT t.tag_title) AS tags FROM proposal p 

      LEFT JOIN user u on u.user_record_id = p.user_record_id

      LEFT JOIN course_details c on c.course_code = p.course_code

      LEFT JOIN record r on r.proposal_id = p.proposal_id

      LEFT JOIN proposal_tags pt on pt.proposal_id = p.proposal_id

      LEFT JOIN tag_details t on t.tag_code = pt.tag_code

LEFT JOIN (
            SELECT h.student_record_id, COUNT(*) AS Held
            FROM held_proposals h
        ) H on h.proposal_id = p.proposal_id
      WHERE p.source = "Supervisor"

      AND (r.status_code not in (3,8) OR r.status_code IS NULL)

      GROUP BY p.proposal_id;

表格目前看起来像这样:

我想在表的末尾添加从“held_proposals”表返回的每个提案的值作为另一列。

能否就如何在 SQL 查询中实现此目的向我提供一些指导。提前谢谢你。

Answer 1

您需要将要加入的查询中的计数分组为

SELECT 
p.proposal_id, 
p.proposal_title, 
p.description, 
u.user_record_id, 
u.forename, 
u.surname, 
c.course_title, coalesce(h.Held,0) as `Held`,
GROUP_CONCAT(DISTINCT t.tag_title) AS tags 
FROM proposal p 
LEFT JOIN user u on u.user_record_id = p.user_record_id
LEFT JOIN course_details c on c.course_code = p.course_code
LEFT JOIN record r on r.proposal_id = p.proposal_id
LEFT JOIN proposal_tags pt on pt.proposal_id = p.proposal_id
LEFT JOIN tag_details t on t.tag_code = pt.tag_code
LEFT JOIN (
  SELECT proposal_id, COUNT(*) AS Held
  FROM held_proposals group by proposal_id
) h on h.proposal_id = p.proposal_id
WHERE p.source = "Supervisor"
AND (r.status_code not in (3,8) OR r.status_code IS NULL)
GROUP BY p.proposal_id;