php - 无重复(分组依据)字段 1 循环，字段 2 位于水平线

Question

假设我有一个名为“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 View 中:

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页面输出有什么解决方案吗？

Answer 1

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)上使用此索引，以消除对临时表和排序过程的需要