使用 JOIN 表的错误 MySQL 查询结果

debugcn 发表于 Dev

异种

以下 MySQL 查询假设根据帖子的观点 + 评分 + 提交日期按升序对帖子进行排名：

select
    cat                    ,
    p.id                   ,
    title                  ,
    p.date                 ,
    shares                 ,
    source                 ,
    cat                    ,
    count(v.post_id) views ,
    sum(r.ilike)     rating,
    r.module               ,
    r.module_id            ,
    @Rank := @Rank + 1 AS Rank
from
    posts p
    JOIN
        rates r
        on
            r.module_id  = p.id
            AND r.module = 'posts'
    JOIN
        posts_views v
        on
            v.post_id = p.id
WHERE
    p.date     <= UNIX_TIMESTAMP(NOW())
    AND p.state = '3'
    AND
    (
        p.cat NOT REGEXP '[[:<:]]15[[:>:]]'
    )
GROUP BY
    r.module_id
ORDER BY
    rating DESC       ,
    views DESC        ,
    p.date ASC LIMIT 0,     10

给出以下结果：

结果有3个问题：

的视图列值加倍
该评级列的值复制意见“值
NULL 中的Rank列

斯宾塞7593

该查询正在生成一个半笛卡尔积。与来自多个匹配的行r和从多个匹配的行v，这些行越来越匹配在一起，膨胀的结果rating和views。如果我们删除 GROUP BY 和聚合函数，并取回详细信息行，我们可以观察到导致视图计数增加一倍、三倍的“重复”行......

对此的一种解决方法是通过从内联视图中的至少一个子表进行预聚合来避免笛卡尔积。然后我们将派生表连接到posts表中以获取到外部查询的聚合。

当viewsor中没有匹配的行时，我们可能想考虑使用外连接来处理条件rates，因此我们可以为没有任何视图的帖子返回零计数。

初始化用户定义的变量，作为单独的语句，或在内联视图中。

此外，我们要晋级的所有列引用，既是为了帮助未来的读者（不是强迫他们在表定义一下找出哪些表中的列状cat或者title或者source是从哪里来的），并避免与查询断裂“模糊列”错误，将来当同名列添加到查询中引用的表之一时。

我建议这样的事情：

SELECT p.cat
     , p.id
     , p.title
     , p.date
     , p.shares
     , p.source
     , p.cat
     , IFNULL(v.cnt_views,0)  AS views
     , r.tot_rating           AS rating
     , r.module
     , r.module_id
     , @Rank := @Rank + 1     AS Rank

  FROM ( SELECT @Rank := 0 ) i

 CROSS
  JOIN posts p

  LEFT
  JOIN ( SELECT ra.module_id
              , MAX(ra.module)   AS module
              , SUM(ra.ilike)    AS tot_rating
           FROM rates ra
          WHERE ra.module = 'posts'
          GROUP
             BY ra.module_id
       ) r
    ON r.module_id = p.id

  LEFT
  JOIN ( SELECT pv.post_id
              , SUM(1)    AS cnt_views
           FROM posts_views pv
          GROUP
             BY pv.post_id
       ) v
    ON v.post_id = p.id

 WHERE p.date <= UNIX_TIMESTAMP(NOW())
   AND p.state = '3'
   AND p.cat NOT REGEXP '[[:<:]]15[[:>:]]'

 ORDER
    BY r.tot_rating DESC
     , v.cnt_views  DESC
     , p.date ASC
 LIMIT 0, 10

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-07-24

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

使用 JOIN 表的错误 MySQL 查询结果

使用 JOIN 表的错误 MySQL 查询结果

使用表的join构建一个mysql查询以在symfony中使用

使用OR子句的MySQL JOIN查询非常慢

表的GROUP BY在使用LEFT JOIN的MySQL中返回错误计数

使用JOIN修改多列查询的结果

使用pdo在同一表上的php mysql join查询

使用INNER JOIN和LEFT JOIN的MySQL查询

如何使用同一数据库表的查询结果更新MYSQL表中的字段？

MySQL LEFT JOIN无法使用多个表

使用INNER JOIN的mySQL查询的打印结果

使用WHERE子句的MySQL LEFT JOIN查询

MySQL乘以查询结果（LEFT JOIN，SUM，COUNT，GROUP BY）

使用JOIN查询的Mysql Pivot /交叉表查询

使用LEFT JOIN和History表的MySQL查询的正确结果

如何使用PHP错误显示MySql查询的多个结果

MySQL Join查询中的语法错误

使用COUNT ON LEFT JOIN进行MySQL查询

使用OR子句的MySQL JOIN查询非常慢

在MySQL JOIN查询中使用GROUB BY

MySQL Join查询{如何获得以下结果}

当使用Join且2个表的字段名称相同时，如何在MYSQL查询中查明正确的结果？

从JOIN中的MySQL查询中排除结果

使用LEFT OUTER JOIN建立查询结果

MySQL查询的排序结果（使用join）

MySQL JOIN与LIMIT查询结果

结合MySQL表与查询UNION或JOIN？

使用JOIN表进行MYSQL查询

如何从LEFT JOIN MySQL表调用结果

MYSQL 使用 join 查询多个表

MySQL：当引用外部表的列时，在 JOIN 的子查询中使用 WHERE 子句会出错