两句SQL（部门有多少员工；薪资等级）

三张表，部门表，员工表，薪资表

image.png
image.png
image.png

1.查询每个部门有多少人
2.给一个员工编号，确定其薪资水平

image.png

-------------------------------------分界线--------------------------------------------------------
根据一下表结构及其关系，写出符合业务需求的SQL。

image.png

1.列出库存数量小于10的SKU系统编号、SKU名称、仓库系统编号和其库存数量，按仓库系统编号升序，库存数量倒序排列。

##列出库存数量小于10的SKU系统编号、SKU名称、仓库系统编号和其库存数量，按仓库系统编号升序，库存数量倒序排列。
select
inv.sku_id SKU系统编号,
s.name SKU名称,
inv.store_id 仓库系统编号,
inv.quantity 库存数量
from 
inventory inv
left join sku s on s.id = inv.sku_id
where inv.quantity < 10
ORDER BY inv.store_id,inv.quantity desc;

2.列出包含大于等于5个SKU的SPU记录，显示SPU系统编号和SKU个数，按SKU个数倒序排列。

##列出包含大于等于5个SKU的SPU记录，显示SPU系统编号和SKU个数，按SKU个数倒序排列。
SELECT spu_id SPU系统编号,count(id) SKU个数 FROM sku GROUP BY spu_id HAVING count( id ) >= 5 ORDER BY SKU个数 desc;

3.列出2020年销售数量最大的10个SKU，显示SPU系统编号、SKU系统编号、SKU名称以及销售数量，按SKU的销售数量倒序排序。

##列出2020年销售数量最大的10个SKU，显示SPU系统编号、SKU系统编号、SKU名称以及销售数量，按SKU的销售数量倒序排序。
SELECT
    su.spu_id SPU系统编号,
    su.id SKU系统编号,
    su.NAME SKU名称,
    a.amount 销售数量 
FROM
    (SELECT
    sum( oi.quantity ) amount,
    oi.sku_id 
FROM
    order_items oi
    LEFT JOIN orders o ON o.id = oi.order_id 
WHERE
    YEAR(o.place_time) = '2020' 
GROUP BY    oi.sku_id 
ORDER BY    amount DESC     LIMIT 10    ) a
    LEFT JOIN sku su ON su.id = a.sku_id;

名词解释：

SPU = Standard Product Unit （标准化产品单元）,SPU是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。

SKU=stock keeping unit(库存量单位) SKU即库存进出计量的单位（买家购买、商家进货、供应商备货、工厂生产都是依据SKU进行的）。

具体示例请参考：https://www.jianshu.com/p/c86fe57a3a4c

注：建表语句和测试数据插入脚本如下

