mysql - 从 MySQL 中的左连接中排除某些组合

Question

我正在分析游戏数据。我的数据包括游戏ID(GID)，玩家ID(PID)和时间片(T)，......表A和B是我的查询创建的两个表，如下所示:

Table A
GID, PID, T, command_freq, command_id
1,   1,   0, 17, 10
1,   1,   0, 4, 5
1,   1,   1, 26, 10
1,   1,   1, 6, 5
1,   1,   2, 5, 5
1,   1,   2, 3, 10
1,   1,   5, 7, 10

Table B
GID, PID, T, order_freq, order_id
 1,  1,   0, 7, 40
 1,  1,   2, 3, 40
 1,  1,   2, 11, 42
 1,  1,   5, 1, 40

我需要找到每个时间片中执行了哪些命令和命令(以及它们的计数)。结果应如下所示:

GID, PID, T, command_count, command_id, order_count, order_id
1,   1,   0,  17,           10,            Null, Null
1,   1,   0,  4,            5 ,            Null, Null
1,   1,   0,  Null,         Null,          7, 40
1,   1,   1,  26,           10,            Null, Null
1,   1,   1,  6,            5,             Null, Null
1,   1,   2,  5,            5,             Null, Null
1,   1,   2,  3,            10,            Null, Null
1,   1,   2,  Null,         Null,          3, 40
1,   1,   2,  Null,         Null,          11, 42
1,   1,   5,  7,            10,            Null, Null
1,   1,   5,  Null,         Null,          1, 40

一般来说，在 T=t 中我们可以有 0 到 n 个命令或命令。我需要 T=t 的所有命令和命令的并集。

Answer 1

架构

drop table if exists ta;
create table ta (GID int,PID int,T int,command_freq int, command_id int);
insert into ta values
(1,   1,   0, 17, 10),
(1,   1,   0, 4, 5),
(1,   1,   1, 26, 10),
(1,   1,   1, 6, 5),
(1,   1,   2, 5, 5),
(1,   1,   2, 3, 10),
(1,   1,   5, 7, 10);

drop table if exists tb;
create table tb (gid int,pid int,t int,order_freq int,order_id int);
insert into tb values
( 1,  1,   0, 7, 40),
( 1,  1,   2, 3, 40),
( 1,  1,   2, 11, 42),
( 1,  1,   5, 1, 40);

请注意，通过使用联合，联合中的第一个查询将驱动最终输出的列名目标:

GID, PID, T, command_count, command_id, order_count, order_id

因此，如果愿意的话，联合中的第二个及之后的查询可以很马虎，至少就其所谓的列名称而言是这样。

下面的第一个查询虽然生成了相同的输出(至少通过命令行实用程序输出在此处向您显示)，但提供的结果与第二个查询略有不同，如果我通过 MySQL Workbench 运行它。

select GID,PID,T,command_count,command_id,order_count,order_id 
from 
(   select GID, PID, T, command_freq as command_count, command_id, null as order_count, null as order_id, 1 as theOrder from ta 
    union  
    select GID, PID, T, null as command_count, null, order_freq as order_count, order_id, 2 as theOrder from tb 
) xDerived 
order by GID,PID,T,theOrder; 
+------+------+------+---------------+------------+-------------+----------+
| GID  | PID  | T    | command_count | command_id | order_count | order_id |
+------+------+------+---------------+------------+-------------+----------+
|    1 |    1 |    0 |            17 |         10 |        NULL |     NULL |
|    1 |    1 |    0 |             4 |          5 |        NULL |     NULL |
|    1 |    1 |    0 |          NULL |       NULL |           7 |       40 |
|    1 |    1 |    1 |            26 |         10 |        NULL |     NULL |
|    1 |    1 |    1 |             6 |          5 |        NULL |     NULL |
|    1 |    1 |    2 |             5 |          5 |        NULL |     NULL |
|    1 |    1 |    2 |             3 |         10 |        NULL |     NULL |
|    1 |    1 |    2 |          NULL |       NULL |          11 |       42 |
|    1 |    1 |    2 |          NULL |       NULL |           3 |       40 |
|    1 |    1 |    5 |             7 |         10 |        NULL |     NULL |
|    1 |    1 |    5 |          NULL |       NULL |           1 |       40 |
+------+------+------+---------------+------------+-------------+----------+
11 rows in set (0.00 sec)

下面的第二个版本需要花费一些额外的精力来使用 rownum 来达到结果排序(如果这在 GID,PID,T block 内部甚至很重要的话)。第二个结果集至少通过 mysql 命令行工具和 MySQL Workbench 显示了相同的顺序:

select GID,PID,T,command_count,command_id,order_count,order_id 
from 
(   select GID, PID, T, command_freq as command_count, command_id, null as order_count, null as order_id, 1 as theOrder,@rn1:=@rn1+1 as rownum 
    from ta 
    cross join (select @rn1:=0) xParams1 
    union  
    select GID, PID, T, null as command_count, null, order_freq as order_count, order_id, 2 as theOrder,@rn2:=@rn2+1 as rownum   from tb 
    cross join (select @rn2:=0) xParams2 
) xDerived 
order by GID,PID,T,theOrder,rownum; 
+------+------+------+---------------+------------+-------------+----------+
| GID  | PID  | T    | command_count | command_id | order_count | order_id |
+------+------+------+---------------+------------+-------------+----------+
|    1 |    1 |    0 |            17 |         10 |        NULL |     NULL |
|    1 |    1 |    0 |             4 |          5 |        NULL |     NULL |
|    1 |    1 |    0 |          NULL |       NULL |           7 |       40 |
|    1 |    1 |    1 |            26 |         10 |        NULL |     NULL |
|    1 |    1 |    1 |             6 |          5 |        NULL |     NULL |
|    1 |    1 |    2 |             5 |          5 |        NULL |     NULL |
|    1 |    1 |    2 |             3 |         10 |        NULL |     NULL |
|    1 |    1 |    2 |          NULL |       NULL |           3 |       40 |
|    1 |    1 |    2 |          NULL |       NULL |          11 |       42 |
|    1 |    1 |    5 |             7 |         10 |        NULL |     NULL |
|    1 |    1 |    5 |          NULL |       NULL |           1 |       40 |
+------+------+------+---------------+------------+-------------+----------+
11 rows in set (0.00 sec)

无论哪种情况，theOrder 列都用于强制 NULL 的输出位于每个 GID,PID,T 的底部> 阻止。

我将它们作为一种方式，让您可视化变量的使用和排序，以便在进行更改时达到您想要的输出顺序。