mysql 动态行变列

CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    quantity INT
);

SELECT 
    product_id,
    SUM(CASE WHEN MONTH(sale_date) = 1 THEN quantity ELSE 0 END) AS 'Jan',
    SUM(CASE WHEN MONTH(sale_date) = 2 THEN quantity ELSE 0 END) AS 'Feb',
    -- ... 其他月份
FROM 
    sales
GROUP BY 
    product_id;

import pandas as pd
import mysql.connector

# 连接MySQL数据库
conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
cursor = conn.cursor()

# 执行SQL查询
cursor.execute("SELECT product_id, sale_date, quantity FROM sales")
rows = cursor.fetchall()

# 将查询结果转换为DataFrame
df = pd.DataFrame(rows, columns=['product_id', 'sale_date', 'quantity'])

# 使用pivot_table方法实现动态行变列
pivot_df = df.pivot_table(index='product_id', columns='sale_date', values='quantity', aggfunc='sum')

# 打印结果
print(pivot_df)