如何使用MYSQL与PHP实现表格纵向数据转横向透视表效果
多表关联透视表实现方案
核心实现思路
先通过联表获取nama、category维度字段,再做行转列生成日期维度列,最后分别计算行维度的单商品总发货量、列维度的单日总发货量即可。
方案1:SQL端直接实现(以MySQL为例)
1. 基础联表逻辑(替换你原来仅查tbl_shipped的代码)
首先关联两张表拿到全量维度和度量数据:
SELECT d.nama, d.category, s.date, s.amount FROM tbl_data d LEFT JOIN tbl_shipped s ON d.id_data = s.id_data
2. 动态透视+合计统计完整代码
因为发货日期是动态变化的,用动态SQL生成列不需要手动修改代码:
-- 生成动态日期列的SQL片段 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(s.date = "', date, '", s.amount, 0)) AS `', date, '`') ) INTO @sql FROM tbl_shipped; -- 拼接商品维度的透视查询,加行合计 SET @sql = CONCAT( 'SELECT d.nama, d.category, ', @sql, ', SUM(s.amount) AS `总发货量` ', 'FROM tbl_data d ', 'LEFT JOIN tbl_shipped s ON d.id_data = s.id_data ', 'GROUP BY d.id_data, d.nama, d.category ' ); -- 拼接底部合计行,加列合计 SET @sql = CONCAT( @sql, ' UNION ALL ', 'SELECT "合计" AS nama, "" AS category, ', (SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(s.date = "', date, '", s.amount, 0)) AS `', date, '`')) FROM tbl_shipped), ', SUM(s.amount) AS `总发货量` ', 'FROM tbl_shipped s ' ); -- 执行生成的动态SQL PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
其他数据库适配说明
- PostgreSQL可替换为
crosstab函数实现透视,联表和合计逻辑一致 - SQL Server可使用
PIVOT关键字实现行转列 - 存在空日期数据可加
WHERE s.date IS NOT NULL过滤无效数据
方案2:Python Pandas实现(如果是代码侧处理数据)
如果是把数据拉到本地做分析,用Pandas实现更简洁:
import pandas as pd # 替换为你自己的数据库连接驱动,比如pymysql、psycopg2等 import pymysql # 1. 建立数据库连接拉取联表数据 conn = pymysql.connect(host='数据库地址', user='账号', password='密码', database='库名') df = pd.read_sql(''' SELECT d.nama, d.category, s.date, s.amount FROM tbl_data d LEFT JOIN tbl_shipped s ON d.id_data = s.id_data ''', conn) conn.close() # 2. 生成透视表 pivot_df = pd.pivot_table( df, index=['nama', 'category'], # 行维度:商品名+分类 columns='date', # 列维度:发货日期 values='amount', # 值:发货量 aggfunc='sum', # 聚合规则:求和 fill_value=0 # 空值填充为0 ) # 3. 新增行合计列:单商品所有日期发货总量 pivot_df['总发货量'] = pivot_df.sum(axis=1) # 4. 新增列合计行:所有商品单日发货总量 pivot_df.loc[('合计', ''), :] = pivot_df.sum(axis=0) # 输出结果 print(pivot_df)
内容的提问来源于stack exchange,提问作者Imam aqrom
相关产品推荐
相关产品推荐

