SQL入门：如何连接两张表并存入新表？含按年份匹配需求

当然可行！这是SQL里很常见的关联查询场景，核心就是通过symbol字段精准匹配，同时让两张表date字段的年份保持一致，最终把结果存入新表new_table。下面给你详细拆解实现步骤和代码：

核心思路 #

• 用连接查询关联两张表，匹配条件分两部分：
  1. 两张表的symbol字段完全相等
  2. 两张表date字段的年份相同
• 通过CREATE TABLE ... AS SELECT语法直接将查询结果写入新表

具体SQL代码（分数据库示例） #

MySQL / SQL Server 版本 #

CREATE TABLE new_table AS
SELECT 
    t1.symbol,
    t1.date AS date1,
    t1.value_1 AS value1,
    t2.date AS date2,
    t2.value_2 AS value2
FROM 
    table1 t1
-- 用INNER JOIN只保留两边都匹配到的记录，若需保留table1所有记录可改用LEFT JOIN
INNER JOIN 
    table2 t2 
    ON t1.symbol = t2.symbol 
    AND YEAR(t1.date) = YEAR(t2.date);

PostgreSQL / Oracle 版本 #

-- PostgreSQL 写法
CREATE TABLE new_table AS
SELECT 
    t1.symbol,
    t1.date AS date1,
    t1.value_1 AS value1,
    t2.date AS date2,
    t2.value_2 AS value2
FROM 
    table1 t1
INNER JOIN 
    table2 t2 
    ON t1.symbol = t2.symbol 
    AND EXTRACT(YEAR FROM t1.date) = EXTRACT(YEAR FROM t2.date);

-- Oracle 可选写法（用TO_CHAR提取年份）
CREATE TABLE new_table AS
SELECT 
    t1.symbol,
    t1.date AS date1,
    t1.value_1 AS value1,
    t2.date AS date2,
    t2.value_2 AS value2
FROM 
    table1 t1
INNER JOIN 
    table2 t2 
    ON t1.symbol = t2.symbol 
    AND TO_CHAR(t1.date, 'YYYY') = TO_CHAR(t2.date, 'YYYY');

针对你的示例数据验证 #

拿你提供的表数据举例：

• table1中symbol="A"且年份为2010的记录，会和table2中symbol="A"且年份为2010的记录成功关联，正好得到你期望的结果行：

  symbol date1          value1           date2          value2
  "A"    "2010-12-31"   "29.635193133"   "2010-01-05"   "22.324748927"
  

• 其他年份的记录（比如2011年的"A"）也会按同样规则匹配，只要table2中有对应年份的同symbol记录，就会被纳入结果。

内容的提问来源于stack exchange，提问作者jdow