Mysql使用某些数据创建表

Question

我有一个满是发票的数据库。在年底，我想做一次年度回顾。我希望评论看起来类似于这张图片：

​

​

这是我所拥有的数据的一个例子。

​

​

这是我到现在为止所做的查询。如果有人能帮上忙，我会很感激的。

SELECT invID, invDate, craftArr, vesselTotal, craftGT
FROM company, agents, vessels, invoice
WHERE invoice.status =  '0'
AND invoice.craftArr >  '2014-01-01'
AND invoice.craftArr <  '2014-12-31'
AND invoice.invCraft = vessels.craftID
AND vessels.craftComp = company.compID
AND company.compAgent = agents.agentID
ORDER BY craftArr ASC

Answer 1

我会试着给你一个提示，如何实现你的目标。

通过GT范围的

1. 循环：

1.1。使用此查询收集给定范围内每个月的合计。查询将为您提供当前行的金额列表。PHP备注。您可以从您的脚本传递两个变量，用于GT范围的开始和结束。

SELECT MONTH(craftArr) as monthNumber, sum(vesselTotal) as total FROM company, agents, vessels, invoice WHERE invoice.status = '0' AND invoice.craftArr > '2014-01-01' AND invoice.craftArr < '2014-12-31' AND invoice.invCraft = vessels.craftID AND vessels.craftComp = company.compID AND company.compAgent = agents.agentID AND craftGT between ($START_CURRENT RANGE, $END_CURRENT_RANGE) group by MONTH(craftArr) ORDER BY monthNumber ASC

1.2。将$total变量设置为0。

1.3。使用一个循环遍历月份和查询结果：

备注。如果有几个月没有发票，则缺少金额。您需要在迭代时处理它(monthNumber可以用于此目的)。

1.3.1。在适当的单元格中输出金额，如果本月没有结果，则输出金额为0。

1.3.2。按当前数量增加$total

1.4。当前行的输出总计

Answer 2

您只需要条件聚合。首先，修复您的查询，使用适当的连接。

下面是它的样子的草图：

SELECT craftGT,
       sum(case when month(craftArr) = 1 then vesselTotal else 0 end) as Jan,
       sum(case when month(craftArr) = 2 then vesselTotal else 0 end) as Feb,
       . . .
       sum(case when month(craftArr) = 12 then vesselTotal else 0 end) as Dec,
       sum(vesselTotal) as Total
FROM company join
     agents
     on company.compAgent = agents.agentID join
     vessels
     on vessels.craftComp = company.compID join
     invoice
     on invoice.invCraft = vessels.craftID
WHERE invoice.status =  '0' AND
      invoice.craftArr >  '2014-01-01' AND
      invoice.craftArr <  '2014-12-31'
GROUP BY craftGT;

您似乎希望将craftGT放入范围中。目前还不清楚细节是什么样子，但您可以使用case来完成此操作

SELECT (case when CraftGT BETWEEN 0 and 1000 THEN '0-1000'
             when CraftGT BETWEEN 1001 and 1999 THEN 1000-1999'
             . . .
        end) as craftGTRange
. . .
GROUP BY craftGTRange

Answer 3

从下面发布的示例中，有两件事应该立即弄清楚。首先，PHP不是我的强项！第二个问题是，即便如此，构造所需的输出也非常容易！这个概念可以扩展到类似数据网格的东西，这带来了更大的灵活性。

请注意，为了便于理解，我简化了“GT括号”。

数据集..。

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
 (invID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,InvDate DATE NOT NULL
 ,craftArr DATETIME NOT NULL
 ,vesselTotal DECIMAL(6,2) NOT NULL
 ,craftGT INT NOT NULL
 );

INSERT INTO my_table VALUES
(23 ,'2014-02-01','2014-01-31 23:10:00', 595.07, 13992),
(24 ,'2014-02-02','2014-02-02 03:20:00', 956.08, 33933),
(25 ,'2014-02-05','2014-02-05 00:00:00', 952.06, 52926),
(26 ,'2014-02-05','2014-02-05 00:35:00', 637.38, 30998),
(50 ,'2014-02-05','2014-02-05 01:00:00',1196.25, 42289),
(28 ,'2014-02-06','2014-02-06 00:40:00', 480.64, 24344),
(30 ,'2014-02-06','2014-02-06 15:10:00', 246.7 , 8500),
(31 ,'2014-02-07','2014-02-06 23:30:00',2082.69, 71304),
(34 ,'2014-02-07','2014-02-07 00:10:00',1429.2 , 77499),
(32 ,'2014-02-07','2014-02-07 00:40:00', 557.34, 26302);

