Mysql刷题笔记 1.15 查找每种商品最近一天的日平均单价

-- 1.15 查找每种商品最近一天的日平均单价

Create table T0115(
	商品 nvarchar(22),
	日期 Date,
	单价 int,
	数量 int
);
Insert INTO T0115 VALUES ('桌子','2016-01-01',100,1);
Insert INTO T0115 VALUES ('椅子','2016-01-01',50,4);
Insert INTO T0115 VALUES ('桌子','2016-01-02',100,2);
Insert INTO T0115 VALUES ('桌子','2016-01-02',105,1);

T0115
+--------+------------+--------+--------+
| 商品   | 日期        | 单价    | 数量   |
+--------+------------+--------+--------+
| 桌子   | 2016-01-01 |    100 |      1 |
| 椅子   | 2016-01-01 |     50 |      4 |
| 桌子   | 2016-01-02 |    100 |      2 |
| 桌子   | 2016-01-02 |    105 |      1 |
+--------+------------+--------+--------+

结果
+--------+------------+--------------+
| 商品   | 日期       | 平均单价     |
+--------+------------+--------------+
| 桌子   | 2016-01-02 |       101.67 |
| 椅子   | 2016-01-01 |        50.00 |
+--------+------------+--------------+

解题思路
1. 根据结果 可以看出 需要对商品进行分组
2. 每组需要计算 单价*数量之和 / 数量之和
3. 但是这样会导致 桌子的出现两天的数据 所以需要对商品分组 按照日期从大到小
4. 选出最近一天的答案



+--------+------------+--------------+
| 商品   | 日期        | 平均单价     |
+--------+------------+--------------+
| 桌子   | 2016-01-01 |       100.00 |
| 椅子   | 2016-01-01 |        50.00 |
| 桌子   | 2016-01-02 |       101.67 |
+--------+------------+--------------+

-- 1.15 查找每种商品最近一天的日平均单价

Create table T0115(
	商品 nvarchar(22),
	日期 Date,
	单价 int,
	数量 int
);
Insert INTO T0115 VALUES ('桌子','2016-01-01',100,1);
Insert INTO T0115 VALUES ('椅子','2016-01-01',50,4);
Insert INTO T0115 VALUES ('桌子','2016-01-02',100,2);
Insert INTO T0115 VALUES ('桌子','2016-01-02',105,1);

T0115
+--------+------------+--------+--------+
| 商品   | 日期        | 单价    | 数量   |
+--------+------------+--------+--------+
| 桌子   | 2016-01-01 |    100 |      1 |
| 椅子   | 2016-01-01 |     50 |      4 |
| 桌子   | 2016-01-02 |    100 |      2 |
| 桌子   | 2016-01-02 |    105 |      1 |
+--------+------------+--------+--------+

结果
+--------+------------+--------------+
| 商品   | 日期       | 平均单价     |
+--------+------------+--------------+
| 桌子   | 2016-01-02 |       101.67 |
| 椅子   | 2016-01-01 |        50.00 |
+--------+------------+--------------+

解题思路
1. 根据结果 可以看出 需要对商品进行分组
2. 每组需要计算 单价*数量之和 / 数量之和
3. 但是这样会导致 桌子的出现两天的数据 所以需要对商品分组 按照日期从大到小
4. 选出最近一天的答案

SELECT 商品,日期,CAST(SUM(单价 * 1.0 * 数量) / SUM(数量) AS DECIMAL(8,2)) AS 平均单价
FROM  T0115
GROUP BY 商品,日期

+--------+------------+--------------+
| 商品   | 日期        | 平均单价     |
+--------+------------+--------------+
| 桌子   | 2016-01-01 |       100.00 |
| 椅子   | 2016-01-01 |        50.00 |
| 桌子   | 2016-01-02 |       101.67 |
+--------+------------+--------------+


SELECT 商品,日期,CAST(SUM(单价 * 1.0 * 数量) / SUM(数量) AS DECIMAL(8,2)) AS 平均单价
FROM 
(
	SELECT *,DENSE_RANK() OVER(PARTITION BY 商品 ORDER BY 日期 DESC) rn
    FROM T0115
)t
WHERE rn = 1
GROUP BY 商品,日期