MySQL或PHP动态地将行转换为列
OK, I have a sales table that looks like this:
好的,我的销售表看起来像这样:
Prod_ID | Store_ID | Date_Sold | Num_Sold 105 | 1010 | 2012-09-21 | 50 105 | 1011 | 2012-09-22 | 20 105 | 1012 | 2012-09-22 | 35 ............................................ 109 | 1010 | 2012-09-21 | 25 109 | 1011 | 2012-09-23 | 15 109 | 1012 | 2012-09-23 | 30
I would like to create a new table or view that looks like this:
我想创建一个如下所示的新表或视图:
Store_ID | 105 | ... | 109 1010 | 50 | ... | 25 1011 | 20 | ... | 15 1012 | 35 | ... | 30
I'm not really sure how to accomplish this. I have seen where people hard code this in, but I don't want to do that as I have more that 50 different Prod_IDs and they are constantly changing. Is there a way to to this dynamically? I am going to be displaying this data on a webpage via PHP so maybe there is an easier way to do it using PHP?? Let me know if this explanation is unclear.
我真的不确定如何做到这一点。我已经看到人们硬编码的地方,但我不想这样做,因为我有50多个不同的Prod_ID,并且它们在不断变化。有没有办法动态地这个?我将通过PHP在网页上显示这些数据,所以也许有一种更简单的方法来使用PHP?如果这个解释不清楚,请告诉我。
Thanks in advance!!
提前致谢!!
2 个解决方案
#1
2
In MySQL you will need to use a prepared statement to PIVOT
the data. Since MySQL does not have a PIVOT
function, then you will need to use an aggregate function along with a CASE
:
在MySQL中,您需要使用预准备语句来PIVOT数据。由于MySQL没有PIVOT函数,因此您需要使用聚合函数和CASE:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(case when Prod_ID = ''',
Prod_ID,
''' then Num_Sold end) AS ''',
Prod_ID, ''''
)
) INTO @sql
FROM yourtable;
SET @sql = CONCAT('SELECT store_id, ', @sql, '
FROM yourtable
GROUP BY store_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo
请参阅SQL Fiddle with Demo
If you had a known number of columns, then you would hard-code the values similar to this:
如果您有已知数量的列,那么您将硬编码与此类似的值:
select store_id,
sum(case when prod_id = 105 then Num_Sold end) as '105',
sum(case when prod_id = 109 then Num_Sold end) as '109'
from yourtable
group by store_id
see SQL Fiddle with Demo
看看SQL Fiddle with Demo
更多相关文章
- [开心学php100天]第五天:string函数(上)
- curl获取网页内容出现乱码或为空的解决方案,另附curl_getinfo函数
- APMServ 在 Win7 下出现“APMServ-Apache 服务因 函数不正确。
- 本机PHP函数将授予我直接访问字符串部分而无需创建临时数组的权
- 数据库分卷备份 thinkphp3.2版
- 速率结构的数据库/算法
- 使用php codeigniter进行Mysql数据库同步
- PHPExcel导入导出及大量数据导入缓存方式的修改
- php中三种数据库的连接方式