如何合并根据日期排序的两个或多个表 [英] How to merge two or more tables sorted according to date

问题描述

你好，我需要帮助我的学校项目。我有表try1和try2，我想加入两个显示收入和支出，相应日期从try1和try2

hello pls i need help on my school project. I have tables try1 and try2, i want to join the two showing the income and expenditure sorted with the date accordingly from the try1 and try2

try1
         +------+-------+--------+--------+
         |  id  | date  | name   |expensis|
         +------+-------+--------+--------+
         |  1   |17/11/1|  shoe  |   200  |
         +------+-------+--------+--------+
         |  2   |17/11/2|  books |   300  |
         +------+-------+--------+--------+
         |  3   |17/11/2| T.fair |   400  |
         +------+-------+--------+--------+
try2
         +------+-------+--------+--------+
         |  id  | date  | name   | source |
         +------+-------+--------+--------+
         |  1   |17/11/1|  Dad   |  1000  |
         +------+-------+--------+--------+
         |  2   |17/11/2| uncle  |        |
         +------+-------+--------+--------+
         |  3   |17/11/4|  Bro   | 150    |
         +------+-------+--------+--------+
    
    RESULT
             +-------+--------+--------+
             | date  |expensis| source |
             +-------+--------+--------+
             |17/11/1|  200   |  1000  |
             +-------+--------+--------+
             |17/11/2|  300   |        |
             +-------+--------+--------+
             |17/11/2|  400   |        |
             +-------+--------+--------+
             |17/11/4|        |  150   |
             +-------+--------+--------+

我尝试过：

What I have tried:

SELECT u.date, b.amount, h.amount
FROM
  (SELECT Date FROM try1 UNION SELECT Date FROM try2) u
  RIGHT JOIN try1 b ON u.Date=b.Date
 RIGHT JOIN try2 h ON u.Date=h.Date

推荐答案

JoséAmílcarFerreiraCasimiro（@jafcasimiro）为您提供了一个解决方案。



这是一篇很好的CodeProject文章，供您更好地理解连接

SQL连接的可视化表示 [ ^ ]



这是另一个帮助您了解子查询如何工作的方法：

SQL Server：Subquer ies [ ^ ]



其他几点...



a）你试图查询列 b。金额和 h.amount 但两个表都没有这样的列名。



b ）避免对列名使用保留字 - 例如 date 和 name 。如果您确实使用它们，请记住用方括号将它们包围起来，以区分列名和类型（或其他保留字）。

José Amílcar Ferreira Casimiro (@jafcasimiro) has given you one solution.

Here is a good CodeProject article for you to understand joins better
Visual Representation of SQL Joins[^]

And here is another one to help you understand how subqueries work:
SQL Server: Subqueries[^]

A couple of other points ...

a) you try to query on columns b.amount and h.amount but neither table has such a column name.

b) Avoid using "Reserved Words" for column names - for example date and name. If you do use them then remember to surround them with square brackets to distinguish between the column name and the type (or other reserved word).

这篇关于如何合并根据日期排序的两个或多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！