//建表及插入数据
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
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