SELECT to_char(creattime, 'yyyy-mm-dd')creattime,count(distinct id)
from network_checkin where to_char(creattime, 'yyyy-mm-dd') > '2014-11-25 15:53:11'
group by to_char(creattime, 'yyyy-mm-dd') 这是统计每一天不同时段的和,
select count(*)c,to_char(creattime, 'yyyy-mm-dd')Creat from network_checkin where to_char(creattime, 'yyyy-mm-dd')> '2014-11-25 15:53:11' and mark=2
group by to_char(creattime, 'yyyy-mm-dd')
order by to_char(creattime, 'yyyy-mm-dd')这是统计一天内不同单,单据状态的量,例如延迟 ,mark是单状态的标示
该怎样达到以下的效果呢
登记时间(天) 单数 正常(单数) 延迟(单数) 提前(单数)
2014-12-1 3 2 0 1
------解决思路----------------------
SELECT COUNT (*) 单数, TO_CHAR (creattime, 'yyyy-mm-dd') 时间,
SUM (CASE
WHEN mark = 2
THEN 1
ELSE 0
END) 正常,
SUM (CASE
WHEN mark = 0
THEN 1
ELSE 0
END) 延迟,
SUM (CASE
WHEN mark = 1
THEN 1
ELSE 0
END) 提前
FROM network_checkin
WHERE TO_CHAR (creattime, 'yyyy-mm-dd') > '2014-11-25 15:53:11'
GROUP BY TO_CHAR (creattime, 'yyyy-mm-dd')
ORDER BY TO_CHAR (creattime, 'yyyy-mm-dd')
------解决思路----------------------
1#是一种各数据库较通用的写法,oracle中可以使用sum(decode())的写法,具体用法可百度下,范例蛮多的