已有一个视图,sql语句如下: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;结果是:
名称 数量 比例
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?
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;结果是:
名称 数量 比例
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?
解决方案 »
- 启动OracleDBConsoleorcl时报如下错误
- 请问:oracle里是不是不能插入 繁体字——
- 请教,导出ORACLE 的一个用户,但不导出这个用户下面的某个表,如何实现?
- Oracle存储过程重复项不显示问题
- ORACLE 按照月份查询所有数据问题
- 初学,一个pl/sql的问题
- 请问高手:怎样得到一个表中的第n条到第n+20条数据?
- oracle官方的PL/SQL说明文档在什么地方下载.找了半天没找到.
- 在oracle9i的客户端,开始-〉运行下,输入sqlplus,出现协议适配器错误,为什么?
- OpenStack云环境下可以安装Oracle 11g和RAC吗
- 紧急求助~关于两个表的查询问题~~!!!!
- 一条信息的元素数量可变,数据库表结构该如何设计进行保存?
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
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;
t.xms,
t.xmsbl,
DECODE(ROW_NUMBER() OVER(ORDER BY t.xmsbl DESC), 1, 1, 0) flag
FROM v_kjcgfb_xy t
ORDER BY t.DWBZMC
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) flag
FROM (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) t
ORDER BY t.DWBZMC
(
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);
结果:
COUNT(DISTINCT A.ID) = MAX(COUNT(DISTINCT A.ID)) over() THEN 1
ELSE 0 END "标识"