mysql - 如何从两个表中按日期获取数据？

我试图从两个mysql表中获取数据并按日期排列它们。这两个表与收入和支出有关。我要实现的目标是，首先从收入表中显示日期为15/04/2014的数据，然后从支出表中显示日期为15/04/2014的数据，然后再次循环显示下一个日期并显示收入表和支出表中与14/04/14相关的数据。当前代码循环的次数为no。收入表中的条目数。如何纠正呢？代码如下：

<?php
  include "connection.php";
?>
<table width="100%" border="1" align="center" cellpadding="2" cellspacing="2">
                <tr>
                  <td>Date</td>
                  <td>Particulars</td>
                  <td>Transaction Type</td>
                  <td>Sub Expense</td>
                  <td>Expense</td>
                  <td>Subincome</td>
                  <td>Income</td>
                  <td>Cash in Hand</td>
                  <td>Remarks</td>
                </tr>
                <?php
                $datequery = mysql_query("select date from income order by date asc") or die (mysql_error());
                while ($row = mysql_fetch_array($datequery))
                {
                $currentdate = $row['date'] ;

                echo $currentdate."<br>";

                            $result = mysql_query("SELECT *  FROM income WHERE date = '$currentdate' ") or die (mysql_error());
                            while ($row = mysql_fetch_array($result))
                            {
                                ?>
                            <tr>
                              <td><?php echo $row['date']; ?></td>
                              <td><?php echo $row['particulars']; ?></td>
                              <td><?php echo $row['trntype']; ?></td>
                              <td>&nbsp;</td>
                              <td>&nbsp;</td>
                              <td><?php $subincome = $row['subincome']; echo $subincome ;?></td>
                              <td><?php $income =  $row['income']; echo $income ?></td>
                              <td><?php $balance = $balance + $row['subincome'] + $row['income'] - $row['subexpense'] - $row['expense']; ;  echo $balance; ?></td>
                              <td><?php echo $row['remarks']; ?></td>
                            </tr>
                                            <?php
                            }
                                        $result = mysql_query("SELECT *  FROM expenditure WHERE date = '$currentdate' ") or die (mysql_error());
                                        while ($row = mysql_fetch_array($result))
                                        {
                                            ?>
                                        <tr>
                                          <td><?php echo $row['date']; ?></td>
                                          <td><?php echo $row['particulars']; ?></td>
                                          <td><?php echo $row['trntype']; ?></td>
                                          <td><?php echo $row['subexpense']; ?></td>
                                          <td><?php echo $row['expense']; ?></td>
                                          <td>&nbsp;</td>
                                          <td>&nbsp;</td>
                                          <td><?php $balance = $balance + $row['subincome'] + $row['income'] - $row['subexpense'] - $row['expense'];  echo $balance; ?></td>
                                          <td><?php echo $row['remarks']; ?></td>
                                        </tr>
                                        <?php
                                        }
                $date = strtotime($currentdate);
                $date = strtotime("+1 day", $date);;
                $currentdate =  date('Y-m-d', $date);

                }
                ?>
              </table>

最佳答案

您可以使用union all运算符进行单个查询，然后进行相应的排序。在下面的查询中，我添加了一个虚拟列进行排序，因此我可以确保收入行总是在支出行之前：

SELECT  *
FROM    (SELECT *, 1 AS dummy FROM income
         UNION ALL
         SELECT *, 2 AS dummy FROM expenditure) t
ORDER BY `date` ASC, dummy ASC

关于mysql - 如何从两个表中按日期获取数据？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23080767/