时间戳

首页 > 解决方案 > 如何将 2 个表连接在一起？

sql - 如何将 2 个表连接在一起？

问题描述

我正在尝试使用 2 个现有表创建一个连接表。如下所示：

这是第一个表查询，看起来像这样

https://ibb.co/sg2MXKf

SELECT
    DATEPART( week, dbo.Income.IncomeDate ) AS [Week Income],
    DATEPART( YEAR, dbo.Income.IncomeDate ) AS [Year],
    SUM ( dbo.Income.CardAmount ) AS [Total Card],
    SUM ( dbo.Income.CashAmount ) AS [Total Cash],
    SUM ( dbo.Income.TipsAmount ) AS [Total Tip],
    SUM ( dbo.Income.SalaryAmount ) AS [Total Salary],
    SUM ( dbo.Income.Adjustment ) AS [Total Adjustment] 
FROM
    dbo.Income 
GROUP BY
    DATEPART( week, dbo.Income.IncomeDate ),
    DATEPART( YEAR, dbo.Income.IncomeDate ) 
ORDER BY
    DATEPART(YEAR, dbo.Income.IncomeDate )

这是第二个表查询，看起来像这样

https://ibb.co/z8sRwpT

SELECT
    DATEPART( wk, dbo.Transactions.PaymentMadeOn ) AS [Week],
    COUNT (DATEPART( wk, dbo.Transactions.PaymentMadeOn )) AS [Expenses Count],
    DATEPART( YEAR, dbo.Transactions.PaymentMadeOn ) AS [Year],
    SUM ( dbo.Transactions.PaymentAmount ) AS [Total] 
FROM
    dbo.Transactions 
GROUP BY
    DATEPART( wk, dbo.Transactions.PaymentMadeOn ),
    DATEPART( YEAR, dbo.Transactions.PaymentMadeOn ) 
ORDER BY
    DATEPART( YEAR, dbo.Transactions.PaymentMadeOn )

我所期望的是这样的。表 1 和表 2 加起来，总费用加起来。

https://ibb.co/0DbZLYV

标签： sqldatabase

解决方案

正如 SO 人所评论的那样，我们无法访问描述您预期结果的图像。但是，我们了解您正在查看JOIN您所显示的两个查询的结果。

方法如下：将每个查询转换为子查询，然后将JOIN它们放在共同的关键字段上；在您的用例中，这必须是Yearand Week。该ORDER BY子句需要移动到外部查询。在SELECT,WHERE和ORDER BY子句中，您可以使用您定义的别名（此处A和B）自由访问子查询中的所有字段。

示例代码（你将不得不适应SELECT，我不知道你想要它看起来像什么）：

SELECT
    A.[Week Income],
    A.[Year],
    A.[Total] - B.[Total Salary] AS [Balance]
    ...
 FROM (           
    SELECT
        DATEPART( week, dbo.Income.IncomeDate ) AS [Week Income],
        DATEPART( YEAR, dbo.Income.IncomeDate ) AS [Year],
        SUM ( dbo.Income.CardAmount ) AS [Total Card],
        SUM ( dbo.Income.CashAmount ) AS [Total Cash],
        SUM ( dbo.Income.TipsAmount ) AS [Total Tip],
        SUM ( dbo.Income.SalaryAmount ) AS [Total Salary],
        SUM ( dbo.Income.Adjustment ) AS [Total Adjustment] 
    FROM
        dbo.Income 
    GROUP BY
        DATEPART( week, dbo.Income.IncomeDate ),
        DATEPART( YEAR, dbo.Income.IncomeDate ) 
 ) AS A 
 LEFT JOIN (  
    SELECT
        DATEPART( wk, dbo.Transactions.PaymentMadeOn ) AS [Week],
        COUNT (DATEPART( wk, dbo.Transactions.PaymentMadeOn )) AS [Expenses Count],
        DATEPART( YEAR, dbo.Transactions.PaymentMadeOn ) AS [Year],
        SUM ( dbo.Transactions.PaymentAmount ) AS [Total] 
    FROM
        dbo.Transactions 
    GROUP BY
        DATEPART( wk, dbo.Transactions.PaymentMadeOn ),
        DATEPART( YEAR, dbo.Transactions.PaymentMadeOn ) 
  ) AS B ON A.[Week Income] = B.[Week] AND A.[Year] = B.[Year]
ORDER BY
    A.[Year],
    A.[Week Income]

推荐阅读