将两个SQL表组合成一个排序的HTML输出 - Combining two SQL tables into one sorted HTML output

Combining two SQL tables into one sorted HTML output

我已经创建了两个SQL表的示例。第一个表需要简单的乘法来产生最终的"收益"，而第二个表直接将这个值存储为"奖金"。我想要能够做的是创建一个PHP页面，允许我选择任何员工，并让它生成一个HTML表(如我的代码片段中的第三个表)。

我不确定是否可以在SQL查询中以某种方式"JOIN"这两个表。我可以在每个表上运行两个单独的查询，两个生成两个单独的数组，但我不知道如何按照日期顺序对表进行排序。

MONTHLY DATA (sql table)
<table>
  <tr>
    <th>Month</th>
    <th>Employee</th>
    <th>Net Sales</th>
    <th>Com%</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Tim</td>
    <td>275,500</td>
    <td>.05</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Sara</td>
    <td>219,200</td>
    <td>.06</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Tim</td>
    <td>391,500</td>
    <td>.055</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>359,800</td>
    <td>.06</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>116,100</td>
    <td>.10</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Alex</td>
    <td>217,100</td>
    <td>.04</td>
  </tr>
</table>
<br />
<br />BONUS DATA (sql table):
<table>
  <tr>
    <th>Month</th>
    <th>Employee</th>
    <th>Bonus</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Tim</td>
    <td>500</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>Sara</td>
    <td>800</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Tim</td>
    <td>600</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Sara</td>
    <td>950</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>Alex</td>
    <td>450</td>
  </tr>
</table>
<br />
<br />View Sara's Monthly Revenue (html output):
<table>
  <tr>
    <th>Month</th>
    <th>Toal Rev</th>
    <th>Source</th>
  </tr>
  <tr>
    <td>Oct</td>
    <td>13,152</td>
    <td>"from sales"</td>
  </tr>
  <tr>
    <td>Oct</td>
    <td>800</td>
    <td>"bonus"</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>33,198</td>
    <td>"from sales"</td>
  </tr>
  <tr>
    <td>Nov</td>
    <td>950</td>
    <td>"bonus"</td>
  </tr>
  <tr>
    <td>.</td>
  </tr>
  <tr>
    <td></td>
    <td>48,100</td>
    <td>TOTAL</td>
  </tr>
</table>

月度数据

Month    Employee    Net Sales    Com%
Oct      Tim         275,500      .05
Oct      Sara        219,200      .06
Nov      Tim         391,500      .055
Nov      Sara        359,800      .06
Nov      Sara        116,100      .10
Nov      Alex        217,100      .04

sql表:

Month   Employee    Bonus
Oct     Tim         500
Oct     Sara        800
Nov     Tim         600
Nov     Sara        950
Nov     Alex        450

您需要分别从两个表中取出数据，然后将它们合并在一起。像下面这样的SQL将为您拉回数据，之后计算最终总数应该很简单。

SELECT
    Month,
    [Total Rev],
    Source
FROM
    (
    SELECT
        Employee,
        Month,
        SUM([Net Sales] * [Com%]) AS [Total Rev],
        'from sales' AS Source
    FROM
        [MONTHLY DATA]
    GROUP BY
        Employee,
        Month
    UNION
    SELECT
        Employee,
        Month,
        SUM(Bonus) AS [Total Rev],
        'bonus'
    FROM
        [BONUS DATA]
    GROUP BY
        Employee,
        Month
    ) AS tbl1
WHERE
    Employee='Sara'
ORDER BY
    Month,
    Source DESC