我有一个销售汇总表,如:
CREATE TABLE `sales_data` (
`id` int(4) unsigned NOT NULL,
`start_date` date NOT NULL,
`end_date` date DEFAULT NULL,
`store_sales` int(11) DEFAULT 0,
PRIMARY KEY (`id`,`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
id
列是产品ID, store_sales
是本周的总项目销售额 .
我希望根据销售情况找到特定产品ID的等级 AND 销售的独特产品总数 - 不是销售总额,而是不同的项目 .
到目前为止我有:
SELECT FORMAT(tmp.rank, 0) AS rank, tmp.id, FORMAT(MAX(tmp.row_num), 0) AS total
FROM (
SELECT
sd.id
DENSE_RANK() OVER (ORDER BY SUM(sd.store_sales) DESC) AS rank,
ROW_NUMBER() OVER (ORDER BY sd.id) AS row_num
FROM sales_data sd
WHERE sd.start_date >= '2017-01-01'
AND sd.end_date <= '2017-05-15'
GROUP BY sd.id
) AS tmp
WHERE tmp.id = 9233
LIMIT 1;
我认为,问题是 MAX(tmp.row_num)
受外部查询中的 WHERE
子句限制,只需选择该单个项目的行号 .
有没有办法在不运行子查询两次的情况下实现这一目的?
Edit: 这就是我为内部查询获取的内容:
rank,id,total
-------------------
1,4920,2
2,4043,3
3,3514,5
4,2425,7
5,4503,11
...
虽然这是你想要的:
rank,id,total
-------------------
1,4920,373
2,4043,373
3,3514,373
4,2425,373
5,4503,373
...
所以我的外部查询可以选择 WHERE id = 4043
,我可以打印为 Ranked {rank} out of {total}
给我 Ranked 2 out of 373
.
1 回答
我相信DENSE_RANK()OVER(ORDER BY COUNT(sd.id)DESC)会做到这一点 .
;