双倍结果问题

Question

我已经做了两天了，没办法，我需要帮助。

目标-除其他外.

• 和所有项目时间表条目
• 和所有项目发票

按项目分组(projectID)并显示在表中。

在与上面提到的表相同的页面上，有一个表单允许用户输入一个新的时间表条目。在刷新时，运行和表将显示更新的时间表总计。

Current --当我提交时间表表单(例如:项目X为1.25小时)时，会发生三件事。

1. 表单数据被发布到数据库。这工作得很好。
2. 页面刷新和项目X的时间表条目更新了2.5个小时(应该增加1.25小时)，
3. 发票总额也按该项目的总发票值递增。也就是说，如果已经为项目X开出了5000美元的发票，增加一个新的时间表条目将把它推到10,000美元-15,000美元.等等。

查询-如下所示：

<?php
    $query = "SELECT tsm_projects.projectName AS projectName,   tsm_projects.projectID AS projectID, tsm_projects.value AS value, tsm_projects.estHours AS estHours, tsm_clients.clientName AS clientName, tsm_projects.estHours - SUM(tsm_timesheets.time) AS remaining, SUM(tsm_invoices.invoiceValue) AS invoiceValue, SUM(tsm_timesheets.time) AS totalTime FROM tsm_projects
    LEFT JOIN tsm_timesheets ON tsm_projects.projectID = tsm_timesheets.projectID
    LEFT JOIN tsm_clients ON tsm_clients.clientID = tsm_projects.clientID
    LEFT JOIN tsm_invoices ON tsm_invoices.projectID = tsm_projects.projectID
    WHERE projectType = 'active'
    GROUP BY tsm_timesheets.projectID 
    ORDER BY tsm_projects.projectName";
    $result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
    echo "<tr><td>". $row['projectName'] . " [" . $row['clientName'] . "]</td><td>$" . number_format($row[value], 2, '.', ',') . " [" . $row['estHours'] . "]</td><td>$" . $row['invoiceValue'] . "</td><td>" . number_format($row[totalTime], 2, '.', ',') ." [";
    if($row["remaining"] <= 0) {
    echo "<span class=\"redText\">" . $row['remaining'] . "</span>"; }
    else {
    echo "<span class=\"greenText\">+" . $row['remaining'] . "</span>"; }
    echo "]</td></tr>"; }
?>

SQL -我猜时间表和/或发票表可能相关：

TABLE `tsm_timesheets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projectID` varchar(10) NOT NULL,
  `activity` varchar(20) NOT NULL,
  `date` date NOT NULL,
  `time` decimal(4,2) NOT NULL,
  `timesheetID` varchar(10) NOT NULL,
  `memberID` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
)

TABLE `tsm_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projectID` varchar(10) NOT NULL,
  `month` varchar(15) NOT NULL,
  `notes` varchar(255) NOT NULL,
  `invoiceValue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `gstValue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fee` decimal(6,2) NOT NULL DEFAULT '0.00',
  `costs` decimal(6,2) NOT NULL DEFAULT '0.00',
  `invoiceNumber` varchar(15) NOT NULL,
  `dateSent` date NOT NULL,
  `dateDeposit` date NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `addedBy` varchar(20) NOT NULL,
  `invoiceID` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
)

希望有人能帮忙。提前谢谢。

雷夫

Answer 1

聚合函数是根据每个结果行而不是每一个表行计算的。

您需要单独执行分组：

LEFT JOIN (
  SELECT projectID, SUM(invoiceValue) AS SumInvoiceValue
  FROM tsm_invoices
  GROUP BY projectID) i ON i.projectID = tsm_projects.projectID

整个查询：

SELECT p.projectName AS projectName, p.projectID AS projectID, p.value AS value,
    p.estHours AS estHours, c.clientName AS clientName,
    p.estHours - t.SumTime AS remaining,
    i.SumInvoiceValue AS invoiceValue,
    t.SumTime AS totalTime
FROM tsm_projects p
    LEFT JOIN tsm_clients c ON c.clientID = p.clientID
    LEFT JOIN (
        SELECT projectID, SUM(time) AS SumTime
        FROM tsm_timesheets
        GROUP BY projectID
      ) t ON p.projectID = t.projectID
    LEFT JOIN (
        SELECT projectID, SUM(invoiceValue) AS SumInvoiceValue
        FROM tsm_invoices
        GROUP BY projectID
      ) i ON i.projectID = p.projectID
WHERE projectType = 'active'
GROUP BY p.projectID 
ORDER BY p.projectName