56,687
社区成员
发帖
与我相关
我的任务
分享
select 商品,日期,销量
(select 日期 from 表tt where 商品=t.商品 order by 销量 desc limit 1) as 最大销量发生的日期,
(select 销量 from 表tt where 商品=t.商品 order by 销量 desc limit 1) as 最大销量
from 表tt a
where 日期='2016-01-22'
SELECT t1.商品, t1.日期 AS 最大销量发生的日期, t2.销量 AS 最大销量,
t2.日期 AS 指定日期, t2.销量 AS 指定日期最大销量, t2.销量/t1.销量 AS 比例 FROM
(SELECT 商品, MAX(销量) AS 销量 FROM tt GROUP BY 商品) AS t2
INNER JOIN tt AS t1
ON t1.商品 = t2.商品 AND t1.销量 =t2.销量
INNER JOIN
(SELECT 商品, 日期, MAX(销量) AS 销量 FROM tt
WHERE DATE_FORMAT(日期, '%Y-%m-%d') = '2016-01-22'
GROUP BY 商品) AS t3
ON t1.商品 = t3.商品
SELECT t1.商品, t1.日期 AS 最大销量发生的日期, t2.销量 AS 最大销量,
t2.日期 AS 指定日期, t2.销量 AS 指定日期最大销量, CONVERT((1.000*t2.销量/t1.销量), DECIMAL(18, 3)) AS 比例 FROM
(SELECT 商品, MAX(销量) AS 销量 FROM tt GROUP BY 商品) AS t2
INNER JOIN tt AS t1
ON t1.商品 = t2.商品 AND t1.销量 =t2.销量
INNER JOIN
(SELECT 商品, 日期, MAX(销量) AS 销量 FROM tt
WHERE DATE_FORMAT(日期, '%Y-%m-%d') = '2016-01-22'
GROUP BY 商品) AS t3
ON t1.商品 = t3.商品