gpt4 book ai didi

mysql INNER_JOIN 子查询

转载 作者:行者123 更新时间:2023-11-29 00:12:38 24 4
gpt4 key购买 nike

我继承了一个MySQL数据库,其中有一个表如下:

mysql> describe stock_groups;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| group | varchar(5) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| parent | varchar(5) | YES | | NULL | |
| order | int(11) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

当我运行 mysql> select * from stock_groups wheregroup='D2';

我得到:

mysql> select * from stock_groups where `group`='D2';
+----+-------+------+--------+-------+
| id | group | name | parent | order |
+----+-------+------+--------+-------+
| 79 | D2 | MENS | D | 51 |
+----+-------+------+--------+-------+
1 row in set (0.00 sec)

我还有一张 table :

mysql> describe stock_groups_styles_map;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| group | varchar(5) | NO | | NULL | |
| style | varchar(25) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

当我运行时:

mysql> select `group` from stock_groups_styles_map where style='N26';
+-------+
| group |
+-------+
| B1 |
| B11 |
| D2 |
| V2 |
+-------+
4 rows in set (0.00 sec)

如何获取 stock_groups.name

最佳答案

加入表格,并仅选择您需要的数据。如果您需要唯一的行,请使用 distinct 关键字:

select  -- If you need unique names, use "select distinct" instead of "select"
sg.name
from
stock_groups_styles_map as sgs
inner join stock_groups as sg on sgs.group = sg.group
where
sgs.style = 'N26'

您也可以使用子查询来解决这个问题,但在这种情况下那将是相当低效的。

重要的事情

您应该为表添加适当的索引。它将提高数据库的性能。

关于mysql INNER_JOIN 子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24344315/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com