建表,有A、B、C三个商品,求在20190201 ~ 20200802,时间段内:商品卖出最大金额及最大金额对应日期;并且卖出最早日期,以及最早日期对应售卖金额
1、建表测试语句(mysql):
CREATE TABLE `t_product`(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`product_name` varchar(32) DEFAULT NULL COMMENT '商品名',
`amt` decimal(12,2) DEFAULT NULL COMMENT '金额',
`date` varchar(8) DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (1, 'A', 500.00, '20190101');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (2, 'B', 300.00, '20190112');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (3, 'C', 800.00, '20190201');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (4, 'A', 1500.00, '20190101');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (5, 'B', 600.00, '20190203');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (6, 'C', 100.00, '20190306');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (7, 'A', 500.00, '20190509');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (8, 'B', 700.00, '20190607');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (9, 'C', 900.00, '20190802');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (10, 'A', 100.00, '20190118');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (11, 'A', 200.00, '20190118');
INSERT INTO `t_product`(`id`, `product_name`, `amt`, `date`) VALUES (12, 'A', 100.00, '20190218');
插入结果:
2、所有数据展示
select * from t_product;
3、日期范围内数据展示(满足20190201 ~ 20200802):
SELECT
t1.id AS '主键id',
t1.product_name AS '商品名称',
t1.date AS '日期',
t1.amt '对应金额'
FROM
t_product t1
WHERE
t1.date BETWEEN '20190201'
AND '20190802'
4、求结果(这步写的有问题):
表中各商品最小日期,和最小日期对应金额,最大金额,和最大金额对应日期:
SELECT
t1.product_name AS '商品名称',
min( t1.date ) AS '最小日期',
(
SELECT
max( amt )
FROM
t_product t2
WHERE
id IN (
SELECT
min( id )
FROM
t_product t3
WHERE
min( t1.date ) = t2.date
AND t3.product_name = t1.product_name
AND t3.id = t2.id
)
) AS '最小日期对应金额',
max( t1.amt ) AS '最大金额',
( SELECT max( date ) FROM t_product WHERE max( t1.amt ) = amt ) AS '最大金额对应日期'
FROM
t_product t1
WHERE
t1.date BETWEEN '20190201'
AND '20190802'
GROUP BY
t1.product_name
#(查询结果有点问题,在第5步,修正)
5、正确结果
SELECT
t1.product_name AS '商品名称',
min( t1.date ) AS '最小日期',
(
SELECT
max( amt )
FROM
t_product t2
WHERE
t2.date = min( t1.date )
AND t2.product_name = t1.product_name
) AS '最小日期对应金额',
max( t1.amt ) AS '最大金额',
(
SELECT
max( date )
FROM
t_product t2
WHERE
t2.amt = max( t1.amt )
AND t2.product_name = t1.product_name
) AS '最大金额对应日期'
FROM
t_product t1
WHERE
t1.date >= '20180201'
AND t1.date <= '20190802'
GROUP BY
t1.product_name