哦,还有个判断select count(*) from (select sum(fenshu) as sumfenshu where a.id=b.id and (一个月内的判断,不好意思忘了怎么写) and sumfenshu >100 group by a.id )sumfenshu>100 都忘了.....
select count( id ) from a where 日期 >= to_date( '2004-01-01' , 'yyyy-mm-dd') and 日期 <= to_date( '2004-02-01' , 'yyyy-mm-dd' ) group by a.id having sum( 分数 ) > 100
select count(*) from (select sum(fenshu) as sumfenshu 【from a,b 】where a.id=b.id and (一个月内的判断,不好意思忘了怎么写) and sumfenshu >100 group by a.id ) 中间忘了from a,b,不好意思啊........
***************************************************************** ****************有两个SQL都可以:均测试通过********************** ***************************************************************** 注:1、这个统计和B表没有关系,直接统计A表就可以了。 2、开始日期和结束日期你要另外得到。SQL1:SELECT COUNT(*) FROM ( SELECT A.Id, SUM(A.分数) S FROM A WHERE To_Char(A.日期,'YYYY-MM-DD') >= '2004-04-01' AND To_Char(A.日期,'YYYY-MM-DD') <= '2004-05-01' GROUP BY A.Id ) WHERE S>100 *****************************************************************SQL2:SELECT COUNT(*) FROM ( SELECT A.Id, SUM(A.分数) FROM Your_Tab A WHERE To_Char(A.日期,'YYYY-MM-DD') >= '2004-04-01' AND To_Char(A.日期,'YYYY-MM-DD') <= '2004-05-01' GROUP BY A.Id HAVING SUM(A.分数) > 100 )
同意prettylife(风过留痕)的写法!
select sum(分数),a.id from a,b where a.id=b.id and 日期=... group by a.id having sum(分数)>100
--取得这个结果好像用不上b表 select count(a.id) 总数 from ( select sum(分数),a.id id from a where 日期 between ... and ... group by a.id having sum(分数)>100) a;
呵呵,楼上说的是, select sum(分数) ,a.id from a表 where to_char(日期,'yyyy-mm-dd')>=..and to_char(日期,'yyyy-mm-dd')<=.... group by a.id having sum(分数)>100
SELECT COUNT(*) FROM (SELECT SUM(A.分) TOTAL FROM A WHERE A.日期 BETWEEN minDate AND maxDate GROUP BY A.Id HAVING TOTAL>100);
select sum(分数),a.id from a,b where a.id=b.id and To_date('2004-07-01','YYYY-MM-DD') >= BeginDate and To_date('2004-08-01','YYYY-MM-DD') <=EndDate group by a.id having sum(分数)>100;
中间忘了from a,b,不好意思啊........
****************有两个SQL都可以:均测试通过**********************
*****************************************************************
注:1、这个统计和B表没有关系,直接统计A表就可以了。
2、开始日期和结束日期你要另外得到。SQL1:SELECT COUNT(*)
FROM
(
SELECT A.Id, SUM(A.分数) S
FROM A
WHERE To_Char(A.日期,'YYYY-MM-DD') >= '2004-04-01'
AND To_Char(A.日期,'YYYY-MM-DD') <= '2004-05-01'
GROUP BY A.Id
)
WHERE S>100
*****************************************************************SQL2:SELECT COUNT(*)
FROM
(
SELECT A.Id, SUM(A.分数)
FROM Your_Tab A
WHERE To_Char(A.日期,'YYYY-MM-DD') >= '2004-04-01'
AND To_Char(A.日期,'YYYY-MM-DD') <= '2004-05-01'
GROUP BY A.Id HAVING SUM(A.分数) > 100
)
select count(a.id) 总数 from ( select sum(分数),a.id id from a where 日期 between ... and ... group by a.id having sum(分数)>100) a;
select sum(分数) ,a.id from a表 where to_char(日期,'yyyy-mm-dd')>=..and to_char(日期,'yyyy-mm-dd')<=.... group by a.id having sum(分数)>100
FROM (SELECT SUM(A.分) TOTAL
FROM A
WHERE A.日期 BETWEEN minDate AND maxDate
GROUP BY A.Id
HAVING TOTAL>100);