在 MySQL 中从左连接中排除一些组合 [英] Excluding some combinations from left join in MySQL
问题描述
我正在分析游戏数据.我的数据包括游戏 ID (GID)、玩家 ID (PID) 和时间片 (T)，.... 表 A 和 B 是由我的查询创建的两个表，如下所示:
I'm analyzing game data. My data includes game id (GID), player id (PID), and Time slice (T),.... Table A and B are two tables that created by my queries as follow:
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
我需要找出在每个时间片中执行了哪些命令和命令(以及它们的计数).结果应该如下:
I need to find in which commands and orders are done in each time slice (also their counts). The result should be as follows:
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 的所有命令和命令的联合.
In general in T=t we could have 0 to n commands or orders. I need the union of all commands and orders for T=t.
推荐答案
Schema
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);
请注意，使用联合时，联合中的第一个查询决定了最终输出的目标列名:
Note that with the use of unions, the first query in a union drives what the column names are targeted as for the final output:
GID, PID, T, command_count, command_id, order_count, order_id
因此，如果需要，联合中的第 2 次及之后的查询可能会很草率，至少就其所谓的列名而言.
So the 2nd and thereafter queries in a union can be sloppy about it if they want, at least insofar as their alleged column names.
下面的第一个查询虽然生成了相同的输出，但它至少通过命令行实用程序输出此处向您显示，但如果出现以下情况，则提供的结果与第二个查询略有不同我通过 MySQL Workbench 运行它.
The first query below, though it generates the same output at least shown to you here via the command line utility output, delivers slightly different results than the second query if I run it thru 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)
如果这在 GID,PID,T 块内甚至很重要，下面的第二个版本需要额外的小努力来使用 rownum 来达到您的结果排序.第二个结果集至少通过 mysql 命令行工具和 MySQL Workbench 显示了相同的顺序:
The second version below takes the minor additional effort to use a rownum to hit your result ordering if that was even important inside of the GID,PID,T blocks. The second result set shows the same ordering for me at least via the mysql command line tool and 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 的底部> 阻止.
In either case, the theOrder column was used to force the output of NULL to be at the bottom of each GID,PID,T block.
我将它们呈现为一种让您在进行更改时可视化变量的使用和排序以达到所需输出顺序的方式.
I present them as a way for you to visualize the use of variables and ordering to hit your desired output ordering as you make changes.
这篇关于在 MySQL 中从左连接中排除一些组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
