从mysql表中获取数据 - fetch data form mysql table

你好，下面是我的查询，它给出了很好的结果

SELECT `collection_series`.`chart_name`, `datadatexnumericy`.`x` as date, `datadatexnumericy`.`y`
FROM (`datadatexnumericy`)
JOIN `collection_series` ON `collection_series`.`series_id` = `datadatexnumericy`.`series_id`
WHERE `collection_series`.`collection_id` =  '265'
chart_name            date              y
Sydney             1973-09-30         2.50000
Melbourne          1973-09-30         5.70000
Brisbane           1973-09-30         6.60000
Perth              1973-09-30         7.10000

但如果我想要下面这样的结果，有什么解决方案吗？任何帮助都会提前通知，谢谢。。。

date             Sydney         Melbourne      Brisbane     Perth       
1973-09-30       2.50000        5.70000        6.60000      7.10000

下面是我的表格结构

datadatexnumericy(first table)
series_id     x            y
43532        1991-12-31   -2.10000

不要混淆series_id，因为城市名称来自集合系列表，其中series''id匹配并获取城市名称

collection_series(second table)
in this table there is coloumn which name is collection_id and series_id
collection id is '265' and i am matching `collection_series`.`series_id` = `datadatexnumericy`.`series_id`

如果是针对一组已知的chart_names，则可以使用以下技术生成数据透视表

select
dd.x as date,
max( case when cs.chart_name = 'Sydney' then dd.y end ) as `Sydney`,
max( case when cs.chart_name = 'Melbourne' then dd.y end ) as `Melbourne`,
max( case when cs.chart_name = 'Brisbane' then dd.y end ) as `Brisbane`,
max( case when cs.chart_name = 'Perth' then dd.y end ) as `Perth`
from datadatexnumericy dd
join collection_series cs on cs.series_id = dd.series_id
group by dd.x

您也可以在group by之前添加where条件作为

WHERE cs.collection_id =  '265'

以下是如何使其成为动态

set @sql = NULL;
select
  group_concat(distinct
    concat(
      'max(case when cs.chart_name = ''',
      cs.chart_name,
      ''' then dd.y end) AS ',
      replace(cs.chart_name, ' ', '')
    )
  ) INTO @sql
from collection_series cs
join datadatexnumericy dd on cs.series_id = dd.series_id
;
set @sql = concat('select dd.x as date, ', @sql, ' from datadatexnumericy dd
join collection_series cs on cs.series_id = dd.series_id
group by dd.x');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

查看此处的演示

我现在想不出用这种方式查询它的方法，但你可以先重构正常的提取结果。先用这种专门的格式构建它，然后呈现它

首先是获取标题（日期和地点等），然后需要根据日期对正文数据进行分组，将它们推送到另一个容器中。

粗略示例：

<?php
// temporary container
$temp = array();
while($row = whatever_fetch_function_assoc($result)) {
    $temp[] = $row; // push the rows
}
// restructure
$places = array_column($temp, 'chart_name'); // if this is not available (only PHP 5.5)
// foreach($temp as $v) {
//  $places[] = $v['chart_name']; // if its not available just use foreach
// }
// header creation
$headers = array_merge(array('Date'), $places); // for headers
foreach($temp as $v) { // then extract the dates
    $data[$v['date']][] = $v['y']; // group according to date
}
?>

然后，一旦结构完成，你就把它呈现在一个循环中（就像你通常会做的那样）：

<!-- presentation -->
<table cellpadding="10"> 
    <thead>
        <tr><?php foreach($headers as $h): // headers ?>
        <th><?php echo $h; ?></th>
        <?php endforeach; ?></tr>
    </thead>
    <tbody>
    <?php foreach($data as $date => $values): ?>
        <tr>
            <td><?php echo $date; // the date ?></td>
            <?php foreach($values as $d): ?>
            <td><?php echo $d; ?></td>
            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

某种样本输出