mysql数据库timeout MYSQL数据库原理与应用答案

转载

definitely 2024-07-24 15:01:21

文章标签 mysql数据库timeout 数据库 mysql sql 数据 文章分类 MySQL 数据库

数据库系统原理与应用教程（066）—— MySQL 练习题：操作题 71-81（十）：连接查询

71、连接查询（1）

数据表：Customers，包含顾客名称：cust_name、顾客 id：cust_id，表中数据如下：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

数据表：Orders（订单信息表），包含订单号：order_num，顾客 id：cust_id，表中数据如下：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

【问题】编写 SQL 语句，查询 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。查询结果如下：

cust_name	order_num
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');
*/

解答：

/*
select c.cust_name, o.order_num
from Customers c join Orders o on c.cust_id = o.cust_id
order by c.cust_name, o.order_num;
*/
mysql> select c.cust_name, o.order_num
    -> from Customers c join Orders o on c.cust_id = o.cust_id
    -> order by c.cust_name, o.order_num;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| an        | a5        |
| andy      | a1        |
| ben       | a2        |
| hex       | a7        |
| tom       | a4        |
| tony      | a3        |
+-----------+-----------+
6 rows in set (0.00 sec)

72、连接查询（2）

数据表：Customers，包含顾客名称：cust_name，顾客id：cust_id，表中数据如下：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

数据表：Orders（订单信息表），包含订单号：order_num，顾客id：cust_id，表中数据如下：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

数据表：OrderItems，包含商品订单号：order_num，订购数量：quantity，单价：item_price，表中数据如下：

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

【问题】查询顾客名称、订单号以及每个订单的总价（OrderTotal），并按顾客名称再按订单号对结果进行升序排序。查询结果如下：

cust_name	order_num	OrderTotal
an	a5	375
andy	a1	10000
ben	a2	2000
hex	a7	49
tom	a4	1250
tony	a3	150

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);
*/

解答：

/*
select cust_name, o.order_num, 
       sum(oi.quantity * oi.item_price) OrderTotal
from Customers c join Orders o on c.cust_id = o.cust_id
     join OrderItems oi on o.order_num = oi.order_num
group by cust_name, o.order_num
order by cust_name, o.order_num;
*/
mysql> select cust_name, o.order_num, 
    ->        sum(oi.quantity * oi.item_price) OrderTotal
    -> from Customers c join Orders o on c.cust_id = o.cust_id
    ->      join OrderItems oi on o.order_num = oi.order_num
    -> group by cust_name, o.order_num
    -> order by cust_name, o.order_num;
+-----------+-----------+------------+
| cust_name | order_num | OrderTotal |
+-----------+-----------+------------+
| an        | a5        |        375 |
| andy      | a1        |      10000 |
| ben       | a2        |       2000 |
| hex       | a7        |         49 |
| tom       | a4        |       1250 |
| tony      | a3        |        150 |
+-----------+-----------+------------+
6 rows in set (0.00 sec)

73、连接查询（3）

数据表：OrderItems，表中数据如下：

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

数据表：Orders，表中数据如下：

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

【问题】编写 SQL 语句，查询哪些订单购买了 prod_id 为 “BR01” 的产品，显示每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。查询结果如下：

cust_id	order_date
cust10	2022-01-01 00:00:00
cust1	2022-01-01 00:01:00

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
);
INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');
*/

解答：

/*
-- 使用子查询
select cust_id, order_date
from Orders where order_num in 
    (select order_num from OrderItems where prod_id = 'BR01')
order by order_date;

-- 使用连接查询
select o.cust_id, o.order_date
from Orders o join OrderItems oi on o.order_num = oi.order_num
where oi.prod_id = 'BR01'
order by o.order_date;
*/
-- 使用子查询
mysql> select cust_id, order_date
    -> from Orders where order_num in 
    ->     (select order_num from OrderItems where prod_id = 'BR01')
    -> order by order_date;
+---------+---------------------+
| cust_id | order_date          |
+---------+---------------------+
| cust10  | 2022-01-01 00:00:00 |
| cust1   | 2022-01-01 00:01:00 |
+---------+---------------------+
2 rows in set (0.00 sec)

-- 使用连接查询
mysql> select o.cust_id, o.order_date
    -> from Orders o join OrderItems oi on o.order_num = oi.order_num
    -> where oi.prod_id = 'BR01'
    -> order by o.order_date;
+---------+---------------------+
| cust_id | order_date          |
+---------+---------------------+
| cust10  | 2022-01-01 00:00:00 |
| cust1   | 2022-01-01 00:01:00 |
+---------+---------------------+
2 rows in set (0.00 sec)

74、连接查询（4）

