我检查了类似问题的其他解决方案,但是sqlite不支持row_number()和rank()函数,或者没有涉及连接多个表的示例,将它们按多列分组并且同时仅为每个组返回前N个结果.

这是我运行的代码

db = sqlite3.connect('mydb')

cursor = db.cursor()

cursor.execute(
    '''
    CREATE TABLE orders(
        id INTEGER PRIMARY KEY,product_id INTEGER,client_id INTEGER
        )
    '''
)

cursor.execute(
    '''
    CREATE TABLE clients(
        id INTEGER PRIMARY KEY,gender TEXT,city TEXT
        )
    '''
)

cursor.execute(
    '''
    CREATE TABLE products(
        id INTEGER PRIMARY KEY,category_name TEXT
        )
    '''
)

orders = [
    (9,6),(3,10),(8,(4,8),(5,(7,4),(9,2),(10,1),7),9),(2,(6,3),(1,5),9)
]

clients = [
    ('Male','NY'),('Female',('Male','London'),'London')
]

products = [
    ('Kitchen',),('Sport',('Furniture',('Kitchen',)
]

cursor.executemany("INSERT INTO orders(product_id,client_id) VALUES(?,?)",orders)
cursor.executemany("INSERT INTO clients(gender,city) VALUES(?,clients)
cursor.executemany("INSERT INTO products(category_name) VALUES(?)",(products))

db.commit()

cursor.execute(
    '''
    SELECT
        category_name,city,gender,product_id,COUNT(product_id)
    FROM orders
    LEFT JOIN products ON product_id = products.id
    LEFT JOIN clients ON client_id = clients.id
    GROUP BY product_id,category_name,gender
    ORDER BY category_name,COUNT(product_id) DESC
    '''
)

print('''category_name,COUNT(product_id)''')

all_rows = cursor.fetchall()
for a,b,c,d,e in all_rows:
    print(a,e)

db.close()

现在的问题是如何在单个查询中获得这样的输出？我不需要与红线交叉的行,因为我只需要前2.