时间戳

首页 > 解决方案 > 找出每年每学期以美元计算的销售额最差和最好的产品

mysql - 找出每年每学期以美元计算的销售额最差和最好的产品

问题描述

我将表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           |

我怎样才能获得以美元计销售额最少/最多的产品，它productCode适用year_semester于以下格式：

| 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          |

我试图做的是使用 aCASE 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 ;

标签： mysql

解决方案

你可以这样做left join。

select t1.*, minProduct.product_code,  t2.minSales, maxProduct.product_code, t3.maxSales from 
    (select distinct order_year
        , semester
        , year_semester
    from sales_by_year_semester) as t1
left join
    (select  order_year
        , min(total_sales_in_usd) as minSales   
    from sales_by_year_semester group by order_year) as t2 on t2.order_year = t1.order_year
left join
    (select order_year
        , max(total_sales_in_usd) as maxSales       
    from sales_by_year_semester group by order_year, ) as t3 on t3.order_year = t1.order_year
left join
    (select product_code, total_sales_in_usd, order_year
        from sales_by_year_semester) as minProduct 
            on minProduct.total_sales_in_usd = t2.minSales and minProduct.order_year=t2.order_year
left join
    (select product_code, total_sales_in_usd, order_year
        from sales_by_year_semester) as maxProduct
            on maxProduct.total_sales_in_usd = t3.maxSales and maxProduct.order_year=t2.order_year

推荐阅读