主查询和子查询 – 子查询未返回所期望的结果

huangapple go评论79阅读模式
英文:

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 ;

huangapple
  • 本文由 发表于 2023年7月17日 23:42:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/76706113.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定