join 2 mysql select based on text field

Duy Trần 发表于 Dev

R_life_R

I have the 2 following select in Mysql:

1st select:

(SELECT DISTINCT `Online_playerdatabase_v2`.`Player`, 
Online_playerdatabase_v2.First_Deposit_Date As FirstDep, 
TRUNCATE(Online_playerdatabase_v2.Balance,2) as Balance

FROM Online_playerdatabase_v2 
WHERE `Online_playerdatabase_v2`.`Player`<>'Player'
ORDER BY `Online_playerdatabase_v2`.`Balance` DESC;

2d select:

SELECT DISTINCT(Online_customer_activity_v2.Customers) as Player,
max(Online_customer_activity_v2.Date) as LastAction
FROM Online_customer_activity_v2
WHERE `Online_customer_activity_v2`.`Total_Bets`>0
Group by Online_customer_activity_v2.Customers

Output Select 1

Player      FirstDep    Balance
Ray         2014-10-19  9100.00
Ramzi       2014-11-02  9.61
tareq       2014-11-06  805.00
STAN        2014-10-17  7.50
Bill        2014-03-25  68.40
karam       2014-11-16  676.50
Abdul       2014-11-13  650.00
Renaud      2014-03-12  507.00
John        2014-11-22  500.00

Output select 2

Player  LastAction
John    2015-11-13
Bill    2014-12-14
Renaud  2015-03-14
Abdul   2015-11-16
Ray     2015-11-22
STAN    2015-10-29
Ramzi   2015-11-10
Tarek   2015-05-10
karam   2014-12-10
Abdul   2015-02-10

Desired Output, a join on both Select that adds following calculations: active days (FirstDep-LastAction) and Days_last_Visit (CurrentDate - Last Action)

Summarized in following table:

Player  FirstDep    Balance LastAction  Active_days Days_last_Visit
Ray     2014-10-19  9100.00 2015-11-22  399          1
Ramzi   2014-11-02  9.61    2015-11-10  373          13
tareq   2014-11-06  805.00  2015-05-10  185          197
STAN    2014-10-17  7.50    2015-10-29  377          25
Bill    2014-03-25  68.40   2014-12-14  264          344
karam   2014-11-16  676.50  2014-12-10  24           348
Abdul   2014-11-13  650.00  2015-02-10  89           286
Renaud  2014-03-12  507.00  2015-03-14  367          254
John    2014-11-22  500.00  2015-11-13  356          10

Your help is greatly appreciated! Thanks

Tim Biegeleisen

The following query should give the result you want. I will add that I joined the two tables from your intermediate queries above using the Player field. This is not a very robust way to join, because the name may not be unique among all players in the table. A better way to join would be to use a unique identifier of some sort.

SELECT t1.Player, t1.FirstDep, t1.Balance, t2.LastAction,
    DATEDIFF(t2.LastAction, t1.FirstDep) AS Active_days,
    DATEDIFF(NOW(), t2.LastAction) AS Days_last_Visit
FROM
(
    SELECT DISTINCT `Online_playerdatabase_v2`.`Player`, 
        Online_playerdatabase_v2.First_Deposit_Date AS FirstDep, 
        TRUNCATE(Online_playerdatabase_v2.Balance,2) AS Balance
    FROM Online_playerdatabase_v2 
    WHERE `Online_playerdatabase_v2`.`Player` <> 'Player'
) t1
INNER JOIN
(
    SELECT DISTINCT(Online_customer_activity_v2.Customers) AS Player,
        MAX(Online_customer_activity_v2.Date) AS LastAction
    FROM Online_customer_activity_v2
    WHERE `Online_customer_activity_v2`.`Total_Bets` > 0
    GROUP BY Online_customer_activity_v2.Customers
) t2
ON t1.`Player` = t2.`Player`

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。