表结构是这样的:ID DealDate Result Comments
1 2011-10-02 Y
2 2011-10-02 N reject
3 2011-10-02 Y
4 2011-10-02 N reject
5 2011-10-02 N reject
6 2011-10-02 Y
7 2011-10-03 N reject
8 2011-10-03 Y
9 2011-10-04 Y
10 2011-10-05 N reject
11 2011-10-05 Y 想在想用一条查询语句,统计出在2011-10-02这一天里总的条目数,Y的数目,N的数目.
结果集包含的字段是:DealDate,Y_Qty,N_Qty.高手们,前辈们,拜托了。
1 2011-10-02 Y
2 2011-10-02 N reject
3 2011-10-02 Y
4 2011-10-02 N reject
5 2011-10-02 N reject
6 2011-10-02 Y
7 2011-10-03 N reject
8 2011-10-03 Y
9 2011-10-04 Y
10 2011-10-05 N reject
11 2011-10-05 Y 想在想用一条查询语句,统计出在2011-10-02这一天里总的条目数,Y的数目,N的数目.
结果集包含的字段是:DealDate,Y_Qty,N_Qty.高手们,前辈们,拜托了。
sum(decode(Result,'Y',1,0))Y_Qty,
sum(decode(Result,'N',1,0))N_Qty
from tb
where DealDate='2011-10-02'
group by DealDate
select c.dealdate,sum(c.Ycnt),sum(c.Ncnt),sum(c.cnt)
(select b.dealdate,decode(a.result,'Y',b.cnt,0) Ycnt,
decode(a.result,'N',b.cnt,0) Ncnt,
b.cnt
(select a.dealdate,a.result,count(1) cnt
from table_test a
group by a.dealdate,a.result) b) c
group by c.dealdate
decode函数用处很多 如果Result值为Y则转换成1相加反之转换成0
(
ID NUMBER(4),
DealDate DATE,
RESULT VARCHAR2(20),
Comments VARCHAR2(20)
);INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'Y', NULL);
INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'N', 'Reject');
INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'Y', NULL);
INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'N', 'Reject');
INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'N', 'Reject');
INSERT INTO T82 VALUES(1, to_date('2011-10-02', 'YYYY-MM-DD'), 'Y', NULL);
INSERT INTO T82 VALUES(1, to_date('2011-10-03', 'YYYY-MM-DD'), 'N', 'Reject');
INSERT INTO T82 VALUES(1, to_date('2011-10-03', 'YYYY-MM-DD'), 'Y', NULL);
INSERT INTO T82 VALUES(1, to_date('2011-10-04', 'YYYY-MM-DD'), 'Y', NULL);
INSERT INTO T82 VALUES(1, to_date('2011-10-05', 'YYYY-MM-DD'), 'N', 'Reject');
INSERT INTO T82 VALUES(1, to_date('2011-10-05', 'YYYY-MM-DD'), 'Y', NULL);实测结果:
[code=SQL]
select DealDate,
(select count(Result) from tb1 where Result='Y' and DealDate='2011-10-02') Y_Qty,
(select count(Result) from tb1 where Result='N' and DealDate='2011-10-02') N_Qty
from tb1
where DealDate='2011-10-02'
[/code
select DealDate,
(select count(Result) from tb1 where Result='Y' and DealDate='2011-10-02') Y_Qty,
(select count(Result) from tb1 where Result='N' and DealDate='2011-10-02') N_Qty
from tb1
where DealDate='2011-10-02'
( id number,
deladate varchar2(20),
result varchar2(2),
comments varchar2(22)
)
insert into testa values(1,'2011-10-02','Y',null);
insert into testa values(2,'2011-10-02','N','rejects');
insert into testa values(3,'2011-10-02','Y',null);
insert into testa values(4,'2011-10-02','N',null);
insert into testa values(5,'2011-10-02','N','rejects');
insert into testa values(6,'2011-10-02','Y',null);
insert into testa values(7,'2011-10-03','N','rejects');
insert into testa values(8,'2011-10-03','Y',null);
insert into testa values(9,'2011-10-04','Y',null);
insert into testa values(10,'2011-10-05','N','rejects');
insert into testa values(11,'2011-10-05','Y',null);
select deladate,count(*) as 条目数 ,sum(decode(result,'Y',1,0)) as Y的数目, sum(decode(result,'N',1,0)) as Y的数目
from testa
where deladate = '2011-10-02'
group by deladate;