with t1 as ( select '2009' a,'胜' b from dual union all select '2009' a,'败' b from dual union all select '2009' a,'胜' b from dual union all select '2008' a,'胜' b from dual union all select '2008' a,'败' b from dual ) select a 年份,sum(decode(b,'胜',1,0)) 胜,sum(decode(b,'败',1,0)) 败 from t1 group by a 年份 胜 败 ---------------------------- 1 2009 2 1 2 2008 1 1
CREATE TABLE T169 ( YEAR VARCHAR2(10), RESULT VARCHAR2(2) );-- INSERT INTO T169 VALUES('2009', '胜'); INSERT INTO T169 VALUES('2009', '败'); INSERT INTO T169 VALUES('2009', '胜'); INSERT INTO T169 VALUES('2008', '败'); INSERT INTO T169 VALUES('2008', '胜');方法一: SELECT YEAR, (SELECT COUNT(RESULT) FROM T169 c WHERE RESULT='胜' AND t.year =c.year)胜 , (SELECT COUNT( RESULT) FROM T169 c WHERE RESULT='败' AND t.year =c.year)败 FROM T169 t GROUP BY YEAR;方法二: WITH cte AS ( SELECT YEAR,CASE RESULT WHEN '胜' THEN 1 WHEN '败'THEN 0 END res FROM T169 ) SELECT YEAR,(SELECT COUNT(res) FROM cte c WHERE res>0 AND t.year =c.year),(SELECT COUNT( res) FROM cte c WHERE res=0 AND t.year =c.year) FROM cte t GROUP BY YEAR;
如果这两年有几千场比赛的话,是不是要写几千个‘select'。。
SELECT year,SUM(Decode(flag,'胜',1,0)),SUM(Decode(flag,'败',1,0)) FROM TEST GROUP BY YEAR
with t1 as
(
select '2009' a,'胜' b from dual union all
select '2009' a,'败' b from dual union all
select '2009' a,'胜' b from dual union all
select '2008' a,'胜' b from dual union all
select '2008' a,'败' b from dual
)
select a 年份,sum(decode(b,'胜',1,0)) 胜,sum(decode(b,'败',1,0)) 败
from t1
group by a 年份 胜 败
----------------------------
1 2009 2 1
2 2008 1 1
(
YEAR VARCHAR2(10),
RESULT VARCHAR2(2)
);--
INSERT INTO T169 VALUES('2009', '胜');
INSERT INTO T169 VALUES('2009', '败');
INSERT INTO T169 VALUES('2009', '胜');
INSERT INTO T169 VALUES('2008', '败');
INSERT INTO T169 VALUES('2008', '胜');方法一:
SELECT YEAR,
(SELECT COUNT(RESULT) FROM T169 c WHERE RESULT='胜' AND t.year =c.year)胜 ,
(SELECT COUNT( RESULT) FROM T169 c WHERE RESULT='败' AND t.year =c.year)败
FROM T169 t
GROUP BY YEAR;方法二:
WITH cte
AS
(
SELECT YEAR,CASE RESULT WHEN '胜' THEN 1 WHEN '败'THEN 0 END res
FROM T169
)
SELECT YEAR,(SELECT COUNT(res) FROM cte c WHERE res>0 AND t.year =c.year),(SELECT COUNT( res) FROM cte c WHERE res=0 AND t.year =c.year)
FROM cte t
GROUP BY YEAR;
GROUP BY YEAR