数据表：OrderItems，表中数据如下：

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

数据表：Orders，表中数据如下：

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

数据表：Customers，表中数据如下：

cust_id	cust_email
cust10	cust10@cust.com
cust1	cust1@cust.com
cust2	cust2@cust.com

【问题】查询购买 prod_id 为 “BR01” 的产品的所有顾客的电子邮件。查询结果如下：

cust_email
cust10@cust.com
cust1@cust.com

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
);
INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
);
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');
*/

解答：

/*
-- 使用子查询
select cust_email from Customers where cust_id in 
    (select cust_id from Orders where order_num in 
         (select order_num from OrderItems where prod_id = 'BR01')
    );
-- 使用连接查询
select c.cust_email 
from Customers c join Orders o on c.cust_id = o.cust_id
    join OrderItems oi on o.order_num = oi.order_num
where prod_id = 'BR01';
*/
-- 使用子查询
mysql> select cust_email from Customers where cust_id in 
    ->     (select cust_id from Orders where order_num in 
    ->          (select order_num from OrderItems where prod_id = 'BR01')
    ->     );
+-----------------+
| cust_email      |
+-----------------+
| cust10@cust.com |
| cust1@cust.com  |
+-----------------+
2 rows in set (0.00 sec)

-- 使用连接查询
mysql> select c.cust_email 
    -> from Customers c join Orders o on c.cust_id = o.cust_id
    ->     join OrderItems oi on o.order_num = oi.order_num
    -> where prod_id = 'BR01';
+-----------------+
| cust_email      |
+-----------------+
| cust10@cust.com |
| cust1@cust.com  |
+-----------------+
2 rows in set (0.00 sec)

75、连接查询（5）

数据表：OrderItems，表中数据如下：

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

数据表：Orders，表中数据如下：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

数据表：Customers，表中数据如下：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

【问题】编写 SQL 语句，查询订单总价不小于 1000 的客户名称和总额（item_price * quantity），按总额对结果进行排序。查询结果如下：

cust_name	total_price
andy	1050
ben	1319
tom	2242

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);


DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
*/

解答：

/*
select c.cust_name,
       sum(oi.item_price * oi.quantity) total_price
from OrderItems oi join Orders o on oi.order_num = o.order_num
    join Customers c on o.cust_id = c.cust_id
group by c.cust_name
having total_price >= 1000
order by total_price;
*/
mysql> select c.cust_name,
    ->        sum(oi.item_price * oi.quantity) total_price
    -> from OrderItems oi join Orders o on oi.order_num = o.order_num
    ->     join Customers c on o.cust_id = c.cust_id
    -> group by c.cust_name
    -> having total_price >= 1000
    -> order by total_price;
+-----------+-------------+
| cust_name | total_price |
+-----------+-------------+
| andy      |        1050 |
| ben       |        1319 |
| tom       |        2242 |
+-----------+-------------+
3 rows in set (0.00 sec)

76、连接查询（6）

数据表：Customers，表中数据如下：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

数据表：Orders，表中数据如下：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

【问题】编写 SQL语句，查询每个顾客的名称和所有的订单号，根据顾客姓名 cust_name 升序排序。查询结果如下：

cust_name	order_num
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
*/

解答：

/*
select c.cust_name, o.order_num
from Customers c join Orders o on c.cust_id = o.cust_id
order by c.cust_name;
*/
mysql> select c.cust_name, o.order_num
    -> from Customers c join Orders o on c.cust_id = o.cust_id
    -> order by c.cust_name;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| an        | a5        |
| andy      | a1        |
| ben       | a2        |
| hex       | a7        |
| tom       | a4        |
| tony      | a3        |
+-----------+-----------+
6 rows in set (0.00 sec)

77、连接查询（7）

数据表：Orders，表中数据如下：

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

数据表：Customers，表中数据如下：

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex
cust40	ace

【问题】检索每个顾客的名称和所有的订单号，列出所有的顾客，即使他们没有下过订单。查询结果如下：

cust_name	order_num
ace	NULL
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),('cust40','ace');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
*/

解答：

/*
select c.cust_name, o.order_num
from Customers c left join Orders o on c.cust_id = o.cust_id
order by c.cust_name;
*/
mysql> select c.cust_name, o.order_num
    -> from Customers c left join Orders o on c.cust_id = o.cust_id
    -> order by c.cust_name;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| ace       | NULL      |
| an        | a5        |
| andy      | a1        |
| ben       | a2        |
| hex       | a7        |
| tom       | a4        |
| tony      | a3        |
+-----------+-----------+
7 rows in set (0.00 sec)

78、连接查询（8）

数据表：Products，表中数据为：

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

数据表：OrderItems，表中数据为：

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

