如何绕过MySql中语法错误分区

Question

MySql引发运行此sql的ProgrammingError

SELECT parents.uuid AS parents_uuid, children.uuid AS children_uuid,
children.parent_uuid AS children_parent_uuid, 
count(*) OVER (PARTITION BY parents.uuid) AS children_count
FROM parents, children
WHERE children.parent_uuid = parents.uuid ORDER BY children_count DESC

错误：

sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY parents.uuid)' at line 1") [SQL: 'SELECT parents.uuid AS parents_uuid, children.uuid AS children_uuid, children.parent_uuid AS children_parent_uuid, count(*) OVER (PARTITION BY parents.uuid)']

使用模式，其中Parent可以有多个children，而Child只有一个parent

Base = declarative_base()
class Parent(Base):
    __tablename__ = 'parents'
    uuid = Column(String(64), primary_key=True, unique=True)
    def __init__(self):  
        self.uuid = uuid.uuid4()   

class Child(Base):
    __tablename__ = 'children'
    uuid = Column(String(64), primary_key=True, unique=True)
    parent_uuid = Column(String(64), ForeignKey('parents.uuid'))
    def __init__(self, parent_uuid=None):  
        self.uuid = uuid.uuid4()   
        self.parent_uuid = parent_uuid

Answer 1

MySQL中不支持窗口函数。要解决此问题，请使用相关子查询来获取计数。

SELECT 
p.uuid AS parents_uuid, 
c.uuid AS children_uuid,
c.parent_uuid AS children_parent_uuid, 
(SELECT COUNT(*) FROM children WHERE parent_uuid = p.uuid) AS children_count
FROM parents p
JOIN children c ON c.parent_uuid = p.uuid 
ORDER BY children_count DESC

或者使用派生表获取计数并将其join到现有查询。

SELECT 
p.uuid AS parents_uuid, 
c.uuid AS children_uuid,
c.parent_uuid AS children_parent_uuid, 
counts.children_count
FROM parents p
JOIN (SELECT parent_uuid,COUNT(*) as children_count
      FROM children 
      GROUP BY parent_uuid) counts ON counts.parent_uuid = p.uuid 
JOIN children c ON c.parent_uuid = p.uuid 
ORDER BY counts.children_count DESC

Answer 2

从MySQL 8.0 https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html开始支持窗口函数