请教一下分组、排序、赋值问题

[封尘-莫若]

根据品牌分组，按销售排序，得到每个品牌的销售ABCD状态，前百分之25是A类，后0.25是B，在后是C，最后0.25是D类
我现在想得到按品牌分组，得到每个品牌的销售ABCD状态；
SELECT
品牌，
品类，
SUM(销售额),
NTILE(4) OVER (ORDER BY SUM(销售额) DESC NULLS LAST ),
(
CASE WHEN NTILE(4) OVER (ORDER BY SUM(销售额) DESC NULLS LAST )=1 THEN A
CASE WHEN NTILE(4) OVER (ORDER BY SUM(销售额) DESC NULLS LAST )=2 THEN B
CASE WHEN NTILE(4) OVER (ORDER BY SUM(销售额) DESC NULLS LAST )=3 THEN B
CASE WHEN NTILE(4) OVER (ORDER BY SUM(销售额) DESC NULLS LAST )=4 THEN B
)
FROM TABLE
GROUP BY 品牌，品类
ORDER BY SUM(销售额) desc

我这么写出来就只能按整个大盘进行状态分组，无法根据每个品牌进行状态划分怎么破

[封尘-莫若]

我既想得到按整个大盘划分的，又想得到按每个品牌的，有办法搞嘛

[ZJHZ_叶]

SELECT PP,PL,XSE,
CASE WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=1 THEN 'A'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=2 THEN 'B'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=3 THEN 'C'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=4 THEN 'D'
END 销售状态
,
case when
NTILE(4) OVER( ORDER BY XSE DESC)=1 then 'A'
when
NTILE(4) OVER( ORDER BY XSE DESC)=2 then 'B'
when
NTILE(4) OVER( ORDER BY XSE DESC)=3 then 'C'
when
NTILE(4) OVER( ORDER BY XSE DESC)=4 then 'D' END 大盘状
FROM TABLE_TEST
order by 1,3 desc

[封尘-莫若]

引用 2 楼 ZJHZ_叶 的回复:

SELECT PP,PL,XSE, CASE WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=1 THEN 'A' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=2 THEN 'B' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=3 THEN 'C' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=4 THEN 'D' END 销售状态 , case when NTILE(4) OVER( ORDER BY XSE DESC)=1 then 'A' when NTILE(4) OVER( ORDER BY XSE DESC)=2 then 'B' when NTILE(4) OVER( ORDER BY XSE DESC)=3 then 'C' when NTILE(4) OVER( ORDER BY XSE DESC)=4 then 'D' END 大盘状 FROM TABLE_TEST order by 1,3 desc

最后这个order by 1,3 desc是啥意思嘞？

[yaiger]

表的第1个字段升序，第3个字段降序

引用 3 楼 LXYWXWJQ 的回复:

[quote=引用 2 楼 ZJHZ_叶 的回复:]
SELECT PP,PL,XSE,
CASE WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=1 THEN 'A'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=2 THEN 'B'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=3 THEN 'C'
WHEN
NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=4 THEN 'D'
END 销售状态
,
case when
NTILE(4) OVER( ORDER BY XSE DESC)=1 then 'A'
when
NTILE(4) OVER( ORDER BY XSE DESC)=2 then 'B'
when
NTILE(4) OVER( ORDER BY XSE DESC)=3 then 'C'
when
NTILE(4) OVER( ORDER BY XSE DESC)=4 then 'D' END 大盘状
FROM TABLE_TEST
order by 1,3 desc

最后这个order by 1,3 desc是啥意思嘞？[/quote]

[封尘-莫若]

引用 4 楼 yaiger 的回复:

表的第1个字段升序，第3个字段降序 [quote=引用 3 楼 LXYWXWJQ 的回复:] [quote=引用 2 楼 ZJHZ_叶 的回复:] SELECT PP,PL,XSE, CASE WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=1 THEN 'A' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=2 THEN 'B' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=3 THEN 'C' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=4 THEN 'D' END 销售状态 , case when NTILE(4) OVER( ORDER BY XSE DESC)=1 then 'A' when NTILE(4) OVER( ORDER BY XSE DESC)=2 then 'B' when NTILE(4) OVER( ORDER BY XSE DESC)=3 then 'C' when NTILE(4) OVER( ORDER BY XSE DESC)=4 then 'D' END 大盘状 FROM TABLE_TEST order by 1,3 desc

最后这个order by 1,3 desc是啥意思嘞？[/quote][/quote] 解决了

[封尘-莫若]

引用 4 楼 yaiger 的回复:

表的第1个字段升序，第3个字段降序 [quote=引用 3 楼 LXYWXWJQ 的回复:] [quote=引用 2 楼 ZJHZ_叶 的回复:] SELECT PP,PL,XSE, CASE WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=1 THEN 'A' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=2 THEN 'B' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=3 THEN 'C' WHEN NTILE(4) OVER(PARTITION BY PP ORDER BY XSE DESC)=4 THEN 'D' END 销售状态 , case when NTILE(4) OVER( ORDER BY XSE DESC)=1 then 'A' when NTILE(4) OVER( ORDER BY XSE DESC)=2 then 'B' when NTILE(4) OVER( ORDER BY XSE DESC)=3 then 'C' when NTILE(4) OVER( ORDER BY XSE DESC)=4 then 'D' END 大盘状 FROM TABLE_TEST order by 1,3 desc

最后这个order by 1,3 desc是啥意思嘞？[/quote][/quote] 大神我想问下，如果不等分，比如按销售额倒序排列， 取前百分之10 A 前50%-10% B 前70%-50% C 小于70% D 这个该怎么搞