DROP TABLE IF EXISTS `spu`;
CREATE TABLE `spu`  (
  `id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT 'SPU系统编号',
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SPU名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of spu
-- ----------------------------
INSERT INTO `spu` VALUES (1, '苹果6s');
INSERT INTO `spu` VALUES (2, '6s耳机');
INSERT INTO `spu` VALUES (3, 'ipad');
INSERT INTO `spu` VALUES (4, '电脑');

DROP TABLE IF EXISTS `sku`;
CREATE TABLE `sku`  (
  `id` bigint(10) NOT NULL COMMENT 'SKU系统编号',
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SKU名称',
  `price` double(10, 2) NULL DEFAULT NULL COMMENT 'SKU单价',
  `size` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SKU类型,枚举XS，S,M,L,XL',
  `spu_id` bigint(10) NULL DEFAULT NULL COMMENT 'SPU系统编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sku
-- ----------------------------
INSERT INTO `sku` VALUES (1, '6s金色M', 5000.00, 'M', 1);
INSERT INTO `sku` VALUES (2, '6s金色L', 5000.00, 'L', 1);
INSERT INTO `sku` VALUES (3, '6s金色XL', 5000.00, 'XL', 1);
INSERT INTO `sku` VALUES (4, '6s金色S', 5000.00, 'S', 1);
INSERT INTO `sku` VALUES (5, '6s银色XS', 5000.00, 'XS', 1);
INSERT INTO `sku` VALUES (6, '6s银色S', 5000.00, 'S', 1);
INSERT INTO `sku` VALUES (7, '6s银色M', 5000.00, 'M', 2);
INSERT INTO `sku` VALUES (8, '6s银色L', 5000.00, 'L', 2);
INSERT INTO `sku` VALUES (9, '6s黄色XL', 5000.00, 'XL', 2);
INSERT INTO `sku` VALUES (10, '6s粉色XS', 5000.00, 'XS', 2);
INSERT INTO `sku` VALUES (11, '6s粉色S', 5000.00, 'S', 2);

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '订单系统编号',
  `total_amount` double(255, 0) NULL DEFAULT NULL COMMENT '订单总金额',
  `place_time` date NULL DEFAULT NULL COMMENT '下单时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (1, 10000, '2020-12-16');
INSERT INTO `orders` VALUES (2, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (3, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (4, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (5, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (6, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (7, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (8, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (9, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (10, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (11, 20000, '2020-12-16');
INSERT INTO `orders` VALUES (12, 20000, '2020-12-16');

DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items`  (
  `id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '订单明细编号',
  `sku_id` bigint(10) NULL DEFAULT NULL COMMENT 'SKU系统编号',
  `order_id` bigint(10) NULL DEFAULT NULL COMMENT '订单系统编号',
  `price` bigint(10) NULL DEFAULT NULL COMMENT 'SKU单价',
  `quantity` int(255) NULL DEFAULT NULL COMMENT 'SKU数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_items
-- ----------------------------
INSERT INTO `order_items` VALUES (1, 1, 1, 10000, 2);
INSERT INTO `order_items` VALUES (2, 2, 1, 15000, 3);
INSERT INTO `order_items` VALUES (3, 3, 3, 10000, 2);
INSERT INTO `order_items` VALUES (4, 4, 4, 10000, 2);
INSERT INTO `order_items` VALUES (5, 5, 5, 20000, 4);
INSERT INTO `order_items` VALUES (6, 6, 6, 50000, 10);
INSERT INTO `order_items` VALUES (7, 7, 7, 60000, 12);
INSERT INTO `order_items` VALUES (8, 8, 8, 25000, 5);
INSERT INTO `order_items` VALUES (9, 9, 9, 35000, 7);
INSERT INTO `order_items` VALUES (10, 2, 2, 15000, 3);
INSERT INTO `order_items` VALUES (11, 10, 10, 40000, 8);
INSERT INTO `order_items` VALUES (12, 11, 11, 45000, 9);
INSERT INTO `order_items` VALUES (13, 9, 9, 5000, 1);

DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory`  (
  `store_id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '仓库系统编号',
  `sku_id` bigint(10) NULL DEFAULT NULL COMMENT 'sku系统编号',
  `quantity` int(100) NULL DEFAULT NULL COMMENT '库存数量',
  PRIMARY KEY (`store_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of inventory
-- ----------------------------
INSERT INTO `inventory` VALUES (1, 1, 10);
INSERT INTO `inventory` VALUES (2, 2, 9);
INSERT INTO `inventory` VALUES (3, 3, 11);
INSERT INTO `inventory` VALUES (4, 4, 12);
INSERT INTO `inventory` VALUES (5, 5, 1);
INSERT INTO `inventory` VALUES (6, 6, 14);
INSERT INTO `inventory` VALUES (7, 7, 5);
INSERT INTO `inventory` VALUES (8, 8, 16);
INSERT INTO `inventory` VALUES (9, 9, 17);
INSERT INTO `inventory` VALUES (10, 10, 18);
INSERT INTO `inventory` VALUES (11, 11, 19);
INSERT INTO `inventory` VALUES (12, 12, 20);