MySQL查询根据特定条件从多个表中选择

Question

我有如下两个表:

表internetclient(id,full_name,location,phone_number)

表internetclientdetails(incdid,icid,date_sub, date_exp,isPaid,profile_sub)

两表数据如下:客户端

--------------------------------------------------------
id         full_name       location    phone_number
-------------------------------------------------------
4         Joe Amine         beirut       03776132
5         Mariam zoue       beirut       03556133

客户端订阅

--------------------------------------------------------------------------
incdid   icid      date_sub      date_exp      isPaid      sub_price
----------------------------------------------------------------------------
  6        4      2018-01-01     2018-01-30      0           2000
  7        5      2017-01-01     2017-01-30      0           1000
  8        4      2018-03-01     2018-03-30      1           50000
  9        5      2018-05-01     2019-05-30      1           90000

注意:incdid 代表 internetClientDetailsIdicid代表internetClientId

问题我想根据最新的客户订阅日期进行查询，返回客户名称以及所有详细信息，结果应如下所示:

------------------------------------------------------------
full_name     client_id     date_sub     sub_price
------------------------------------------------------------
 Joe Amine       4           2018-03-01      50000
 Mary            5           2018-05-01      90000

我在做什么

SELECT * FROM client c LEFT JOIN client_subscription c_s on c.id=c_s.client_id
联盟
SELECT * FROM client c RIGHT JOIN client_subscription c_S on c.id=c_s.client_id
在哪里
c.sub_date=(SELECT MAX(sub_date) from client_subscription c_s INNER JOIN client c on c.id=c_s.client_id GROUP BY c_s.client_id

我整晚都在研究它。非常感谢任何帮助。

Answer 1

要为每个客户端获取 client_subscription，您可以使用自连接

select c.name, a.client_id, a.date_sub, a.sub_price
from client_subscription a
join (
    select client_id, max(date_sub) date_sub
    from client_subscription
    group by client_id
) b on a.client_id = b.client_id and a.date_sub = b.date_sub
join client c on a.client_id = c.id
order by a.date_sub

Demo

或者使用左连接

select c.name, a.client_id, a.date_sub, a.sub_price
from client_subscription a
left join client_subscription b on a.client_id = b.client_id and a.date_sub < b.date_sub
join client c on a.client_id = c.id
where b.client_id is null
order by a.date_sub

Demo

---

使用更新的数据集更新的查询是

select c.full_name, a.icid, a.date_sub, a.sub_price
from internetclientdetails a
join (
    select icid, max(date_sub) date_sub
    from internetclientdetails
    group by icid
) b on a.icid = b.icid and a.date_sub = b.date_sub
join internetclient c on a.icid = c.id
order by a.date_sub;

select c.full_name, a.icid, a.date_sub, a.sub_price
from internetclientdetails a
left join internetclientdetails b on a.icid = b.icid and a.date_sub < b.date_sub
join internetclient c on a.icid = c.id
where b.icid is null
order by a.date_sub

Updated Demo