我的数据库中有两个表格,它们都有大约1亿条记录。第一个表格uph包含订单细节,而另一个表格urs包含顾客细节。它们的结构如下:
mysql> desc uph;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uid | int(11) | NO | | NULL | |
| order_from | varchar(255) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> desc usr;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| uid | int(11) | NO | PRI | NULL | auto_increment |
| profile | char(10) | NO | | NULL | |
+---------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
两个表格都有这样的数据:
mysql> select * from usr;
+-----+----------+
| uid | profile |
+-----+----------+
| 1 | in-store |
| 2 | ecom |
| 3 | ecom |
| 4 | in-store |
| 5 | ecom |
+-----+----------+
4 rows in set (0.00 sec)
mysql> select * from uph;
+----+-----+------------+
| id | uid | order_from |
+----+-----+------------+
| 1 | 1 | in-store |
| 2 | 2 | ecom |
| 3 | 1 | ecom |
| 4 | 4 | in-store |
+----+-----+------------+
4 rows in set (0.00 sec)
现在,我想找到那些拥有“ecom”资料的用户,如果他们已经购买过任何物品,则订单来源应该仅为“ecom”。如果没有购买任何物品但仍然拥有资料,则仍将被视为“ecom”用户。
如果任何用户从电子商务和实体店都购买了物品,则这些用户将被排除在结果之外。这意味着用户不应与实体店有任何关系。
因此,在查询的输出中,我们将得到以下结果:
+----+
| uid |
+-----+
| 2 |
| 3 |
| 5 |
+-----+
由于两个表都包含大量数据,因此我只能使用用户子查询。请建议如何在不使用子查询的情况下完成它。