解决方案 »
- pro*c的动态游标获取不到值
- 关于异常处理的问题
- 应届生面试的一个问题??
- Oracle备份时可以正常使用吗?急死了,经理等着我解决,大家帮个忙啊(全身家当)----备份与正常使用
- 突然断电后,Oracle服务不能启动,请问怎么办?
- 一个简单的SQL问题(关于oracle的select 语句)
- 菜鸟问题:网站要发布了,数据库怎么迁移/发布?
- 在线急等,安装数据库
- select * from dba_users where ...的奇怪问题?
- 如何修改Oracle8i(8.1.6.0.0)首选身份证明(OS is Win2000)
- Sybase与Oracle SQL语句问题,请各位大虾指点,“散分啊”
- 求助,insert into报错,最常用的句型
SUM (DECODE (q1, 'A', 1, 0)) / COUNT (q1) "q1选择A的比率",
SUM (DECODE (q1, 'B', 1, 0)) / COUNT (q1) "q1选择B的比率",
SUM (DECODE (q1, 'C', 1, 0)) / COUNT (q1) "q1选择C的比率",
SUM (DECODE (q1, 'D', 1, 0)) / COUNT (q1) "q1选择D的比率",
COUNT (q2) "q2回答次数",
SUM (DECODE (q2, 'A', 1, 0)) / COUNT (q2) "q2选择A的比率",
SUM (DECODE (q2, 'B', 1, 0)) / COUNT (q2) "q2选择B的比率",
SUM (DECODE (q2, 'C', 1, 0)) / COUNT (q2) "q2选择C的比率",
SUM (DECODE (q2, 'D', 1, 0)) / COUNT (q2) "q2选择D的比率"
FROM table1
(
SELECT '张三' a, 'A' q1, 'C' q2 FROM dual
UNION ALL
SELECT '李四', 'B', 'C' FROM dual
)
SELECT COUNT(q1),
sum(CASE WHEN q1='A' THEN 1 ELSE 0 END)/COUNT(q1) A1,
sum(CASE WHEN q1='B' THEN 1 ELSE 0 END)/COUNT(q1) B1,
sum(CASE WHEN q1='C' THEN 1 ELSE 0 END)/COUNT(q1) C1,
sum(CASE WHEN q1='D' THEN 1 ELSE 0 END)/COUNT(q1) D1,
COUNT(q2),
sum(CASE WHEN q2='A' THEN 1 ELSE 0 END)/COUNT(q2) A2,
sum(CASE WHEN q2='B' THEN 1 ELSE 0 END)/COUNT(q2) B2,
sum(CASE WHEN q2='C' THEN 1 ELSE 0 END)/COUNT(q2) C2,
sum(CASE WHEN q2='D' THEN 1 ELSE 0 END)/COUNT(q2) D2FROM t
WITH t AS
(
SELECT '张三' a, null q1, null q2 FROM dual
UNION ALL
SELECT '李四', null, 'C' FROM dual
)SELECT COUNT(q1),
sum(CASE WHEN q1='A' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q1) = 0 THEN 1 ELSE COUNT(q1) END)*100||'%' A1,
sum(CASE WHEN q1='B' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q1) = 0 THEN 1 ELSE COUNT(q1) END)*100||'%' B1,
sum(CASE WHEN q1='C' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q1) = 0 THEN 1 ELSE COUNT(q1) END)*100||'%' C1,
sum(CASE WHEN q1='D' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q1) = 0 THEN 1 ELSE COUNT(q1) END)*100||'%' D1,
COUNT(q2),
sum(CASE WHEN q2='A' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q2) = 0 THEN 1 ELSE COUNT(q2)END)*100||'%' A2,
sum(CASE WHEN q2='B' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q2) = 0 THEN 1 ELSE COUNT(q2) END)*100||'%' B2,
sum(CASE WHEN q2='C' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q2) = 0 THEN 1 ELSE COUNT(q2) END)*100||'%' C2,
sum(CASE WHEN q2='D' THEN 1 ELSE 0 END)/(CASE WHEN COUNT(q2) = 0 THEN 1 ELSE COUNT(q2) END)*100||'%' D2FROM t
(
NAME VARCHAR(20),
q1 VARCHAR(20),
q2 VARCHAR(20)
);INSERT INTO t VALUES('001', 'A', 'A');
INSERT INTO t VALUES('002', 'B', 'B');
INSERT INTO t VALUES('003', 'C', 'C');
INSERT INTO t VALUES('004', 'D', 'D');
INSERT INTO t VALUES('005', 'A', 'B');
INSERT INTO t VALUES('006', 'A', 'C');
INSERT INTO t VALUES('007', 'A', 'D');
INSERT INTO t VALUES('008', 'B', 'A');
INSERT INTO t VALUES('009', 'B', 'C');
INSERT INTO t VALUES('010', 'B', 'D');
INSERT INTO t VALUES('011', 'C', 'A');
INSERT INTO t VALUES('012', 'C', 'B');
INSERT INTO t VALUES('013', 'C', 'D');
INSERT INTO t VALUES('014', 'D', 'A');
INSERT INTO t VALUES('015', 'D', 'B');
INSERT INTO t VALUES('016', 'D', 'C');SELECT COUNT(q1) AS "q1回答次数",
SUM(DECODE(q1, 'A', 1, 0))/COUNT(q1) AS "q1选择A的比率",
SUM(DECODE(q1, 'B', 1, 0))/COUNT(q1) AS "q1选择B的比率",
SUM(DECODE(q1, 'C', 1, 0))/COUNT(q1) AS "q1选择C的比率",
SUM(DECODE(q1, 'D', 1, 0))/COUNT(q1) AS "q1选择D的比率",
COUNT (q2) AS "q2回答次数",
SUM(DECODE(q2, 'A', 1, 0))/COUNT(q2) AS "q2选择A的比率",
SUM(DECODE(q2, 'B', 1, 0))/COUNT(q2) AS "q2选择B的比率",
SUM(DECODE(q2, 'C', 1, 0))/COUNT(q2) AS "q2选择C的比率",
SUM(DECODE(q2, 'D', 1, 0))/COUNT(q2) AS "q2选择D的比率"
FROM t;