sql - 连接两个表，以便结果显示在多列中

Question

假设我们有一个包含这样数据的表

+----------------+------------+-------+
| Company_number |    Date    | Value |
+----------------+------------+-------+
|            123 | 2017-01-01 |     5 |
|            123 | 2017-02-01 |    10 |
|            123 | 2018-01-01 |    15 |
|            456 | 2018-01-05 |    33 |
+----------------+------------+-------+

如何接收格式的数据

+----------------+------+------------+------------+
| Company_number | Mont | Value 2017 | Value 2018 |
+----------------+------+------------+------------+
|            123 |   01 |          5 |         15 |
|            123 |   02 |         10 |            |
|            456 |   01 |         33 |            |
+----------------+------+------------+------------+

我不知道如何选择 2017 年的所有数据，并使用公司编号和月份将其与 2018 年的数据连接起来。

我已经取出了2017年的所有记录，将其放入另一个表中，并尝试使用此选择，但当没有公共(public)月份时它不会显示记录(没有二月份的记录)。

select 
s.company_number
,datepart(month,s.date) as Month
,s.Value as Value_2017
,r.Value as Value_2018
from table1 as s
left join table2 as r on concat(r.company_number,datepart(month,r.date))=concat(s.company_number,datepart(month,s.date))
where datepart(year,s.date)='2018'



select results (with no February)
    +----------------+-------+------------+------------+
    | company_number | Month | Value_2017 | Value_2018 |
    +----------------+-------+------------+------------+
    |            123 |     1 |         15 | 5          |
    |            456 |     1 |         33 | NULL       |
    +----------------+-------+------------+------------+

Answer 1

试试这个:

SELECT 
    COALESCE([t1].[company_number], [t2].[company_number]) AS [Company_Number],
    MONTH(COALESCE([t1].[date], [t2].[date])) AS [Month],
    [t1].[value] AS [2018 Value], 
    [t2].[value] AS [2017 Value]
FROM 
    [table1] AS [t1]
FULL OUTER JOIN 
    [table2] as [t2] on [t1].[company_number] = [t2].[company_number] 
    AND MONTH([t1].[date]) = MONTH ([t2].[date])

刚刚尝试过(可以在编辑器中复制/粘贴并执行)并且工作正常:

DECLARE @t1 TABLE (Company_number INT, [Date] Date, Value INT)
DECLARE @t2 TABLE (Company_number INT, [Date] Date, Value INT)
INSERT INTO @t1 VALUES (123, '2017-01-01', 5), (123, '2017-02-01', 10)
INSERT INTO @t2 VALUES (123, '2018-01-01', 15), (456, '2018-01-05', 33)

SELECT 
    COALESCE([t1].Company_number, [t2].Company_number) AS [Company_Number],
    MONTH(COALESCE([t1].[date], [t2].[date])) AS [Month],
 [t1].[value] AS [2018 Value], 
    [t2].[value] AS [2017 Value]
FROM 
    @t1 AS [t1]
FULL OUTER JOIN 
    @t2 as [t2] on [t1].[company_number] = [t2].[company_number] 
    AND MONTH([t1].[date]) = MONTH ([t2].[date])