SELECT * FROM my_table;
+-------+------------+---------------------+-------------+---------+
| invID | InvDate    | craftArr            | vesselTotal | craftGT |
+-------+------------+---------------------+-------------+---------+
|    23 | 2014-02-01 | 2014-01-31 23:10:00 |      595.07 |   13992 |
|    24 | 2014-02-02 | 2014-02-02 03:20:00 |      956.08 |   33933 |
|    25 | 2014-02-05 | 2014-02-05 00:00:00 |      952.06 |   52926 |
|    26 | 2014-02-05 | 2014-02-05 00:35:00 |      637.38 |   30998 |
|    28 | 2014-02-06 | 2014-02-06 00:40:00 |      480.64 |   24344 |
|    30 | 2014-02-06 | 2014-02-06 15:10:00 |      246.70 |    8500 |
|    31 | 2014-02-07 | 2014-02-06 23:30:00 |     2082.69 |   71304 |
|    32 | 2014-02-07 | 2014-02-07 00:40:00 |      557.34 |   26302 |
|    34 | 2014-02-07 | 2014-02-07 00:10:00 |     1429.20 |   77499 |
|    50 | 2014-02-05 | 2014-02-05 01:00:00 |     1196.25 |   42289 |
+-------+------------+---------------------+-------------+---------+
10 rows in set (0.00 sec)

SELECT FLOOR(craftgt/1000)*1000 GT
     , MONTH(craftarr) month
     , SUM(vesseltotal) income 
  FROM my_table 
 WHERE craftarr BETWEEN '2014-01-01' AND '2015-01-01' 
 GROUP 
    BY FLOOR(craftgt/1000)*1000
     , MONTH(craftarr);
+-------+-------+---------+
| GT    | month | income  |
+-------+-------+---------+
|  8000 |     2 |  246.70 |
| 13000 |     1 |  595.07 |
| 24000 |     2 |  480.64 |
| 26000 |     2 |  557.34 |
| 30000 |     2 |  637.38 |
| 33000 |     2 |  956.08 |
| 42000 |     2 | 1196.25 |
| 52000 |     2 |  952.06 |
| 71000 |     2 | 2082.69 |
| 77000 |     2 | 1429.20 |
+-------+-------+---------+

php..。

<?php
include('path/to/connection/scr.ipt');

$query = "
SELECT FLOOR(craftgt/1000)*1000 GT
     , MONTH(craftarr) month
     , SUM(vesseltotal) income
  FROM my_table
 WHERE craftarr BETWEEN '2014-01-01' AND '2015-01-01'
 GROUP
    BY FLOOR(craftgt/1000)*1000
     , MONTH(craftarr);
";

?>
<table>
  <tr>
    <td>GT</td>
    <td>Jan</td>
    <td>Feb</td>
    <td>Mar</td>
    <td>Apr</td>
    <td>May</td>
    <td>Jun</td> <!--This bit could have been automated too -->
    <td>Jul</td>
    <td>Aug</td>
    <td>Sep</td>
    <td>Oct</td>
    <td>Nov</td>
    <td>Dec</td>
    <td>Total</td>
 </tr>
<?
$result = mysqli_query($conn,$query);
while($row = mysqli_fetch_assoc($result)){

$output = '';
$total = 0;
for($i=1;$i<=12;$i++){

if($row['month'] == $i){ $income = $row['income'] ;} else { $income = 0; }
$total += $income;
$output .= "<td>$income</td>";
}

echo "<tr><td>".$row['GT']."</td>".$output."<td>$total</td></tr>";

}
?>
</tr>
</table>

输出：

GT    Jan    Feb      Mar Apr May Jun Jul Aug Sep Oct Nov Dec  Total
8000  0      246.70   0   0   0   0   0   0   0   0   0   0    246.7
13000 595.07 0        0   0   0   0   0   0   0   0   0   0    595.07
24000 0      480.64   0   0   0   0   0   0   0   0   0   0    480.64
26000 0      557.34   0   0   0   0   0   0   0   0   0   0    557.34
30000 0      637.38   0   0   0   0   0   0   0   0   0   0    637.38
33000 0      956.08   0   0   0   0   0   0   0   0   0   0    956.08
42000 0      1196.25  0   0   0   0   0   0   0   0   0   0    1196.25
52000 0      952.06   0   0   0   0   0   0   0   0   0   0    952.06
71000 0      2082.69  0   0   0   0   0   0   0   0   0   0    2082.69
77000 0      1429.20  0   0   0   0   0   0   0   0   0   0    1429.2