为内部where子句选择具有外部值的子查询select

Question

用户可以在订阅表中有多条记录。

我想做的是返回他们的名字、姓氏、电子邮件、开始日期(他们第一次订阅的开始日期，按start_date asc limit 1从订阅顺序中选择start_date，但我需要它用于特定用户(

// users
id
first_name
last_name
email
// subscriptions
id
email
start_date (TIMESTAMP)
end_date (TIMESTAMP)
status

我以为这会起作用，但似乎没有：

select 
distinct(users.email), status, first_name, last_name,
(select start_date from subscriptions where subscriptions.email = users.email order by start_date asc limit 1) as start_date 
from 
subscriptions sub 
join 
users u on sub.email = u.email
order by 
sub.end_date desc

这会为每个人返回相同的start_date，因为它可能会提取匹配的第一个start_date。

SQL篡改了架构：http://sqlfiddle.com/#！9/245c05/5

Answer 1

此查询：

select s.*
from subscriptions s
where s.start_date = (select min(start_date) from subscriptions where email = s.email) 

返回每个用户的第一个订阅的行
加入users:

select u.*, t.status, t.start_date
from users u 
left join (
select s.*
from subscriptions s
where s.start_date = (select min(start_date) from subscriptions where email = s.email)  
) t on t.email = u.email  

请参阅演示
结果：

| id  | email          | first_name | last_name | status   | start_date          |
| --- | -------------- | ---------- | --------- | -------- | ------------------- |
| 1   | [e-mail]   | John       | Smith     | active   | 2018-02-12 23:34:02 |
| 2   | [e-mail]    | Jim        | Smith     | canceled | 2016-03-02 23:34:02 |
| 3   | [e-mail]  | Jerry      | Smith     | active   | 2017-12-12 23:34:02 |
| 4   | [e-mail] | Jackie     | Smith     | active   | 2018-05-22 23:34:02 |