我目前有一个数据表，其中包含我已完成的工作的信息。
ID | date | income | payment | profit
==================================================
1 | 2019/01/01 | 100 | 75 | 25
2 | 2019/01/03 | 200 | 150 | 50
3 | 2019/02/02 | 350 | 200 | 150
4 | 2019/04/05 | 100 | 75 | 25
5 | 2019/05/03 | 500 | 300 | 200
6 | 2019/07/07 | 200 | 160 | 40
我希望使用highcharts.js将数据转换为条形图，但首先我需要按下表的形式按月对所有内容进行分组
Month | Income | Payment | Profit
========================================
January | 300 | 225 | 75
February| 350 | 200 | 50
March | 0 | 0 | 0
April | 100 | 75 | 25
May | 700 | 460 | 240
我不确定如何根据日期查找值。
我一直在寻找这样的东西：
select date ,income, payment, From table1
SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `income`) as jan_income
SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `payment`) as jan_payment
SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `profit`) as jan_profit
并使用表填充
table2表if (mysqli_num_rows($query) > 0) {
// output data of each row
while($result = mysqli_fetch_assoc($query)) {
echo "<tr>
<td>January</td>
<td>".$result['jan_income']."</td>
<td>".$result['jan_payment']."</td>
<td>".$result['jan_profit']."</td>
</tr>
<tr>
<td>February</td>
<td>".$result['feb_income']."</td>
<td>".$result['feb_payment']."</td>
<td>".$result['feb_profit']."</td>
</tr>
如果有人可以建议实现此目标的正确方法，我将不胜感激。
更新...基于NBK答案的新PHP
<tbody>
<?php
//Connect to database (same connection details as all tables so know this works)
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT date, income, payment FROM add_job
DATE_FORMAT(`date`,'%M %Y') AS MontnameYear,
MIN(DATE_FORMAT(`date`,'%M')) AS MonthName,
SUM(`income`) AS income,
SUM(`payment`) AS payment,
MIN(`date`) AS mdate
GROUP BY DATE_FORMAT(`date`,'%M %Y')
ORDER by mdate;";
$query = mysqli_query($conn, $sql);
if (mysqli_num_rows($query) > 0) {
// output data of each row
while($result = mysqli_fetch_assoc($query)) {
echo "<tr>
<td>".$result['MonthName']."</td>
<td>".$result['income']."</td>
<td>".$result['payment']."</td>
<td>".$result['profit']."</td>
</tr>";
}
}
else {
echo "0 results";
}
mysqli_close($conn);
?>
</tbody>
最佳答案
使用此sql状态时，会更容易
Select
DATE_FORMAT(`date`,'%M %Y') MontnameYear,MIN(DATE_FORMAT(`date`,'%M')) MonthName
, SUM(`income`) income,SUM(`payment`) payment, SUM(`profit`) profit
,MIN(`date`) mdate
FROM table1
GROUP BY DATE_FORMAT(`date`,'%M %Y')
ORDER by mdate;
说明：
MonthnameYear需要将选择的ti分组以获得正确的收入...
MonthName是要在表中显示正确的Monthname，因为
您想要的话就花了2019年1月
SUM（..）很明显，它可以使您获得按月分组的列的总和，
年
mdate我必须正确地对其进行排序，所以我不得不使用实际日期
分类
DBfiddle example
这给你这个结果
MontnameYear MonthName income payment profit mdate
January 2019 January 300 225 75 2019-01-01 01:00:00
February 2019 February 350 200 150 2019-02-02 01:00:00
April 2019 April 100 75 25 2019-04-05 02:00:00
May 2019 May 500 300 200 2019-05-03 02:00:00
July 2019 July 200 160 40 2019-07-07 02:00:00
然后在您的php代码中执行此操作，只有一个tabletable行被循环通过，并产生相同的结果。
while($result = mysqli_fetch_assoc($query)) {
echo "<tr>
<td>".$result['MonthName']."</td>
<td>".$result['income']."</td>
<td>".$result['payment']."</td>
<td>".$result['profit']."</td>
</tr>"
}
关于php - 将数据库表中的数据按月按数据表分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57998684/