概述

我检查了类似问题的其他解决方案,但是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.

WITH order_groups AS (
  SELECT
    category_name,COUNT(product_id) AS product_count
  FROM orders OO
  LEFT JOIN products ON product_id = products.id
  LEFT JOIN clients ON client_id = clients.id
  GROUP BY product_id,gender
  ORDER BY category_name,COUNT(product_id) DESC
)
SELECT * FROM order_groups OG_outer
WHERE OG_outer.product_id IN (
  SELECT product_id
  FROM order_groups OG_inner
  WHERE 
    OG_outer.category_name = OG_inner.category_name AND
    OG_outer.city = OG_inner.city AND
    OG_outer.gender = OG_inner.gender
  ORDER BY OG_inner.product_count DESC LIMIT 2
)
ORDER BY category_name,product_count DESC

这将按要求输出以下行：

Furniture|London|Female|4|2
Furniture|London|Female|3|1
Furniture|London|Male|4|3
Furniture|London|Male|3|2
Furniture|NY|Female|5|2
Furniture|NY|Female|4|1
Furniture|NY|Male|3|3
Furniture|NY|Male|4|1
Kitchen|London|Female|9|2
Kitchen|London|Female|8|1
Kitchen|London|Male|9|3
Kitchen|London|Male|8|1
Kitchen|NY|Female|9|4
Kitchen|NY|Female|10|2
Kitchen|NY|Male|1|1
Kitchen|NY|Male|8|1
Sport|London|Female|7|2
Sport|London|Female|2|1
Sport|London|Male|7|2
Sport|London|Male|6|1
Sport|NY|Female|2|2
Sport|NY|Female|6|2
Sport|NY|Male|7|3

总结

以上是编程之家为你收集整理的python – SQLite：在每个组中仅返回前2个结果全部内容，希望文章能够帮你解决python – SQLite：在每个组中仅返回前2个结果所遇到的程序开发问题。

如果您也喜欢它,动动您的小指点个赞吧