产品销售分析 III（SQL）-leetcode1070

问题描述：编写一个 SQL 查询，选出每个销售产品的 第一年 的 产品 id、年份、数量 和 价格。

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是销售表 Sales 的主键.
product_id 是产品表 Product 的外键.
注意: price 表示每单位价格
产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是表的主键.
 

示例：

查询结果格式如下：

Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result table:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+
 

首先：product的ddl

    

    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
     
    -- ----------------------------
    -- Table structure for product
    -- ----------------------------
    DROP TABLE IF EXISTS `product`;
    CREATE TABLE `product`  (
      `id` int(0) NOT NULL,
      `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
     
    -- ----------------------------
    -- Records of product
    -- ----------------------------
    INSERT INTO `product` VALUES (100, '瓜子');
    INSERT INTO `product` VALUES (200, '花生');
    INSERT INTO `product` VALUES (300, '矿泉水');
     
    SET FOREIGN_KEY_CHECKS = 1;

其次：sales的DDL

   

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
     
    -- ----------------------------
    -- Table structure for sales
    -- ----------------------------
    DROP TABLE IF EXISTS `sales`;
    CREATE TABLE `sales`  (
      `id` int(0) NOT NULL AUTO_INCREMENT,
      `product_id` int(0) DEFAULT NULL,
      `year` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      `quantity` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      `price` decimal(10, 2) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
     
    -- ----------------------------
    -- Records of sales
    -- ----------------------------
    INSERT INTO `sales` VALUES (1, 100, '2020', '10', 5000.00);
    INSERT INTO `sales` VALUES (2, 100, '2021', '10', 5000.00);
    INSERT INTO `sales` VALUES (3, 200, '2022', '15', 9000.00);
     
    SET FOREIGN_KEY_CHECKS = 1;

上sql，拿去就可以运行

select product_id,year first_year,quantity,price
from sales
where (product_id,year) in (select product_id,min(year) from Sales group by product_id);

看着简单，反正我第一反应没有写出来。
  我要刷100道算法题，第39道