mysql - 根据唯一键之间的比较选择另一列的最新值

Question

我想要实现的是根据相同的 msisdn 获取另一列的最新日期(如果存在多个链接到的 msisdn其他 imsi)。 (您可以假设 imsi 更像是一个独特的列)

(出于演示目的简化了表格)

我有如下两个表:

运算符表

+--------+--------+---------------------+
|  imsi  | msisdn |    last_accessed    |
+--------+--------+---------------------+
| 74583  | 004442 | 2018-04-05 16:20:32 |
+--------+--------+---------------------+
| 94210  | 023945 | 2017-02-13 11:27:14 |
+--------+--------+---------------------+
| 59123  | 004442 | 2018-07-15 05:24:55 |
+--------+--------+---------------------+
| 61234  | 089923 | 2018-07-21 16:13:29 |
+--------+--------+---------------------+

客户表

+--------+--------------+---------------------+
|  imsi  |  company_id  |    business_plan    |
+--------+--------------+---------------------+
| 74583  |      FEX     |       yearly        |
+--------+--------------+---------------------+
| 94210  |      AOH     |       trial         |
+--------+--------------+---------------------+
| 59123  |      BIOI    |       monthly       |
+--------+--------------+---------------------+
| 61234  |      OOX     |       simple        |
+--------+--------------+---------------------+

下面的结果是我的目标。如果我搜索 74583，它应该返回 2018-07-15 05:24:55。

+--------+--------------+---------------------+----------------------+
|  imsi  |  company_id  |    business_plan    |  last_accessed_date  |
+--------+--------------+---------------------+----------------------+
| 74583  |      FEX     |       yearly        |  2018-07-15 05:24:55 |
+--------+--------------+---------------------+----------------------+

下面的查询几乎返回了我尝试实现的结果，但没有根据上表返回最新日期。

SELECT 
    cust.imsi,
    cust.company_id,
    cust.business_plan,
    CASE
      WHEN
      (
        SELECT MAX(subop.last_accessed)
          FROM operator subop
            WHERE subop.msisdn = op.msisdn
              GROUP BY subop.msisdn
                HAVING COUNT(*) > 1
      )
      THEN
        op.last_accessed
      ELSE
        'Never'
    END
      AS last_accessed_date
FROM customer cust 
  INNER JOIN operator op
    ON cust.imsi = op.imsi
WHERE cust.imsi = '74583';

Answer 1

我们可以尝试在 select 子句中使用相关子查询:

SELECT
    c.imsi,
    c.company_id,
    c.business_plan,
    (SELECT MAX(t.last_accessed) FROM operator t
     WHERE t.msisdn = o.msisdn) last_accessed_date
FROM customer c
INNER JOIN operator o
    ON c.imsi = o.imsi
WHERE c.imsi = '74583';

点击下面的链接获取 SQLFiddle 演示。

Demo