56,803
社区成员




drop database if exists test1;
create database test1;
use test1;
/** 产品分类表 */
create table productType (
typeId varchar(20) not null primary key, /*分类id*/
name varchar(50), /*分类名*/
fatherTypeId varchar(20) /*上级分类*/
);
/** 产品表 */
create table product (
sku varchar(20) not null primary key, /*库存量单位,可看作商品id*/
name varchar(50), /*商品名*/
maxPrice double, /*最高价*/
minPrice double, /*最低价*/
normalCount int, /*良品库存*/
normalSellCount int, /*可销售的良品数*/
badCount int /*损坏件数*/
);
/** 产品分类关系表 */
create table register (
typeId varchar(20),
sku varchar(20),
primary key (typeId, sku)
);
insert into productType values('hf1', 'erji', null);
insert into productType values('hf2', 'taishishebei', null);
insert into productType values('hf3', 'zhoubian', null);
insert into productType values('hf4', 'cd', null);
insert into productType values('hf1-1', 'ruer', 'hf1');
insert into productType values('hf1-2', 'ergua', 'hf1');
insert into productType values('hf1-3', 'bianxie', 'hf1');
insert into productType values('hf1-4', 'toudai', 'hf1');
insert into productType values('hf1-5', 'dongquan', 'hf1');
insert into productType values('hf1-6', 'dongtie', 'hf1');
insert into productType values('hf1-7', 'jingdian', 'hf1');
insert into productType values('hf2-1', 'zhuanpan', 'hf2');
insert into productType values('hf2-2', 'jiema', 'hf2');
insert into productType values('hf2-3', 'erfang', 'hf2');
insert into productType values('hf2-4', 'yitiji', 'hf2');
insert into productType values('hf3-1', 'xiancai', 'hf3');
insert into productType values('hf3-2', 'jiemian', 'hf3');
insert into productType values('hf4-1', 'jpop', 'hf4');
insert into productType values('hf4-2', 'jazz', 'hf4');
insert into productType values('hf4-3', 'bossanova', 'hf4');
insert into productType values('hf4-4', 'ost', 'hf4');
insert into product values('0001', 'hd600', 1900, 1600, 800, 700, 50);
insert into register values('hf1-4', '0001');
insert into register values('hf1-5', '0001');
insert into product values('0002', 'hd800', 12000, 7000, 10, 7, 0);
insert into register values('hf1-4', '0002');
insert into register values('hf1-5', '0002');
insert into product values('0003', 'sr009', 31000, 27000, 21, 17, 1);
insert into register values('hf1-4', '0003');
insert into register values('hf1-7', '0003');
insert into product values('0004', 'e4c', 2500, 1700, 300, 250, 13);
insert into register values('hf1-1', '0004');
insert into register values('hf1-6', '0004');
insert into product values('0005', 'ew9', 1380, 950, 5, 1, 0);
insert into register values('hf1-2', '0005');
insert into register values('hf1-5', '0005');
insert into product values('0006', 'esw11ltd', 6700, 4360, 7, 1, 0);
insert into register values('hf1-3', '0006');
insert into register values('hf1-5', '0006');
insert into product values('0007', 't1', 8900, 6800, 20, 15, 1);
insert into register values('hf1-4', '0007');
insert into register values('hf1-5', '0007');
insert into product values('0008', 'r10', 75000, 11000, 2, 2, 0);
insert into register values('hf1-4', '0008');
insert into register values('hf1-5', '0008');
insert into product values('0009', 'qualia 010', 64000, 11000, 3, 2, 1);
insert into register values('hf1-4', '0009');
insert into register values('hf1-5', '0009');
insert into product values('0010', 'esw9', 1980, 1900, 22, 20, 1);
insert into register values('hf1-3', '0010');
insert into register values('hf1-5', '0010');
insert into product values('0011', 'sr007', 17000, 16000, 10, 9, 1);
insert into register values('hf1-4', '0011');
insert into register values('hf1-7', '0011');
insert into product values('0012', 'ad900', 1900, 1550, 15, 12, 1);
insert into register values('hf1-4', '0012');
insert into register values('hf1-5', '0012');
insert into product values('0021', 'mdac2a', 1900, 1850, 37, 30, 1);
insert into register values('hf2-2', '0021');
insert into register values('hf2-3', '0021');
insert into register values('hf2-4', '0021');
insert into product values('0022', 'lyra2', 18500, 17200, 20, 8, 0);
insert into register values('hf2-2', '0022');
insert into register values('hf2-3', '0022');
insert into register values('hf2-4', '0022');
insert into product values('0023', 'hilo', 17500, 17000, 25, 4, 0);
insert into register values('hf2-2', '0023');
insert into register values('hf2-3', '0023');
insert into register values('hf2-4', '0023');
insert into product values('0024', 'ha5000', 12000, 9800, 20, 9, 0);
insert into register values('hf2-3', '0024');
insert into product values('0025', 'dac32', 18500, 12100, 50, 47, 1);
insert into register values('hf2-2', '0025');
insert into product values('0026', 'mdac3', 1900, 1600, 100, 99, 1);
insert into register values('hf2-2', '0026');
insert into register values('hf2-3', '0026');
insert into register values('hf2-4', '0026');
insert into product values('0027', 'supermo', 11200, 9800, 75, 66, 2);
insert into register values('hf2-1', '0027');
insert into product values('0028', 'd100', 2980, 2750, 80, 80, 0);
insert into register values('hf2-1', '0028');
insert into product values('0031', 'qed audio s', 2400, 2200, 150, 130, 0);
insert into register values('hf3-1', '0031');
insert into product values('0032', 'amazon basic', 25, 21, 2750, 2749, 1);
insert into register values('hf3-1', '0032');
insert into product values('0033', 'ova', 1140, 1100, 500, 8, 0);
insert into register values('hf3-2', '0033');
insert into product values('0034', 'sp2', 4850, 4700, 2, 2, 0);
insert into register values('hf3-2', '0034');
insert into product values('0041', 'aaaa', 50, 50, 12, 9, 0);
insert into register values('hf4-1', '0041');
insert into register values('hf4-4', '0041');
insert into product values('0042', 'bbbb', 300, 295, 5, 5, 0);
insert into register values('hf4-2', '0042');
insert into product values('0043', 'cccc', 200, 195, 75, 65, 0);
insert into register values('hf4-2', '0043');
select t.typeId, t.name, t.skuCount
from (
select typ.typeId as typeId, typ.name as name, count(prod.sku) as skuCount
from product prod left join register reg on reg.sku = prod.sku
left join productType typ on reg.typeId = typ.typeId
where not isNull(typ.fatherTypeId) and prod.maxPrice < 3000 and prod.normalSellCount > 10
group by typ.typeId
) t
order by t.skuCount;
select prod.*
from product prod
where prod.normalSellCount < 10
and prod.sku in (
select distinct reg.sku
from productType typ1 left join productType typ2 on typ1.typeId = typ2.fatherTypeId
left join register reg on reg.typeId = typ2.typeId
where typ1.name = 'erji' or typ1.name = 'taishishebei'
);
select prod.*
from product prod
where prod.normalSellCount < 10
and exists (
select null
from register reg left join productType typ2 on reg.typeId = typ2.typeId
left join productType typ1 on typ2.fatherTypeId = typ1.typeId
where (typ1.name = 'erji' or typ1.name = 'taishishebei')
and reg.sku = prod.sku
);
select distinct prod.*, typ2.name as typeName
from product prod left join register reg on reg.sku = prod.sku
left join productType typ1 on reg.typeId = typ1.typeId
left join productType typ2 on typ1.fatherTypeId = typ2.typeId
where prod.sku in (
select reg.sku
from product prod left join register reg on reg.sku = prod.sku
left join productType typ on reg.typeId = typ.typeId
where not isNUll(typ.fatherTypeId) group by reg.sku having count(typ.typeId) > 1
) order by typ2.name;
select distinct prod.*, typ2.name as typeName
from product prod left join register reg on reg.sku = prod.sku
left join productType typ1 on reg.typeId = typ1.typeId
left join productType typ2 on typ1.fatherTypeId = typ2.typeId
where exists (
select null
from register reg left join productType typ2 on reg.typeId = typ2.typeId
left join productType typ1 on typ2.fatherTypeId = typ1.typeId
where reg.sku = prod.sku
group by(reg.sku) having count(reg.typeId) > 1
) order by typ2.name;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| product | 0 | PRIMARY | 1 | sku | A |
27 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
productType表:
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
| productType | 0 | PRIMARY | 1 | typeId | A |
21 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+
-------------+----------+--------+------+------------+---------+
register表:
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| register | 0 | PRIMARY | 1 | typeId | A |
48 | NULL | NULL | | BTREE | |
| register | 0 | PRIMARY | 2 | sku | A |
48 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
第一题执行计划:
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | re
f | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NU
LL | 11 | Using filesort |
| 2 | DERIVED | typ | index | PRIMARY | PRIMARY | 22 | NU
LL | 21 | Using where |
| 2 | DERIVED | reg | ref | PRIMARY | PRIMARY | 22 | te
st1.typ.typeId | 2 | Using where; Using index |
| 2 | DERIVED | prod | ref | PRIMARY | PRIMARY | 22 | te
st1.reg.sku | 1 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+---
---------------+------+--------------------------+
第二题执行计划:
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | r
ef | rows | Extra |
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
| 1 | PRIMARY | prod | ALL | NULL | NULL | NULL | N
ULL | 27 | Using where |
| 2 | DEPENDENT SUBQUERY | typ2 | ALL | PRIMARY | NULL | NULL | N
ULL | 21 | Using temporary |
| 2 | DEPENDENT SUBQUERY | typ1 | ref | PRIMARY | PRIMARY | 22 | t
est1.typ2.fatherTypeId | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | reg | ref | PRIMARY | PRIMARY | 44 | t
est1.typ2.typeId,func | 1 | Using where; Using index |
+----+--------------------+-------+------+---------------+---------+---------+--
-----------------------+------+--------------------------+
第三题执行计划:
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+
| 1 | PRIMARY | prod | ALL | NULL | NULL | NULL |
NULL | 27 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | reg | index | NULL | PRIMARY | 44 |
NULL | 48 | Using index |
| 1 | PRIMARY | typ1 | ref | PRIMARY | PRIMARY | 22 |
test1.reg.typeId | 1 | |
| 1 | PRIMARY | typ2 | ref | PRIMARY | PRIMARY | 22 |
test1.typ1.fatherTypeId | 1 | |
| 2 | DEPENDENT SUBQUERY | typ | ALL | PRIMARY | NULL | NULL |
NULL | 21 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | reg | ref | PRIMARY | PRIMARY | 22 |
test1.typ.typeId | 2 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | prod | ref | PRIMARY | PRIMARY | 22 |
test1.reg.sku | 1 | Using index |
+----+--------------------+-------+-------+---------------+---------+---------+-
------------------------+------+----------------------------------------------+