SQL关联两表并按条件筛选唯一行的实现问题

问题场景 #

现有两张表，关联关系为Table 1的sn字段与Table 2的student_id字段匹配：

Table 1 结构及数据：

|sn|Name  |Age  |Address  |Date      |
|1 |Name 1|Age 1|Address 1|18-05-2023|
|2 |Name 2|Age 2|Address 2|21-05-2023|
|3 |Name 3|Age 3|Address 3|21-05-2023|
|4 |Name 4|Age 4|Address 4|21-05-2023|

Table 2 结构及数据：

|sn|student_id|addmission_no|transaction_no|Fee |transaction_date|
|10|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|11|2         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|12|3         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|13|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|14|2         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|15|3         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|16|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|17|2         |6464e25b3ad22|312009677062  |1050|20/05/2023      |
|18|3         |6464e25b3ad22|312009677062  |1050|20/05/2023      |
|19|4         |6464e25b3ad22|312009677062  |1050|20/05/2023      |

需求说明 #

关联两表，获取同时满足以下条件的唯一行：

1. Table 1.sn = Table 2.student_id
2. Table 1.Date = '21-05-2023'

预期返回3行，目标输出格式：

<------Table 1 Data-----------------><----------Table 2 Date---------->
|sn|Name  |Age  |Address  |Date      |sn|student_id|addmission_no|Fee |
|2 |Name 2|Age 2|Address 2|21-05-2023|17|2         |6464e25b3ad22|1050|
|3 |Name 3|Age 3|Address 3|21-05-2023|18|3         |6464e25b3ad22|1050|
|4 |Name 4|Age 4|Address 4|21-05-2023|19|4         |6464e25b3ad22|1050|

当前问题 #

尝试的SQL语句返回了所有匹配行，无法实现唯一行的需求：

SELECT table1.sn , table1.name, table1.age, table1.address,
table1.date, table2.sn, table2.student_id, table2.addmission_no,
table2.Fee FROM table1 INNER JOIN table2 ON
table1.sn=table2.student_id where table1.date='21-05-2023'

解决方案 #

观察目标输出，每个student_id对应Table 2中最晚交易日期的行，可通过以下两种方式实现：

方法1：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

先为Table 2中每个student_id的行按transaction_date降序排名，再取排名为1的行关联Table 1：

SELECT 
    t1.sn, t1.Name, t1.Age, t1.Address, t1.Date,
    t2.sn, t2.student_id, t2.addmission_no, t2.Fee
FROM Table1 t1
INNER JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY transaction_date DESC) AS rn
    FROM Table2
) t2 ON t1.sn = t2.student_id
WHERE t1.Date = '21-05-2023' AND t2.rn = 1;

方法2：子查询获取最大交易日期（兼容性更强） #

先找到每个student_id对应的最晚交易日期，再关联Table 2和Table 1：

SELECT 
    t1.sn, t1.Name, t1.Age, t1.Address, t1.Date,
    t2.sn, t2.student_id, t2.addmission_no, t2.Fee
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.sn = t2.student_id
INNER JOIN (
    SELECT student_id, MAX(transaction_date) AS max_trans_date
    FROM Table2
    GROUP BY student_id
) t2_max ON t2.student_id = t2_max.student_id AND t2.transaction_date = t2_max.max_trans_date
WHERE t1.Date = '21-05-2023';

说明 #

两种方法都能得到预期的3行结果：窗口函数方式更简洁高效，适合现代数据库；子查询方式兼容性更好，适用于不支持窗口函数的老版本数据库。

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