比如我有一个表A,表里有两个字段,num,day
num的值每天不同,day是日期,day的值不唯一,同一天有多个num我想统计每天num在0到10之间,10到100之间
同时分时间段
1号到10号,11号到20号,21号到月底
的笔数
是不是不容易明白?
1号到10号the day (一天)的sum(num)值在0-100之间的笔数sql 循环 取和
num的值每天不同,day是日期,day的值不唯一,同一天有多个num我想统计每天num在0到10之间,10到100之间
同时分时间段
1号到10号,11号到20号,21号到月底
的笔数
是不是不容易明白?
1号到10号the day (一天)的sum(num)值在0-100之间的笔数sql 循环 取和
SELECT 10 AS NUM,'2013-01-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 15 AS NUM,'2013-01-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 20 AS NUM,'2013-01-10' AS DAYSTR FROM DUAL
UNION ALL
SELECT 25 AS NUM,'2013-01-11' AS DAYSTR FROM DUAL
UNION ALL
SELECT 6 AS NUM,'2013-01-12' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-01-13' AS DAYSTR FROM DUAL
UNION ALL
SELECT 30 AS NUM,'2013-01-20' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-01-24' AS DAYSTR FROM DUAL
UNION ALL
SELECT 18 AS NUM,'2013-01-24' AS DAYSTR FROM DUAL
)
SELECT DECODE(DAYDD, 1, '1号到10号', 2, '11号到20号', 3, '21号到月底') AS DD,
COUNT(STR1) AS "0到10之間",
COUNT(STR2) AS "10到100之間"
FROM (SELECT DECODE(SIGN(CEIL(NUM / 10) - 1), 0, NUM) AS STR1,
DECODE(SIGN(CEIL(NUM / 10) - 1), 1, NUM) AS STR2,
CEIL(TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'DD') / 10) AS DAYDD
FROM TEST)
GROUP BY DAYDD
==========================================
1 1号到10号 1 2
2 11号到20号 2 2
3 21号到月底 1 1
SELECT 10 AS NUM,'2013-01-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 15 AS NUM,'2013-01-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 20 AS NUM,'2013-01-10' AS DAYSTR FROM DUAL
UNION ALL
SELECT 25 AS NUM,'2013-01-11' AS DAYSTR FROM DUAL
UNION ALL
SELECT 6 AS NUM,'2013-01-12' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-01-13' AS DAYSTR FROM DUAL
UNION ALL
SELECT 30 AS NUM,'2013-01-20' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-01-24' AS DAYSTR FROM DUAL
UNION ALL
SELECT 18 AS NUM,'2013-01-24' AS DAYSTR FROM DUAL
UNION ALL
SELECT 10 AS NUM,'2013-02-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 15 AS NUM,'2013-02-01' AS DAYSTR FROM DUAL
UNION ALL
SELECT 20 AS NUM,'2013-02-10' AS DAYSTR FROM DUAL
UNION ALL
SELECT 25 AS NUM,'2013-02-11' AS DAYSTR FROM DUAL
UNION ALL
SELECT 6 AS NUM,'2013-02-12' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-02-13' AS DAYSTR FROM DUAL
UNION ALL
SELECT 30 AS NUM,'2013-02-20' AS DAYSTR FROM DUAL
UNION ALL
SELECT 8 AS NUM,'2013-02-24' AS DAYSTR FROM DUAL
UNION ALL
SELECT 18 AS NUM,'2013-02-24' AS DAYSTR FROM DUAL
)
SELECT DECODE(DAYDD, 1, '1号到10号(' || DAYMM || '月份)', 2, '11号到20号(' || DAYMM || '月份)', 3, '21号到月底(' || DAYMM || '月份)') AS DD,
COUNT(STR1) AS "0到10之間",
COUNT(STR2) AS "10到100之間"
FROM (SELECT DECODE(SIGN(CEIL(NUM / 10) - 1), 0, NUM) AS STR1,
DECODE(SIGN(CEIL(NUM / 10) - 1), 1, NUM) AS STR2,
CEIL(TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'DD') / 10) AS DAYDD,
TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'MM') AS DAYMM
FROM TEST)
GROUP BY DAYMM,DAYDD
order by DAYMM
COUNT(STR1) AS "0到10之間",
COUNT(STR2) AS "10到100之間"
FROM (SELECT DECODE(SIGN(CEIL(NUM / 10) - 1), 0, NUM) AS STR1,
DECODE(SIGN(CEIL(NUM / 10) - 1), 1, NUM) AS STR2,
DECODE(CEIL(TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'DD') / 10),4,3,CEIL(TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'DD') / 10)) AS DAYDD,
TO_CHAR(TO_DATE(DAYSTR, 'YYYY-MM-DD'), 'MM') AS DAYMM
FROM TEST)
GROUP BY DAYMM,DAYDD
order by DAYMM