列转行

基于Oracle：

表：

期望结果：

  或  

建表：

create table AA_WW1
(
  name    VARCHAR2(10),
  result    VARCHAR2(10),
  time    DATE not null

);
insert into AA_WW1 (name,result, time) values ('湖人','胜', to_date('09-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('火箭','胜', to_date('09-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('火箭','负', to_date('09-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('火箭','负', to_date('09-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('火箭','胜', to_date('10-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('湖人','负', to_date('10-11-2017', 'dd-mm-yyyy'));
insert into AA_WW1 (name,result, time) values ('湖人','负', to_date('10-11-2017', 'dd-mm-yyyy'));

方案一：

select a.time,
SUM（CASE a.result WHEN '胜' THEN 1 ELSE 0 END）as "胜",
SUM（CASE a.result WHEN '负' THEN 1 ELSE 0 END）as "负"
from AA_WW1 a
group by a.time

或：

select a.name,
SUM（CASE a.result WHEN '胜' THEN 1 ELSE 0 END）as "胜",
SUM（CASE a.result WHEN '负' THEN 1 ELSE 0 END）as "负"
from AA_WW1 a
group by a.name

方案二：

 select a1.time, a1."胜", a2."负" from
 (select a.time ,count(a.result) as "胜" from AA_WW1 a where a.result = '胜' group by a.time) a1
 inner join 
 (select a.time ,count(a.result) as "负" from AA_WW1 a where a.result = '负' group by a.time) a2
 on a1.time = a2.time

 

还可以做多组统计：

结果：

sql:

select a.time, a.name,
SUM（CASE a.result WHEN '胜' THEN 1 ELSE 0 END）as "胜",
SUM（CASE a.result WHEN '负' THEN 1 ELSE 0 END）as "负"
from AA_WW1 a
group by a.time,a.name
order by a.time

 

结果：

sql:

select  a.name,a.time,
SUM（CASE a.result WHEN '胜' THEN 1 ELSE 0 END）as "胜",
SUM（CASE a.result WHEN '负' THEN 1 ELSE 0 END）as "负"
from AA_WW1 a
group by a.name,a.time
order by a.time