mysql - 获取 id 以及电话号码和 gmtstarttime

Question

我有如下输入表

Phonenumber GMTSTSRTTIME    GMTENDTIME
989876758   20170101 11:15:00   20170101 11:20:00
989876758   20170101 13:15:00   20170101 13:33:00

和查找表作为

Intra_ID    Seg_id  Phonenumber GMTSTARTTIME    seq
1213    12131   989876758   20170101 11:09:00   1
1213    12132   989876758   20170101 11:09:00   2
1213    12133   989876758   20170101 11:09:00   3
1214    12141   989876758   20170101 13:09:00   1
1214    12142   989876758   20170101 13:09:00   2
1214    12143   989876758   20170101 13:09:00   3

我期待如下输出，max(seg_id) 后跟 intra_ID、phonenumber、GMTSTSRTTIME、GMTENDTIME

Intra_ID    Seg_id  Phonenumber GMTSTSRTTIME    GMTENDTIME
1213    12133   989876758   20170101 11:15:00   20170101 11:20:00
1214    12143   989876758   20170101 13:15:00   20170101 13:33:00

在这种情况下，电话号码会在同一天以不同的时间到达，因此它成为一个唯一值。当我尝试连接查找表时，它会给我如下的交叉连接值。

Intra_ID    Seg_id  Phonenumber GMTSTSRTTIME    GMTENDTIME
1213    12131   989876758   20170101 11:15:00   20170101 11:20:00
1213    12131   989876758   20170101 13:15:00   20170101 13:33:00
1214    12141   989876758   20170101 11:15:00   20170101 11:20:00
1214    12141   989876758   20170101 13:15:00   20170101 13:33:00

这是错误的。

请帮我解决这个问题

提前致谢

Answer 1

查询实际上只是 input 和 lookup 之间的基本内部连接，在电话号码 和 行号上，按以下命令排序时间在每个电话号码组中。问题是您的表中没有行号列，MySQL 不支持任何 ROW_NUMBER() 分析函数。因此，一种选择是使用 session 变量模拟行号。

SET @row_number_1 = 0;
SET @row_number_2 = 0;
SET @Phonenumber_1 = NULL;
SET @Phonenumber_2 = NULL;

SELECT
    t2.Intra_ID,
    t2.Seg_id,
    t1.Phonenumber,
    t1.GMTSTSRTTIME,
    t1.GMTENDTIME, t1.rn, t2.rn
FROM
(
    SELECT 
        @row_number_1:=CASE WHEN @Phonenumber_1 = Phonenumber
                          THEN @row_number_1 + 1
                          ELSE 1
        END AS rn,
        @Phonenumber_1:=Phonenumber as Phonenumber,
        GMTSTSRTTIME,
        GMTENDTIME
    FROM input
    ORDER BY Phonenumber, GMTSTSRTTIME
) t1
INNER JOIN
(
    SELECT 
        @row_number_2:=CASE WHEN @Phonenumber_2 = t.Phonenumber
                          THEN @row_number_2 + 1
                          ELSE 1
        END AS rn,
        @Phonenumber_2:=Phonenumber as Phonenumber,
        t.Intra_ID,
        t.Seg_id
    FROM
    (
        SELECT t1.*
        FROM lookup t1
        INNER JOIN
        (
            SELECT Phonenumber, Intra_ID, MAX(GMTSTARTTIME) AS max_gmt
            FROM lookup
            GROUP BY Phonenumber, Intra_ID
        ) t2
            ON t1.Phonenumber  = t2.Phonenumber AND
               t1.GMTSTARTTIME = t2.max_gmt
    ) t
    ORDER BY t.Phonenumber, t.GMTSTARTTIME
) t2
    ON t1.Phonenumber = t2.Phonenumber AND
       t1.rn = t2.rn;

请注意，此答案是一个很好的示例，说明如何在单个查询中跟踪多个模拟行号。在这种情况下，我们可以为每个行号使用单独的 session 变量。

输出:

此处演示

Rextester