//建表及插入数据
create table match2(team_a varchar(5),team_b varchar(5),result_a varchar(5),result_b varchar(5));
insert into match2 values('A','B','WIN','LOSE');//表示A WIN,B LOSE
insert into match2 values('A','C','LOSE','WIN');
insert into match2 values('A','D','LOSE','WIN');
insert into match2 values('A','E','LOSE','WIN');
insert into match2 values('B','C','WIN','LOSE');
insert into match2 values('B','D','LOSE','WIN');
insert into match2 values('B','E','WIN','LOSE');
insert into match2 values('C','D','LOSE','WIN');
insert into match2 values('C','E','LOSE','WIN');
insert into match2 values('D','E','WIN','LOSE');//查询一:
select a1.team_name TEAM_NAME,
sum(case when a1.result='WIN' then 1 else 0 end) WIN,
sum(case when a1.result='LOSE' then 1 else 0 end) LOSE
FROM ((select team_a team_name,result_a result from match) union all(select team_b team_name,result_b result from match)) a1 group by a1.team_name;
//查询一:结果:
TEAM_NAME WIN LOSE
---------- ---------- ----------
D 4 0
A 1 3
B 2 2
C 1 3
E 2 2//查询二:
select a1.result result,
sum(case when a1.team_name='A' then 1 else 0 end) A,
sum(case when a1.team_name='B' then 1 else 0 end) B,
sum(case when a1.team_name='C' then 1 else 0 end) C,
sum(case when a1.team_name='D' then 1 else 0 end) D,
sum(case when a1.team_name='E' then 1 else 0 end) E,
FROM ((select result_a result,team_a team_name from match) union all(select result_b result,team_b team_name from match)) a1 group by a1.result;//查询二:结果:
SQL> select a1.result result,
2 sum(case when a1.team_name='A' then 1 else 0 end) A,
3 sum(case when a1.team_name='B' then 1 else 0 end) B,
4 sum(case when a1.team_name='C' then 1 else 0 end) C,
5 sum(case when a1.team_name='D' then 1 else 0 end) D,
6 sum(case when a1.team_name='E' then 1 else 0 end) E,
7 FROM ((select result_a result,team_a team_name from match) union all(se
lect result_b result,team_b team_name from match)) a1 group by a1.result;
FROM ((select result_a result,team_a team_name from match) union all(select
result_b result,team_b team_name from match)) a1 group by a1.result
*
第 7 行出现错误:
ORA-00936: 缺失表达式提问:查询二出现这样的错误的原因是什么呢??为什么查询一的语句可以通过,而二的不行?知道的回帖下,非常感谢哈。
以下是我想得到的结果:
RESULT A B C D E
-------- ------ ------ ------ ------ ------
WIN 1 2 1 4 2
LOSE 3 2 3 0 2
create table match2(team_a varchar(5),team_b varchar(5),result_a varchar(5),result_b varchar(5));
insert into match2 values('A','B','WIN','LOSE');//表示A WIN,B LOSE
insert into match2 values('A','C','LOSE','WIN');
insert into match2 values('A','D','LOSE','WIN');
insert into match2 values('A','E','LOSE','WIN');
insert into match2 values('B','C','WIN','LOSE');
insert into match2 values('B','D','LOSE','WIN');
insert into match2 values('B','E','WIN','LOSE');
insert into match2 values('C','D','LOSE','WIN');
insert into match2 values('C','E','LOSE','WIN');
insert into match2 values('D','E','WIN','LOSE');//查询一:
select a1.team_name TEAM_NAME,
sum(case when a1.result='WIN' then 1 else 0 end) WIN,
sum(case when a1.result='LOSE' then 1 else 0 end) LOSE
FROM ((select team_a team_name,result_a result from match) union all(select team_b team_name,result_b result from match)) a1 group by a1.team_name;
//查询一:结果:
TEAM_NAME WIN LOSE
---------- ---------- ----------
D 4 0
A 1 3
B 2 2
C 1 3
E 2 2//查询二:
select a1.result result,
sum(case when a1.team_name='A' then 1 else 0 end) A,
sum(case when a1.team_name='B' then 1 else 0 end) B,
sum(case when a1.team_name='C' then 1 else 0 end) C,
sum(case when a1.team_name='D' then 1 else 0 end) D,
sum(case when a1.team_name='E' then 1 else 0 end) E,
FROM ((select result_a result,team_a team_name from match) union all(select result_b result,team_b team_name from match)) a1 group by a1.result;//查询二:结果:
SQL> select a1.result result,
2 sum(case when a1.team_name='A' then 1 else 0 end) A,
3 sum(case when a1.team_name='B' then 1 else 0 end) B,
4 sum(case when a1.team_name='C' then 1 else 0 end) C,
5 sum(case when a1.team_name='D' then 1 else 0 end) D,
6 sum(case when a1.team_name='E' then 1 else 0 end) E,
7 FROM ((select result_a result,team_a team_name from match) union all(se
lect result_b result,team_b team_name from match)) a1 group by a1.result;
FROM ((select result_a result,team_a team_name from match) union all(select
result_b result,team_b team_name from match)) a1 group by a1.result
*
第 7 行出现错误:
ORA-00936: 缺失表达式提问:查询二出现这样的错误的原因是什么呢??为什么查询一的语句可以通过,而二的不行?知道的回帖下,非常感谢哈。
以下是我想得到的结果:
RESULT A B C D E
-------- ------ ------ ------ ------ ------
WIN 1 2 1 4 2
LOSE 3 2 3 0 2
解决方案 »
- 求助select报ORA-01036: illegal variable name/number
- (10g)1000万数据的表做全表更新后非常慢,我直接重新启动服务后ORACLE.EXE依然占用很大的内存,高手帮忙
- sql实现下列操作
- 请教大家一般如何处理存放请求这类的表?
- VC.NET 2005 下面,如何添加ORACLE 10g的orasqx10.lib到工程?
- oracle9i支持Itanium2芯片吗?
- sql中语句能否实现记录连乘的功能?
- 为何安装程序无法启动,请多指教
- oracle 大数据导入
- oracle 11g 透明网关,请大大指点,查询时报错ora-28545
- 关于存储过程参数
- windows server 2008 R2 x64(64位)系统下安装Oracle10g,安装窗口消失
sum(case
when a1.team_name = 'A' then
1
else
0
end) A,
sum(case
when a1.team_name = 'B' then
1
else
0
end) B,
sum(case
when a1.team_name = 'C' then
1
else
0
end) C,
sum(case
when a1.team_name = 'D' then
1
else
0
end) D,
sum(case
when a1.team_name = 'E' then
1
else
0
end) E
FROM ((select result_a result, team_a team_name from match) union all
(select result_b result, team_b team_name from match)) a1
group by a1.result;第二个SQL,这样改就没问题了。
2 sum(case
3 when a1.team_name = 'A' then
4 1
5 else
6 0
7 end) A,
8 sum(case
9 when a1.team_name = 'B' then
10 1
11 else
12 0
13 end) B,
14 sum(case
15 when a1.team_name = 'C' then
16 1
17 else
18 0
19 end) C,
20 sum(case
21 when a1.team_name = 'D' then
22 1
23 else
24 0
25 end) D,
26 sum(case
27 when a1.team_name = 'E' then
28 1
29 else
30 0
31 end) E
32 FROM ((select result_a result, team_a team_name from match) union all
33 (select result_b result, team_b team_name from match)) a1
34 group by a1.result;RESULT A B C D E
------ ---------- ---------- ---------- ---------- ----------
WIN 1 2 1 4 2
LOSE 3 2 3 0 2SQL>
select a1.result result,
sum(case
when a1.team_name = 'A' then
1
else
0
end) A,
sum(case
when a1.team_name = 'B' then
1
else
0
end) B,
sum(case
when a1.team_name = 'C' then
1
else
0
end) C,
sum(case
when a1.team_name = 'D' then
1
else
0
end) D,
sum(case
when a1.team_name = 'E' then
1
else
0
end) E --这里你多了一个逗号
FROM (select result_a result, team_a team_name from match2) a1
group by a1.result;
RESULT A B C D E
------ ---------- ---------- ---------- ---------- ----------
WIN 1 2 0 1 0
LOSE 3 1 2 0 0