GROUP_BY 内的活动记录 LIMIT

GROUP BY 是一种在数据库中使用的查询语句，它可以根据指定的列对数据进行分组。而在 GROUP BY 子句中，我们还可以使用 LIMIT 关键字来限制返回的记录数目。接下来，我们将通过自然语言生成一篇文章，来进一步说明 GROUP BY 和 LIMIT 的用法，并为你提供一个相关的案例代码。

SELECT 地区, SUM(销售额) AS 销售总额
FROM 销售记录
GROUP BY 地区

SELECT 地区, SUM(销售额) AS 销售总额
FROM 销售记录
GROUP BY 地区
ORDER BY 销售总额 DESC
LIMIT 5

python
import sqlite3
# 连接到 SQLite 数据库
conn = sqlite3.connect('sales.db')
cursor = conn.cursor()
# 创建一个销售记录表格
cursor.execute('''CREATE TABLE sales
                  (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  date DATE,
                  region TEXT,
                  amount FLOAT)''')
# 插入一些销售记录
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-01', '东部', 1000)")
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-02', '东部', 1500)")
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-03', '西部', 2000)")
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-04', '西部', 1200)")
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-05', '南部', 1800)")
cursor.execute("INSERT INTO sales (date, region, amount) VALUES ('2022-01-06', '南部', 900)")
# 按地区分组并计算销售总额
cursor.execute("SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region")
# 获取结果并打印
results = cursor.fetchall()
for row in results:
    print(f"地区：{row[0]}，销售总额：{row[1]}")
# 获取销售总额最高的前两个地区
cursor.execute("SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region ORDER BY total_sales DESC LIMIT 2")
# 获取结果并打印
results = cursor.fetchall()
for row in results:
    print(f"地区：{row[0]}，销售总额：{row[1]}")
# 关闭数据库连接
conn.close()