使用子查询来联接表 [英] Using a subquery to JOIN a table

问题描述

我有一个起始表，它来自查询：

I have an starting table, which comes from a query:

CREATE TABLE my_base 
(
    the_id varchar(6) NOT NULL, 
    the_pay int NOT NULL, 
    the_name varchar(10) NOT NULL
)

INSERT INTO my_base
VALUES ('LMUS01', '2000', 'JOE'), 
       ('LMUS02', '1500', 'JACK')

和一个债务数据 my_table：

And a debt data "my_table" :

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_amount int NOT NULL, 
    the_debt_date date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '180', '2/12/2019'), 
       ('LMUS01', '200', '2/11/2019'), 
       ('LMUS01', '300', '2/13/2019'), 
       ('LMUS02', '100', '2/10/2019'), 
       ('LMUS02', '150', '2/12/2019')

我想要的查询是 my_base中的所有记录都加入了 my_table中变量 the_debt_date的值：

And the query I want is all records in "my_base" joined to the min value of variable "the_debt_date" from "my_table":

'LMUS01','2000','JOE','200','2/11/2019'
'LMUS02','1500','JACK','100','2/10/2019' 

当我这样做时我只是在 my_table中使用一个查询：

When I do it one by one just in "my_table" I use this query:

SELECT the_debt_amount, the_debt_date FROM my_table 
WHERE the_debt_id = 'LMUS01' 
AND the_debt_date = (select min(the_debt_date) 
FROM my_table WHERE the_debt_id = 'LMUS01')

但是我希望所有记录都来自 my_base。我尝试过：

But I want all the records from "my_base". I tried:

SELECT * FROM my_base 
LEFT JOIN my_table ON the_debt_id = the_id WHERE the_id = the_debt_id 
AND the_debt_date = (select min(the_debt_date) FROM my_table WHERE the_id = the_debt_id)

但是它发送了一个错误。任何帮助将不胜感激。

But it sends an error. Any help will be appreciated.

推荐答案

您在my_base中有两条记录...

You have two records in my_base...

SELECT * 
FROM my_base 
LEFT JOIN my_table ON the_debt_id = the_id
WHERE the_id = the_debt_id 
AND the_debt_date = (select min(the_debt_date) 
                     FROM my_table 
                     WHERE the_id = the_debt_id)

同时显示两者记录，但包括冗余字段。取消*并包括对您需要的特定字段的引用。

Displays both records but includes redundant fields. Eliminate the * and include a reference to the specific fields you need.

SELECT my_base.the_id 
       , my_base.the_pay 
       , my_base.the_name 
       , my_table.the_debt_amount 
       , my_table.the_debt_date 
FROM my_base 
LEFT JOIN my_table ON the_debt_id = the_id
WHERE the_id = the_debt_id 
AND the_debt_date = (select min(the_debt_date) 
                     FROM my_table 
                     WHERE the_id = the_debt_id)

这篇关于使用子查询来联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！