mysql - 加入2个基于文本字段的mysql选择

Question

我在 Mysql 中有以下 2 个选择:

第一次选择:

(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 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

输出选择 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

输出选择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，对添加以下计算的 Select 的连接:活跃天数 (FirstDep-LastAction) 和 Days_last_Visit (CurrentDate - Last Action)

总结如下表:

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

非常感谢您的帮助!谢谢

Answer 1

以下查询应该会给出您想要的结果。我要补充一点，我使用 Player 字段从上面的中间查询中加入了两个表。这不是一种非常稳健的加入方式，因为名称在表中的所有玩家中可能不是唯一的。更好的加入方式是使用某种唯一标识符。

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`