create or replace view v_kjcgfb_xy as
select 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 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;

with t1 as 
(
select '1' 编号,'aaa' 名称,'2' 数量 from dual union all
select '2',  'bbb','1' from dual union all
select '3',  'ccc','3' from dual
)
select 名称,数量,case when 数量=(select distinct max(数量) from t1) then 1 else 0 end 标识
from t1

      名称    数量    标识
-----------------------------------------
1    aaa    2    0
2    bbb    1    0
3    ccc    3    1

------解决方案--------------------
SQL code

CREATE OR REPLACE VIEW v_kjcgfb_xy AS
SELECT 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 code

CREATE 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)……