1.表test数据如图:
[img]http://dl2.iteye.com/upload/attachment/0086/5626/a5b01b71-470b-3f56-9126-c872ff34858d.jpg[/img]
2.要求显示如下:
[img]http://dl2.iteye.com/upload/attachment/0086/5628/505d6aff-de65-31ba-b099-168d58e65df9.jpg[/img]
测试:
--实现sql代码片段:
[color=green]SELECT tdate,SUM(CASE WHEN status=0 THEN 1 ELSE 0 END) type_0,
SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) type_1 FROM test GROUP BY tdate ;[/color]
[img]http://dl2.iteye.com/upload/attachment/0086/5626/a5b01b71-470b-3f56-9126-c872ff34858d.jpg[/img]
2.要求显示如下:
[img]http://dl2.iteye.com/upload/attachment/0086/5628/505d6aff-de65-31ba-b099-168d58e65df9.jpg[/img]
测试:
--创建表test
CREATE TABLE test(
tid NUMBER NOT NULL,
tdate DATE,
status NUMBER
);
--插入测试数据
INSERT INTO test(tid,tdate,status) VALUES(1,to_date('2011-01-01','yyyy-mm-dd'),1);
INSERT INTO test(tid,tdate,status) VALUES(2,to_date('2011-01-01','yyyy-mm-dd'),0);
INSERT INTO test(tid,tdate,status) VALUES(3,to_date('2011-01-01','yyyy-mm-dd'),1);
INSERT INTO test(tid,tdate,status) VALUES(4,to_date('2011-01-01','yyyy-mm-dd'),0);
INSERT INTO test(tid,tdate,status) VALUES(5,to_date('2012-02-02','yyyy-mm-dd'),1);
INSERT INTO test(tid,tdate,status) VALUES(6,to_date('2012-02-02','yyyy-mm-dd'),0);
INSERT INTO test(tid,tdate,status) VALUES(7,to_date('2012-02-02','yyyy-mm-dd'),1);
--实现sql代码片段:
[color=green]SELECT tdate,SUM(CASE WHEN status=0 THEN 1 ELSE 0 END) type_0,
SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) type_1 FROM test GROUP BY tdate ;[/color]