我将表 sales_by_year_semester 定义为

CREATE TABLE `sales_by_year_semester` ( 
    `order_year` INT(4) NOT NULL, 
    `semester` TINYINT(1), 
    `year_semester` VARCHAR(6), 
    `productCode` VARCHAR(10), 
    `total_sales_in_usd` FLOAT 
) ENGINE=InnoDB; 

它有以下数据:

| order_year | semester | year_semester | productCode | total_sales_in_usd | 
|------------|----------|---------------|-------------|--------------------| 
| 2019       | 1        | 2019-1        | S32_2206    | 2025.16            | 
| 2019       | 1        | 2019-1        | S24_2972    | 2285.76            | 
| 2019       | 1        | 2019-1        | S18_3232    | 26052.88           | 
| 2019       | 2        | 2019-2        | S24_1937    | 6770.34            | 
| 2019       | 2        | 2019-2        | S24_2840    | 7159.86            | 
| 2019       | 2        | 2019-2        | S12_1099    | 44466.99           | 
| 2019       | 2        | 2019-2        | S10_1949    | 48878.16           | 
| 2019       | 2        | 2019-2        | S18_3232    | 77427.42           | 
| 2020       | 1        | 2020-1        | S24_1937    | 3903.19            | 
| 2020       | 1        | 2020-1        | S24_2840    | 4470.39            | 
| 2020       | 1        | 2020-1        | S10_4698    | 37297.31           | 
| 2020       | 1        | 2020-1        | S12_1108    | 39203.54           | 
| 2020       | 1        | 2020-1        | S18_3232    | 43418.87           | 
| 2020       | 2        | 2020-2        | S24_3969    | 7907.94            | 
| 2020       | 2        | 2020-2        | S24_1937    | 8303.47            | 
| 2020       | 2        | 2020-2        | S12_1108    | 66283.92           | 
| 2020       | 2        | 2020-2        | S18_3232    | 76962.53           | 
| 2021       | 1        | 2021-1        | S24_3969    | 4235.98            | 
| 2021       | 1        | 2021-1        | S24_1937    | 5660.64            | 
| 2021       | 1        | 2021-1        | S10_4698    | 34856.12           | 
| 2021       | 1        | 2021-1        | S18_3232    | 52978.28           | 

如何获取美元销售额最少/最多的产品，它是每个 year_semester 的 productCode，格式如下:

| order_year | semester | year_semester | productCode_least_sales | least_sales_in_usd | productCode_most_sales | most_sales_in_usd | 
|------------|----------|---------------|-------------------------|--------------------|------------------------|-------------------| 
| 2019       | 1        | 2019-1        | S32_2206                | 2025.16            | S18_3232               | 26052.88          | 
| 2019       | 2        | 2019-2        | S24_1937                | 6770.34            | S18_3232               | 77427.42          | 
| 2020       | 1        | 2020-1        | S24_1937                | 3903.19            | S18_3232               | 43418.87          | 
| 2020       | 2        | 2020-2        | S24_3969                | 7907.94            | S18_3232               | 76962.53          | 
| 2021       | 1        | 2021-1        | S24_3969                | 4235.98            | S18_3232               | 52978.28          | 

我尝试做的是使用 CASE WHEN 来选择 MIN(total_sales_in_usd), MAX(total_sales_in_usd) 但我无法获得每个值对应的 productCode。

SELECT  
    order_year, 
    semester, 
    year_semester,  
    CASE WHEN  
        MIN(DISTINCT total_sales_in_usd) THEN productCode END  
        as productCode_least_sales, 
    MIN(total_sales_in_usd), 
    CASE WHEN  
        MAX(DISTINCT total_sales_in_usd) THEN productCode END as productCode_most_sales, 
    MAX(total_sales_in_usd) 
    FROM 
        sales_by_year_semester  
    GROUP BY  
        year_semester ; 
 

请您参考如下方法:

我喜欢使用 ROW_NUMBER 来解决这类问题。假设您使用的是 MySQL 8+，我们可以尝试:

WITH cte AS ( 
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY year_semester ORDER BY total_sales_in_usd) rn_min, 
        ROW_NUMBER() OVER (PARTITION BY year_semester ORDER BY total_sales_in_usd DESC) rn_max 
    FROM sales_by_year_semester 
) 
 
SELECT 
    order_year, 
    semester, 
    year_semester, 
    MAX(CASE WHEN rn_min = 1 THEN productCode END) AS productCode_least_sales, 
    MAX(CASE WHEN rn_min = 1 THEN total_sales_in_usd END) AS least_sales_in_usd, 
    MAX(CASE WHEN rn_max = 1 THEN productCode END) AS productCode_most_sales, 
    MAX(CASE WHEN rn_max = 1 THEN total_sales_in_usd END) AS most_sales_in_usd 
FROM cte 
GROUP BY 
    order_year, 
    semester, 
    year_semester 
ORDER BY 
    order_year, 
    semester, 
    year_semester;