数据表:
value time
"1" "2014-01-01"
"2" "2014-01-01"
"2" "2014-01-02"
"3" "2014-01-03"
"1" "2014-01-04"
"1" "2014-01-04"sql语句:
SELECT "time" count("value") AS "num"
FROM "table"
WHERE "value" = 3
AND time >= '2014-01-01'
AND time <= '2014-01-05'
group by "time"我想返回:
num time
0 2014-01-01
0 2014-01-02
1 2014-01-03
0 2014-01-04实际返回:
num time
1 2014-01-03
我想没有聚合出值就返回一个默认值, 这个应该怎么实现
value time
"1" "2014-01-01"
"2" "2014-01-01"
"2" "2014-01-02"
"3" "2014-01-03"
"1" "2014-01-04"
"1" "2014-01-04"sql语句:
SELECT "time" count("value") AS "num"
FROM "table"
WHERE "value" = 3
AND time >= '2014-01-01'
AND time <= '2014-01-05'
group by "time"我想返回:
num time
0 2014-01-01
0 2014-01-02
1 2014-01-03
0 2014-01-04实际返回:
num time
1 2014-01-03
我想没有聚合出值就返回一个默认值, 这个应该怎么实现
FROM (SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 SDATE,
LAST_DAY(SYSDATE) EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE
左关联。。
WITH test AS
(
SELECT '1' VALUE,'2014-01-01' TIME FROM dual UNION ALL
SELECT '2','2014-01-01' FROM dual UNION ALL
SELECT '2','2014-01-02' FROM dual UNION ALL
SELECT '3','2014-01-03' FROM dual UNION ALL
SELECT '1','2014-01-04' FROM dual UNION ALL
SELECT '1','2014-01-04' FROM dual )
SELECT ddate,decode(num,NULL,0,num) FROM
(SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE
FROM (SELECT to_date('2014-01-01','yyyy-mm-dd') SDATE,
to_date('2014-01-05','yyyy-mm-dd') EDATE
FROM dual) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) t,
(SELECT time ,count(VALUE) AS num FROM test
WHERE value = 3 AND time >= '2014-01-01' AND time <= '2014-01-05' group by TIME) k WHERE t.ddate=k.TIME(+)
when 条件 then
1
else
0
end) countNum,
table.key
from table
group by table.key我把后边where的查询条件加到case里,这样可以返回0