英文:
Main and sub query - sub query not returning the desired results
问题
我有以下用于评论系统的SQL查询。
SELECT
`main`.`comment_id`,
`main`.`comment`,
`main`.`timestamp`,
`main`.`replay_comment_id`,
COUNT(`replay`.`comment_id`) AS replays
FROM `posts_comments` AS `main`
LEFT JOIN `posts_comments` AS `replay` ON `replay`.`replay_comment_id` = `main`.`comment_id`
WHERE
`main`.`post` = "107" AND (`main`.`replay_comment_id` IS NULL OR `main`.`comment_id` in ( SELECT
`posts_comments`.`comment_id`
FROM
`posts_comments`
WHERE
`posts_comments`.`replay_comment_id` = `main`.`comment_id` ) )
GROUP BY
`main`.`comment_id`
ORDER BY
`main`.`comment_id` ASC;
具有以下数据库结构和内部值:
列名 | 类型 | 允许空 | 默认值 |
---|---|---|---|
comment_id | int(10) | 否 | |
post | int(10) | 否 | |
author | int(10) | 否 | |
replay_comment_id | int(10) | 是 | NULL |
timestamp | int(10) | 否 | |
comment | varchar(200) | 否 |
== 转储表 posts_comments 的数据
| 19 | 107 | 12 | NULL | 1688801931 | comment 1
| 20 | 107 | 12 | NULL | 1688801995 | comment 2
| 21 | 107 | 13 | 20 | 1688801995 | test 1 comment for comment 2
| 22 | 107 | 12 | 20 | 1688801995 | test 2 comment for comment 2
| 23 | 107 | 12 | 222 | 1688801995 | test 1 comment for other comment
期望的结果将返回ID为19和20的评论,因为它们是主要评论,以及ID为21和22的评论,因为它们是ID为20的子评论。不应返回ID为23的评论。现在的查询只返回主要评论。
在子查询中,如果我将 main
.comment_id
替换为20,我会得到想要的结果,但如果使用 main
.comment_id
,我就不会得到,我不明白为什么。对于任何评论和想法都将不胜感激。
英文:
I have the following SQL query used for a comments system.
SELECT
`main`.`comment_id`,
`main`.`comment`,
`main`.`timestamp`,
`main`.`replay_comment_id`,
COUNT(`replay`.`comment_id`) AS replays
FROM `posts_comments` AS `main`
LEFT JOIN `posts_comments` AS `replay` ON `replay`.`replay_comment_id` = `main`.`comment_id`
WHERE
`main`.`post` = "107" AND (`main`.`replay_comment_id` IS NULL OR `main`.`comment_id` in ( SELECT
`posts_comments`.`comment_id`
FROM
`posts_comments`
WHERE
`posts_comments`.`replay_comment_id` = `main`.`comment_id` ) )
GROUP BY
`main`.`comment_id`
ORDER BY
`main`.`comment_id` ASC;
With the following database structure and values inside:
|------
|Column|Type|Null|Default
|------
|//**comment_id**//|int(10)|No|
|post|int(10)|No|
|author|int(10)|No|
|replay_comment_id|int(10)|Yes|NULL
|timestamp|int(10)|No|
|comment|varchar(200)|No|
== Dumping data for table posts_comments
|19|107|12|NULL|1688801931|comment 1
|20|107|12|NULL|1688801995|comment 2
|21|107|13|20|1688801995|test 1 comment for comment 2
|22|107|12|20|1688801995|test 2 comment for comment 2
|23|107|12|222|1688801995|test 1 comment for other comment
The desired result will be the returning of comments with ID 19&20 since they are the main ones and 21&22 since they are the sub comments for ID 20. Comment with ID 23 should not be returned. Now the query only returns the main comments.
In the subquery if I replace main
.comment_id
with 20 I get the result I want, but with main
.comment_id
it doesn't and I can't understand why. Any comments and ideas will be appreciated.
答案1
得分: 1
不会起作用。19和20满足main
.replay_comment_id
为NULL,但在21子查询中,posts_comments
.replay_comment_id
= 19不提供任何结果。因此,子查询21和22没有被选中。
请检查此查询,如果它为您提供了结果。
SELECT
p1.`comment_id`,
p1.`comment`,
p1.`timestamp`,
p1.`replay_comment_id`,
(
CASE
WHEN p1.`replay_comment_id` IS NULL THEN 1
WHEN p1.`replay_comment_id` IN (SELECT DISTINCT comment_id FROM posts_comments) THEN 1
ELSE 0
END
) relationFlg,
SUM(1- ISNULL(p2.`comment_id`)) hasChild
FROM
`posts_comments` p1
LEFT JOIN `posts_comments` p2
ON p1.`comment_id` = p2.`replay_comment_id`
GROUP BY 1
HAVING relationFlg = 1
ORDER BY 1 ;
英文:
It wont work. 19 and 20 satisfies main
.replay_comment_id
IS NULL but on 21 subquery gives posts_comments
.replay_comment_id
= 19 which doesnt provide any result. So the child 21 and 22 are not picked.
Please check this query, if it provides you result.
SELECT
p1.`comment_id`,
p1.`comment`,
p1.`timestamp`,
p1.`replay_comment_id`,
(
CASE
WHEN p1.`replay_comment_id` IS NULL THEN 1
WHEN p1.`replay_comment_id` IN (SELECT DISTINCT comment_id FROM posts_comments) THEN 1
ELSE 0
END
) relationFlg,
SUM(1- ISNULL(p2.`comment_id`)) hasChild
FROM
`posts_comments` p1
LEFT JOIN `posts_comments` p2
ON p1.`comment_id` = p2.`replay_comment_id`
GROUP BY 1
HAVING relationFlg = 1
ORDER BY 1 ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论