table A
a1 a2 a3
x 2 3
x 3 3
y 3 3
y 3 3
z 3 3
z 4 5
z 5 5要查询的结果:
x y z
2 2 3
x有两条,y有两条,z有三条这里就一个 统计,x,y,z 要横向一条记录输出。
请指指点。
a1 a2 a3
x 2 3
x 3 3
y 3 3
y 3 3
z 3 3
z 4 5
z 5 5要查询的结果:
x y z
2 2 3
x有两条,y有两条,z有三条这里就一个 统计,x,y,z 要横向一条记录输出。
请指指点。
解决方案 »
- sum,count一起用
- 高手进来解决ORA-12519, TNS:no appropriate service handler found !!!
- 比较难的一个模糊查询SQL
- Rman的基础问题
- pl/sql编程中如何判断varchar2类型数据中含有某子字符串?
- 请问ORACLE里日志文件存在什么地方?
- oracle8i中重启后management serve就不能用了,为何?
- 怎样查看和更改服务器的共享模式和专用模式呀?谢谢
- 存储过程问题 300分 在线等待
- ORACLE 与 WINDOWS ME 系统
- 招Oracle DBA,内部推荐
- 请问我在删除表时提示有主外键使用中,求解决方法和批量删除方法
sum(decode(a1,'y',1)) y,
sum(decode(a1,'z',1)) z
from a
这样查询出来是
x 2
y 2
z 3
我要的效果是
x y z
2 2 3
sum(decode(a1,'y',1)) y,
sum(decode(a1,'z',1)) z
from A;
--sql server 练练手
select
count(case a1 when 'x' then a1 end) x,
count(case a1 when 'y' then a1 end) y,
count(case a1 when 'z' then a1 end) z
from A
CREATE TABLE a
( a1 VARCHAR2(10),
a2 VARCHAR2(10),
a3 VARCHAR2(10)
);INSERT INTO a VALUES('X','2','3');
INSERT INTO a VALUES('X','3','3');
INSERT INTO a VALUES('Y','4','3');
INSERT INTO a VALUES('Y','5','3');
INSERT INTO a VALUES('Z','5','3');
INSERT INTO a VALUES('Z','4','5');
INSERT INTO a VALUES('Z','6','3');SELECT SUM(DECODE(a1,'X',1)) AS X,
SUM(DECODE(a1,'Y',1)) AS Y,
SUM(DECODE(a1,'Z',1)) AS Z
FROM a;
CREATE TABLE a
( a1 VARCHAR2(10),
a2 VARCHAR2(10),
a3 VARCHAR2(10)
);INSERT INTO a VALUES('X','2','3');
INSERT INTO a VALUES('X','3','3');
INSERT INTO a VALUES('Y','4','3');
INSERT INTO a VALUES('Y','5','3');
INSERT INTO a VALUES('Z','5','3');
INSERT INTO a VALUES('Z','4','5');
INSERT INTO a VALUES('Z','6','3');SELECT SUM(DECODE(a1,'X',1)) AS X,
SUM(DECODE(a1,'Y',1)) AS Y,
SUM(DECODE(a1,'Z',1)) AS Z
FROM a;这个应该是可以的
select a1,count(*) from A group by a1
最好别用count(列),用count(*)
2 select 'x' A1,2 A2,3 A3 from dual
3 union all
4 select 'x' A1,3 A2,3 A3 from dual
5 union all
6 select 'y' A1,3 A2,3 A3 from dual
7 union all
8 select 'y' A1,3 A2,3 A3 from dual
9 union all
10 select 'z' A1,3 A2,3 A3 from dual
11 union all
12 select 'z' A1,4 A2,5 A3 from dual
13 union all
14 select 'z' A1,5 A2,5 A3 from dual)
15 select
16 count(case A1 when 'x' then A1 end) X,
17 count(case A1 when 'y' then A1 end) Y,
18 count(case A1 when 'z' then A1 end) Z
19 from TAB;
X Y Z
---------- ---------- ----------
2 2 3
SQL>