我试图从两个不同的表中查询信息,但我不知道如何做到最好。作为免责声明,我仍在学习MySQL/PHP,并且我无法控制表的设置——我正在努力使用我所拥有的,因为我无法添加/更改表。以下是表格和相关属性:
Table(attribute1, attribute2, ...);
------------------------------------
reports(id, reporter_id, added)
report_comments(comment_id, report_id, comment_text, commenter_id)
reporter_id指的是提交报告的用户,commenter_id是而不是与reporter_id相同的人。
我想统计有多少报告评论,例如,对于每个reporter_id,comment_text中有"不正确"一词。然后,我想制作一个表格,显示每个记者的ID以及自"1383359439"(时间戳)以来与该记者的报道相关联的评论数量。
到目前为止,我还不是很成功。我当前的查询如下:
SELECT r.id, r.reporter_id,
(SELECT COUNT(*) FROM report_comments WHERE comment_text LIKE '%incorrect%' AND report_id = r.id) AS comments
FROM reports AS r
LEFT JOIN report_comments AS rc ON r.id = rc.report_id
WHERE r.added > 1383359439
GROUP BY r.reporter_id;
当我将HTML表设置为列出"reporter_id",然后是"comments"时,生成的页面会显示自列出时间以来提交报告的所有人,但计数为"0"或"1",任何报告评论中有"不正确"的报告人都会得到"1";没有"不正确的"报告人会得到"0":
Reporter1 | 0
Reporter2 | 1
Reporter3 | 0
Reporter4 | 1
Reporter5 | 1
问题是,一些记者发表了几条带有"错误"的评论,我想统计一下每一条,而且只针对那些记者(而不是那些从未发表过"错误"评论的记者)。例如:
Reporter2 | 2
Reporter4 | 17
Reporter5 | 3
我显然错过了什么——我做错了什么?
您需要为此使用分组。
SELECT
r.reporter_id AS `reporter_id`,
COUNT(rc.report_id) AS `incorrect_count`
FROM reports AS r
INNER JOIN report_comments AS rc
ON r.id = rc.report_id
WHERE rc.comment_text LIKE '%incorrect%'
AND r.added > ?
GROUP BY `reporter_id`
这里,?
表示您试图与之进行比较的时间戳。
为了回答您的后续问题,有几种方法可以做到这一点。我可能建议将SUM()
与CASE
结合使用,如下所示:
SELECT
r.reporter_id AS `reporter_id`,
SUM(
CASE WHEN rc.comment_text LIKE '%incorrect%'
THEN 1
ELSE 0
END CASE
) AS `incorrect_count`,
SUM(
CASE WHEN rc.comment_text LIKE '%fake%'
THEN 2
ELSE 0
END CASE
) AS `fake_count`,
FROM reports AS r
INNER JOIN report_comments AS rc
ON r.id = rc.report_id
WHERE
rc.comment_text LIKE '%incorrect%'
OR rc.comment_text LIKE '%fake%'
AND r.added > ?
GROUP BY `reporter_id`
它是这样的:
SELECT r.reporter_id, COUNT(*) comments
FROM reports AS r
INNER JOIN report_comments AS rc ON r.id = rc.report_id
WHERE r.added > 1383359439
AND comment_text LIKE '%incorrect%'
GROUP BY r.reporter_id;
我删除了r.id,因为在这种情况下没有意义,因为一个报告程序可以有多个报告(所以多个r.id)。
您可以尝试
SELECT r.id, COUNT(c.id) tot
FROM reports r INNER JOIN report_comments
ON r.id = c.report_id
AND c.comment_text LIKE '%incorrect%'
AND r.added > 1383359439
GROUP BY r.reporter_id