【问题】编写 SQL 语句，查询产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。查询结果如下：

prod_name	order_num
coffee	a1121
cola	a5
cola	a19
cola	a10
egg	a105
sockets	a200
sockets	a1100
soda	NULL

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola'),
('a0023','soda');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	order_num VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');
*/

解答：

/*
select p.prod_name, oi.order_num
from Products p left join OrderItems oi on p.prod_id =  oi.prod_id
order by p.prod_name;
*/
mysql> select p.prod_name, oi.order_num
    -> from Products p left join OrderItems oi on p.prod_id =  oi.prod_id
    -> order by p.prod_name;
+-----------+-----------+
| prod_name | order_num |
+-----------+-----------+
| coffee    | a1121     |
| cola      | a10       |
| cola      | a19       |
| cola      | a5        |
| egg       | a105      |
| sockets   | a1100     |
| sockets   | a200      |
| soda      | NULL      |
+-----------+-----------+
8 rows in set (0.00 sec)

79、连接查询（9）

数据表：Products，表中数据如下：

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

数据表：OrderItems，表中数据如下：

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

【问题】查询产品名称（prod_name）和每一项产品的总订单数，并按产品名称升序排序。查询结果如下：

prod_name	orders
coffee	1
cola	3
egg	1
sockets	2
soda	0

解答：

/*
select p.prod_name, count(order_num) orders
from Products p left join OrderItems oi on p.prod_id =  oi.prod_id
group by p.prod_name
order by p.prod_name;
*/
mysql> select p.prod_name, count(order_num) orders
    -> from Products p left join OrderItems oi on p.prod_id =  oi.prod_id
    -> group by p.prod_name
    -> order by p.prod_name;
+-----------+--------+
| prod_name | orders |
+-----------+--------+
| coffee    |      1 |
| cola      |      3 |
| egg       |      1 |
| sockets   |      2 |
| soda      |      0 |
+-----------+--------+
5 rows in set (0.00 sec)

80、连接查询（10）

数据表：Vendors，表中数据如下：

vend_id
a0002
a0013
a0003
a0010

数据表：Products，表中数据如下：

vend_id	prod_id
a0001	egg
a0002	prod_id_iphone
a00113	prod_id_tea
a0003	prod_id_vivo phone
a0010	prod_id_huawei phone

【问题】列出供应商（Vendors 表中的 vend_id）及可供产品的数量（包括没有产品的供应商），根据 vend_id 升序排序。查询结果如下：

vend_id	prod_id
a0002	1
a0003	1
a0010	1
a0013	0

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('a0002'),
('a0013'),
('a0003'),
('a0010');

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','prod_id_iphone'),
('a00113','prod_id_tea'),
('a0003','prod_id_vivo phone'),
('a0010','prod_id_huawei phone');
*/

解答：

/*
select v.vend_id, count(prod_id) prod_id
from Vendors v left join Products p on v.vend_id = p.vend_id
group by v.vend_id
order by v.vend_id;
*/
mysql> select v.vend_id, count(prod_id) prod_id
    -> from Vendors v left join Products p on v.vend_id = p.vend_id
    -> group by v.vend_id
    -> order by v.vend_id;
+---------+---------+
| vend_id | prod_id |
+---------+---------+
| a0002   |       1 |
| a0003   |       1 |
| a0010   |       1 |
| a0013   |       0 |
+---------+---------+
4 rows in set (0.00 sec)

81、union 联合查询

数据表：OrderItems，表中数据如下：

prod_id	quantity
a0001	105
a0002	100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5
BNBG	10002

【问题】查询数量（quantity）为 100 的产品信息和产品 id（prod_id）以【BNBG】开头的产品信息，按产品 id 对结果进行升序排序。查询结果如下：

prod_id	quantity
a0002	100
BNBG	10002

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);
*/

解答：

/*
-- 使用 union
select * from OrderItems where quantity = 100
union
select * from OrderItems where prod_id like 'BNBG%'
order by prod_id; 

-- 使用 or
select * from OrderItems 
where quantity = 100 or prod_id like 'BNBG%'
order by prod_id; 
*/
-- 使用 union
mysql> select * from OrderItems where quantity = 100
    -> union
    -> select * from OrderItems where prod_id like 'BNBG%'
    -> order by prod_id; 
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002   | 100      |
| BNBG    | 10002    |
+---------+----------+
2 rows in set (0.03 sec)

-- 使用 or 构造查询条件
mysql> select * from OrderItems 
    -> where quantity = 100 or prod_id like 'BNBG%'
    -> order by prod_id; 
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002   | 100      |
| BNBG    | 10002    |
+---------+----------+
2 rows in set (0.00 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。