代码之家 › 专栏 › 技术社区 › Ali

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

greatest-n-per-group union join select mysql

Ali · 技术社区 · 7 年前

我有两张桌子:

桌子 互联网客户端 (ID、全名、位置、电话号码)

桌子 Internet客户端详细信息 (incdid、icdi、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代表internetclientdetailsid 而cidi代表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 UNION SELECT * FROM client c RIGHT JOIN client_subscription c_S on c.id=c_s.client_id WHERE 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

我整晚都在忙这个。任何帮助都非常感谢。

4 回复 | 直到 7 年前

M Khalid Junaid 7 年前

要获取每个客户机的客户机订阅,可以使用自加入

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

Rajat Jaiswal 7 年前

嗨,下面的示例可以帮助您。

DECLARE @tblClient AS TABLE (ID INT , Name varchar(100))
DECLARE @tblClientSub As TABLE (id INT,client_id INT,date_sub DATE,sub_price INT)

INSERT INTO @tblClient (id,Name)
VALUES 
(1,'Linda'),
(2,'Mary'),
(3,'Joe')  

INSERT INTO @tblClientSub(Id,client_id ,    date_sub ,    sub_price)
VALUES
(1,1,'2018/01/01',50),
(2,2,'2018/02/01',50),
(3,2,'2018/03/01',30),
(4,2,'2018/04/01',30),
(5,3,'2018/01/01',50),
(6,3,'2018/07/01',50),
(7,1,'2018/02/01',40)

SELECT c.Id,c.Name,cs.date_sub,cs.sub_price
FROM @tblClient c
CROSS APPLY (SELECT TOP (1)date_sub,sub_price 
             FROM @tblClientSub 
             WHERE client_id = c.Id 
             ORDER BY date_sub DESC) cs

devesh 7 年前

select c.name as 'client_name',cs.client_id,max(cs.sub_date) as 'date_sub',cs.sub_price from client c ,
client_subscription cs where cs.client_id=c.id group by cs.client_id,cs.sub_price;

Ryuk Lee 7 年前

试试这个

SELECT c.Name, c.id , MAX(date_sub), sub_price  FROM client c LEFT JOIN client_subscription c_s on c.id=c_s.client_id
GROUP BY c.id
ORDER BY c.id ASC