把查询语句那些没用的删除后如下: Select plucode,pluname,hxtotal from table现在就是想实现增加ABC这一列的功能。 累加 和百分比这两列,只是为了说明问题。
with t as (select 123 plucode, 'QQWEWQ' pluname, 15 hxtoal from dual union all select 124 plucode, 'QQWEWW' pluname, 14 hxtoal from dual union all select 128 plucode, 'QQWEWE' pluname, 13 hxtoal from dual union all select 126 plucode, 'QQWEWR' pluname, 12 hxtoal from dual union all select 127 plucode, 'QQWEWT' pluname, 11 hxtoal from dual union all select 129 plucode, 'QQWEWY' pluname, 10 hxtoal from dual union all select 120 plucode, 'QQWEWU' pluname, 9 hxtoal from dual union all select 121 plucode, 'QQWEWI' pluname, 8 hxtoal from dual) SELECT T2.*, CASE WHEN SUM_RATE < 0.7 THEN 'A' WHEN SUM_RATE >= 0.7 AND SUM_RATE <= 0.9 THEN 'B' ELSE 'C' END ABC FROM (SELECT T1.PLUCODE, T1.PLUNAME, T1.HXTOAL, T1.SUM_HXTOAL, ROUND(T1.SUM_HXTOAL / (SELECT SUM(HXTOAL) FROM T), 3) SUM_RATE FROM (SELECT T.*, SUM(HXTOAL) OVER(ORDER BY HXTOAL DESC) SUM_HXTOAL FROM T ORDER BY HXTOAL DESC) T1) T2 ORDER BY HXTOAL DESC;注意:T表为测试所需而建的临时表
Select plucode,pluname,hxtotal
from table现在就是想实现增加ABC这一列的功能。
累加 和百分比这两列,只是为了说明问题。
with t as
(select 123 plucode, 'QQWEWQ' pluname, 15 hxtoal
from dual
union all
select 124 plucode, 'QQWEWW' pluname, 14 hxtoal
from dual
union all
select 128 plucode, 'QQWEWE' pluname, 13 hxtoal
from dual
union all
select 126 plucode, 'QQWEWR' pluname, 12 hxtoal
from dual
union all
select 127 plucode, 'QQWEWT' pluname, 11 hxtoal
from dual
union all
select 129 plucode, 'QQWEWY' pluname, 10 hxtoal
from dual
union all
select 120 plucode, 'QQWEWU' pluname, 9 hxtoal
from dual
union all
select 121 plucode, 'QQWEWI' pluname, 8 hxtoal
from dual)
SELECT T2.*,
CASE
WHEN SUM_RATE < 0.7 THEN
'A'
WHEN SUM_RATE >= 0.7 AND SUM_RATE <= 0.9 THEN
'B'
ELSE
'C'
END ABC
FROM (SELECT T1.PLUCODE,
T1.PLUNAME,
T1.HXTOAL,
T1.SUM_HXTOAL,
ROUND(T1.SUM_HXTOAL / (SELECT SUM(HXTOAL) FROM T), 3) SUM_RATE
FROM (SELECT T.*, SUM(HXTOAL) OVER(ORDER BY HXTOAL DESC) SUM_HXTOAL
FROM T
ORDER BY HXTOAL DESC) T1) T2
ORDER BY HXTOAL DESC;注意:T表为测试所需而建的临时表