You need to enable JavaScript to run this app. 如何使用MYSQL与PHP实现表格纵向数据转横向透视表效果
优惠活动
大模型
产品
解决方案
定价
更多

如何使用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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.11 10:24:03