>假设我有一个名为"dp"的表。
Year | Month | Payment| Payer_ID | Payment_Recipient |
2008/2009 | July | 100000 | 1 | John |
2008/2009 | August | 200000 | 1 | Jane |
2009/2010 | August | 150000 | 1 | Jane |
2009/2010 | September | 175000 | 1 | John |
在 mysql 视图中：
Year | July | August | September |
2008/2009 | 100000 | 200000 | 0 |
2009/2010 | 0 | 150000 | 175000 |
我需要在我的 php 页面中输出这样的输出：
Year | July | August | September |
2008/2009 | 100000 | John | 200000 | Jane | 0 | - |
2009/2010 | 0 | - | 150000 | Jane | 175000 | John |
我使用其中一些代码：
$query_Recordset1 = "SELECT
year
, SUM(IF(Month='July', Payment, 0)) As 'July'
, SUM(IF(Month='August', Payment, 0)) As 'August'
, SUM(IF(Month='September', Payment, 0)) As 'September'
FROM
dp
WHERE
Payer_ID = 1
GROUP BY
Year";
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['year']; ?></td>
<td><?php
$monthfilter=$row_Recordset1['month'];
$valuefromfilter=$row_Recordset1['payment'];
if ($monthfilter == 'July')
{
echo $valuefromfilter;
}
else
{
echo "<center>";
echo "<font color=red><b>0</b></font>";
echo "</center><br>";
}
?></td>
<td>script for august</td>
<td>script for september</td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
结果是这样的：
Year | July | August | September |
2008/2009 | * | * | * |
2009/2010 | * | * | * |
- = 未定义的索引....
在 php 页面中输出的任何解决方案？
MySQL不支持PIVOT，因此您需要使用解决方法
请注意，您需要为每个月添加一个 SUM 规则
SELECT
Year
, SUM(IF(Month='July', Payment, 0)) As 'July'
, SUM(IF(Month='August', Payment, 0)) As 'August'
, SUM(IF(Month='September', Payment, 0)) As 'September'
FROM
dp
WHERE
Payer_ID = 1
GROUP BY
Year
请参阅演示 http://sqlfiddle.com/#!2/37cd5/11
注意 对于大型表，您应该在 （Payer_ID， Year） 上使用此索引，以消除对临时表和排序传递的需求
编辑检查下面的查询，这将使PHP客户端代码非常容易
SELECT
'Year'
, 'July'
, 'August'
, 'September'
UNION ALL
SELECT
Year
, SUM(IF(Month='July', Payment, 0)) As 'July'
, SUM(IF(Month='August', Payment, 0)) As 'August'
, SUM(IF(Month='September', Payment, 0)) As 'September'
FROM
dp
WHERE
Payer_ID = 1
GROUP BY
Year
见 http://sqlfiddle.com/#!2/37cd5/13
注意 对于大型表，您应该在 （Payer_ID， Year） 上使用此索引，以消除对临时表和排序传递的需求