mysql - MySQL中COUNT(*)OVER(PARTITION BY mct.MasterTableid)的替代方法

有没有更好的方法可以将以下SQL Server查询转换为MySQL？

SELECT mct.MasterTableid,
       mct.MasterTablename,
       mct.MasterTableorderid,
       ct.CategoryTableid,
       ct.CategoryTablename,
       ct.CategoryTableorderid,
       COUNT(*) OVER (PARTITION BY mct.MasterTableid) AS CoursesCount,
       COUNT(scct.CategoryTabledetailid)          AS ChaptersCount
FROM   MasterTable tm
       INNER JOIN CategoryTable t
               ON ct.MasterTableid = mct.MasterTableid
       INNER JOIN SubCategoryTable td
               ON ct.CategoryTableid = scct.CategoryTableid
WHERE  ct.isdeleted = 0
       AND mct.isdeleted = 0
       AND scct.isdeleted = 0
GROUP  BY mct.MasterTableid,
          mct.MasterTablename,
          mct.MasterTableorderid,
          ct.CategoryTableid,
          ct.CategoryTablename,
          ct.CategoryTableorderid
ORDER  BY mct.MasterTableorderid,
          ct.CategoryTableorderid

MySQL中COUNT（*）OVER（PARTITION BY mct.MasterTableid）的替代方法

最佳答案

您可以count表中的每个MasterTableid和MasterTable结果。

SELECT mct.MasterTableid,
       mct.MasterTablename,
       mct.MasterTableorderid,
       ct.CategoryTableid,
       ct.CategoryTablename,
       ct.CategoryTableorderid,
       mxt_cnt.CoursesCount,
       Count(scct.CategoryTabledetailid) AS ChaptersCount
FROM   MasterTable mct
       INNER JOIN CategoryTable ct
               ON ct.MasterTableid = mct.MasterTableid
       INNER JOIN SubCategoryTable scct
               ON ct.CategoryTableid = scct.CategoryTableid
       INNER JOIN (SELECT MasterTableid,
                          Count(1) AS CoursesCount
                   FROM   MasterTable
                   WHERE  isdeleted = 0
                   GROUP  BY MasterTableid) mxt_cnt
               ON mxt_cnt.MasterTableid = mct.MasterTableid
WHERE  ct.isdeleted = 0
       AND mct.isdeleted = 0
       AND scct.isdeleted = 0
GROUP  BY mct.MasterTableid,
          mxt_cnt.CoursesCount, -- Added in Group by
          mct.MasterTablename,
          mct.MasterTableorderid,
          ct.CategoryTableid,
          ct.CategoryTablename,
          ct.CategoryTableorderid
ORDER  BY mct.MasterTableorderid,
          ct.CategoryTableorderid

或者您可以使用join

SELECT mct.MasterTableid,
       mct.MasterTablename,
       mct.MasterTableorderid,
       ct.CategoryTableid,
       ct.CategoryTablename,
       ct.CategoryTableorderid,
       mxt_cnt.CoursesCount,
       (SELECT Count(1) AS CoursesCount
        FROM   MasterTable mxt_cnt
        WHERE  mxt_cnt.MasterTableid = mct.MasterTableid
               AND mxt_cnt.isdeleted = 0) AS CoursesCount,
       Count(scct.CategoryTabledetailid)  AS ChaptersCount
FROM   MasterTable mct
       INNER JOIN CategoryTable ct
               ON ct.MasterTableid = mct.MasterTableid
       INNER JOIN SubCategoryTable scct
               ON ct.CategoryTableid = scct.CategoryTableid
WHERE  ct.isdeleted = 0
       AND mct.isdeleted = 0
       AND scct.isdeleted = 0
GROUP  BY mct.MasterTableid,
          mct.MasterTablename,
          mct.MasterTableorderid,
          ct.CategoryTableid,
          ct.CategoryTablename,
          ct.CategoryTableorderid
ORDER  BY mct.MasterTableorderid,
          ct.CategoryTableorderid