关于在视图中去最大值的有关问题_Oracle开发

关于在视图中去最大值的问题
已有一个视图，sql语句如下：

SQL code

create or replace view v_kjcgfb_xy asselect c.DWBZMC as mc,cast(count(distinct(a.id))as varchar(40)) as xms,cast(100*count(distinct(a.id))/(select count(*) from t_cg a where a.hzdq is not null and a.dxjf is not null) as numeric(10,2)) as xmsblfrom t_cg a left join t_expcg b on a.cgid=b.cgidleft join v_loaduser c on b.userno=c.ZGH where a.hzdq is not null and a.dxjf is not null group by c.DWBZMC;

结果是：
名称数量比例
aaa 1 0.25
bbb 1 0.25
ccc 2 0.5

现在我想在这个视图后面再加一列，因为程序里面需要，加一列标识，效果如下：

名称数量比例标识
aaa 1 0.25 0
bbb 1 0.25 0
ccc 2 0.5 1

也就是数量最大的哪一项后面标识为1，其他为0.
请问在原来的视图基础上可以做吗？可以的话，要怎么改sql？

------解决方案--------------------

SQL code

with t1 as (select '1' 编号,'aaa' 名称,'2' 数量 from dual union allselect '2',  'bbb','1' from dual union allselect '3',  'ccc','3' from dual)select 名称,数量,case when 数量=(select distinct max(数量) from t1) then 1 else 0 end 标识from t1      名称    数量    标识-----------------------------------------1    aaa    2    02    bbb    1    03    ccc    3    1
------解决方案--------------------
SQL codeCREATE OR REPLACE VIEW v_kjcgfb_xy ASSELECT c.DWBZMC AS mc,       COUNT(DISTINCT a.id) AS xms,       CAST(100 * COUNT(DISTINCT a.id) / SUM(COUNT(DISTINCT a.id)) OVER() AS NUMERIC(10, 2)) AS xmsbl  FROM t_cg a  LEFT JOIN t_expcg b ON a.cgid = b.cgid  LEFT JOIN v_loaduser c ON b.userno = c.ZGH WHERE a.hzdq IS NOT NULL   AND a.dxjf IS NOT NULL GROUP BY c.DWBZMC;
------解决方案--------------------
测试数据：
SQL codeCREATE TABLE T169(   F1 VARCHAR2(20),   F2 NUMBER(4),   F3 NUMBER(5, 2));INSERT INTO T169 VALUES('aaa', 1, 0.25);INSERT INTO T169 VALUES('bbb', 1, 0.25);INSERT INTO T169 VALUES('ccc', 2, 0.5);
------解决方案--------------------
,CASE WHEN  
           COUNT(DISTINCT A.ID) = MAX(COUNT(DISTINCT A.ID)) over() THEN 1
           ELSE 0 END "标识"
------解决方案--------------------
探讨

引用:
那你就把sql部分放到v_kjcgfb_xy 里啊


SQL code

CREATE OR REPLACE VIEW v_kjcgfb_xy AS
SELECT t.DWBZMC,
t.xms,
t.xmsbl,
DECODE(ROW_NUMBER() OVER(ORDER BY t.xmsbl DESC), 1, 1, 0)……