pivot mysql表返回

62lalag4 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(332)

我正在尝试透视这样创建的mysql表

'CREATE TABLE `fundreturns` (
`Timestamp` datetime NOT NULL,
`FundName` varchar(255) NOT NULL,
`MonthYear` datetime NOT NULL,
`Returns` decimal(9,7) DEFAULT NULL,
PRIMARY KEY (`FundName`,`MonthYear`),
CONSTRAINT `FundName` FOREIGN KEY (`FundName`) REFERENCES `fundnames` (`fund_name`))

所以结果将是一个

Date    Company 1   Company 2   ...Company 200  
-------------------------------------------------
09/18        {Returns                    }              
07/18

不是每个公司每个月都有一个回报，而且是一个单一的百分比数字，不需要整个月加起来。我一直在寻找使用mysql或php的解决方案，我遇到了关于动态sql语句的答案，并尝试使用

SET @@group_concat_max_len = 32000;

SET @sql_dynamic = (
    SELECT
        GROUP_CONCAT( DISTINCT
            CONCAT(
                'IF(fundname = '''
                , fundname
                , ''', returns, NULL) AS '
                , fundname
            )
        )
    FROM fundreturns
);

SET @sql = CONCAT('SELECT monthyear, ', 
              @sql_dynamic, ' 
           FROM fundreturns
           GROUP BY monthyear'
       );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这给了我一个错误

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ACL) Alternative Fund,IF(fundname = 'ABCA Reversion', fundreturns, NULL) AS ABC' at line 1

我做错什么了？有没有更好的办法？

mysql pivot-table

来源：https://stackoverflow.com/questions/52425297/pivot-mysql-table-returns

1条答案

按热度按时间

uyto3xhc1#

考虑以下几点。。。

<?php

/*
CREATE TABLE fundreturns (
Timestamp datetime NOT NULL,
FundName varchar(255) NOT NULL,
Returns decimal(9,7) DEFAULT NULL,
PRIMARY KEY (`FundName`,`Timestamp`)
);

INSERT INTO fundreturns VALUES
('2018-01-01','Piggy',10.2),
('2018-01-02','Piggy',15.1),
('2018-01-03','Piggy',7.4),
('2018-01-04','Piggy',3.9),
('2018-01-05','Piggy',1.2),

('2018-01-01','Kermit',10.5),
('2018-01-02','Kermit',9.9),
('2018-01-03','Kermit',10.5),
('2018-01-04','Kermit',11.9),
('2018-01-05','Kermit',12.2);

SELECT Timestamp
     , FundName
     , Returns
  FROM fundreturns
 ORDER
    BY timestamp
     , fundname;

+---------------------+----------+------------+
| Timestamp           | FundName | Returns    |
+---------------------+----------+------------+
| 2018-01-01 00:00:00 | Kermit   | 10.5000000 |
| 2018-01-01 00:00:00 | Piggy    | 10.2000000 |
| 2018-01-02 00:00:00 | Kermit   |  9.9000000 |
| 2018-01-02 00:00:00 | Piggy    | 15.1000000 |
| 2018-01-03 00:00:00 | Kermit   | 10.5000000 |
| 2018-01-03 00:00:00 | Piggy    |  7.4000000 |
| 2018-01-04 00:00:00 | Kermit   | 11.9000000 |
| 2018-01-04 00:00:00 | Piggy    |  3.9000000 |
| 2018-01-05 00:00:00 | Kermit   | 12.2000000 |
| 2018-01-05 00:00:00 | Piggy    |  1.2000000 |
+---------------------+----------+------------+

* /

require('path/to/connection/stateme.nts');

$query = "
SELECT Timestamp
     , FundName
     , Returns
  FROM fundreturns
 ORDER
    BY timestamp
     , fundname;
";

$result = mysqli_query($db,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
$array[] = $row;
}

foreach($array as $v){
$new_array[$v['Timestamp']][$v['FundName']] = $v['Returns'];
}

print_r($new_array);
?>

输出：

Array
(
    [2018-01-01 00:00:00] => Array
        (
            [Kermit] => 10.5000000
            [Piggy] => 10.2000000
        )

    [2018-01-02 00:00:00] => Array
        (
            [Kermit] => 9.9000000
            [Piggy] => 15.1000000
        )

    [2018-01-03 00:00:00] => Array
        (
            [Kermit] => 10.5000000
            [Piggy] => 7.4000000
        )

    [2018-01-04 00:00:00] => Array
        (
            [Kermit] => 11.9000000
            [Piggy] => 3.9000000
        )

    [2018-01-05 00:00:00] => Array
        (
            [Kermit] => 12.2000000
            [Piggy] => 1.2000000
        )

)

赞(0）回复(0）举报 2021-06-20

我来回答

pivot mysql表返回

1条答案

相关问题

热门标签

最新问答