看你是什么数据库了,给你个informix数据库的: select thedate,sum(case when result='胜' then 1 else 0),sum(case when result='胜' then 0 else 1) from mytable group by thedate
select date ,a.num1,b.num2 from t,(select count(*) as a from t where status='胜' and date=t.date) a ,(select count(*) as a from t where status='负' and date=t.date) b where date in (select date from t group by date);
--create table t1(date varchar(10),score varchar(2)) select date,sum(case score when '胜' then 1 else 0 end) as '胜',sum(case score when '负' then 1 else 0 end) as '负' from t1 group by date
select date,A.cntS,B.cntF from ( select date,count(flag) as cntS from table where flag = '1' group by date )A ( select date,count(flag) as cntF from table where flag = '0' group by date )B where A.date = B.date
select date,sum(case score when '胜' then 1 else 0 end) as '胜',sum(case score when '负' then 1 else 0 end) as '负' from t1 group by date
select 日期,胜负 from 表 group by 日期,胜负 order by 日期,胜负 这样不就完了么
oracle的select date,sum(decode(score,'胜',1,0)) as '胜',sum(decode(score,'负',1,0) ) as '负' from t1 group by date
-- -- 表的结构 `datewin` --CREATE TABLE IF NOT EXISTS `datewin` ( `date` date NOT NULL, `result` varchar(10) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;-- -- 导出表中的数据 `datewin` --INSERT INTO `datewin` (`date`, `result`) VALUES ('2011-03-29', '胜'), ('2011-03-29', '胜'), ('2011-03-29', '负'), ('2011-03-29', '负'), ('2011-03-30', '胜'), ('2011-03-30', '负'), ('2011-03-30', '负');select date,sum(case result when '胜' then 1 else 0 end) as '胜',sum(case result when '负' then 1 else 0 end) as '负' from datewin group by date
select thedate,sum(case when result='胜' then 1 else 0),sum(case when result='胜' then 0 else 1) from mytable group by thedate
--create table t1(date varchar(10),score varchar(2))
select date,sum(case score when '胜' then 1 else 0 end) as '胜',sum(case score when '负' then 1 else 0 end) as '负' from t1 group by date
date,A.cntS,B.cntF
from
(
select date,count(flag) as cntS from table where flag = '1' group by date
)A
(
select date,count(flag) as cntF from table where flag = '0' group by date
)B
where
A.date = B.date
这样不就完了么
-- 表的结构 `datewin`
--CREATE TABLE IF NOT EXISTS `datewin` (
`date` date NOT NULL,
`result` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 导出表中的数据 `datewin`
--INSERT INTO `datewin` (`date`, `result`) VALUES
('2011-03-29', '胜'),
('2011-03-29', '胜'),
('2011-03-29', '负'),
('2011-03-29', '负'),
('2011-03-30', '胜'),
('2011-03-30', '负'),
('2011-03-30', '负');select date,sum(case result when '胜' then 1 else 0 end) as '胜',sum(case result when '负' then 1 else 0 end) as '负' from datewin group by date
用group by就OK了嘛
16楼OK的~