python - 如何解决 MySQL 中的 PARTITION BY 语法错误

Question

MySql 引发 ProgrammingError 运行此 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) 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 可以有多个 child，而 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

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

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