时间戳

首页 > 解决方案 > 如何以更短更好的方式在 MySQL 中编写此代码？

mysql - 如何以更短更好的方式在 MySQL 中编写此代码？

问题描述

数据库中有3个表，它们没有关系。如何以简短的形式编写此代码？对不起我的英语不好了。

MySQL 代码；

SELECT
    *,
    (SELECT COUNT(c2.id) FROM comments AS c2 WHERE c2.phone_number = comments.phone_number AND c2.country_code = comments.country_code AND c2.approve = "0" AND c2.deleted_at IS NULL) AS comment_count,
    (SELECT COUNT(f.id) FROM flag_numbers AS f WHERE f.phone_number = comments.phone_number AND f.country_code = comments.country_code AND comments.approve = "0" AND comments.deleted_at IS NULL) AS flag_count 
FROM comments 
WHERE approve = "0" AND deleted_at IS NULL 
ORDER BY id DESC

标签： mysqlsql

解决方案

您的代码非常好，但以下内容更简单一些：

SELECT c.*,
       COUNT(*) OVER (PARTITION BY c.phone_Number, c.country_code) as comment_count,
       (SELECT COUNT(f.id)
        FROM flag_numbers f
        WHERE f.phone_number = c.phone_number AND
              f.country_code = c.country_code 
      ) AS flag_count 
FROM comments c
WHERE c.approve = '0' AND c.deleted_at IS NULL 
ORDER BY id DESC;

如果approve是数字，则删除单引号。不要引用数字常量。

区别：

  • 将第一个子查询替换为comment_count.
  • 删除flag_count.

推荐阅读