数据库表结构如下:挂号表:guahaoID name guahaoshijian
1 zhen 4/13/2010 4:19:23
2 zhen 4/13/2010 4:19:23
3 zhen 4/13/2010 14:19:23
4 zhen 4/13/2010 14:19:23
5 zhen 4/13/2010 14:19:23也就是说上午有2条记录,下午有3条记录select name as 姓名,count(id) as 上午次数 from guahao where guahaoshijian>to_date('2010-4-13 00:00:00','yyyy-mm-dd hh24:mi:ss') and guahaoshijian<to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss') group by name;这样的结果是:
姓名 上午次数
zhen 2我想要的结果是:
姓名 上午次数 下午次数
zhen 2 3请问高手们,应该怎么写?谢谢
在线等!
1 zhen 4/13/2010 4:19:23
2 zhen 4/13/2010 4:19:23
3 zhen 4/13/2010 14:19:23
4 zhen 4/13/2010 14:19:23
5 zhen 4/13/2010 14:19:23也就是说上午有2条记录,下午有3条记录select name as 姓名,count(id) as 上午次数 from guahao where guahaoshijian>to_date('2010-4-13 00:00:00','yyyy-mm-dd hh24:mi:ss') and guahaoshijian<to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss') group by name;这样的结果是:
姓名 上午次数
zhen 2我想要的结果是:
姓名 上午次数 下午次数
zhen 2 3请问高手们,应该怎么写?谢谢
在线等!
and to_date('2010-4-13 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
then id else null end) as 上午次数,
count(case when guahaoshijian between to_date('2010-4-13 12:00:01', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2010-4-13 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
then id else null end) as 下午次数
from guahao
where trunc(guahaoshijian) = to_date('2010-4-13 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by name;
a.guahaoshijian>to_date('2010-4-13 00:00:00','yyyy-mm-dd hh24:mi:ss') and a.guahaoshijian<to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss') and b.guahaoshijian>=to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss') and b.guahaoshijian<to_date('2010-4-14 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by a.name;
select 1 id,'zhen' name,to_date('4/13/2010 4:19:23','mm/dd/yyyy hh24:mi:ss') guahaoshijian from dual
union all
select 2 id,'zhen' name,to_date('4/13/2010 4:19:23','mm/dd/yyyy hh24:mi:ss') guahaoshijian from dual
union all
select 3 id,'zhen' name,to_date('4/13/2010 14:19:23','mm/dd/yyyy hh24:mi:ss') guahaoshijian from dual
union all
select 4 id,'zhen' name,to_date('4/13/2010 14:19:23','mm/dd/yyyy hh24:mi:ss') guahaoshijian from dual
union all
select 5 id,'zhen' name,to_date('4/13/2010 14:19:23','mm/dd/yyyy hh24:mi:ss') guahaoshijian from dual
)
select 姓名,sum(上午次数) 上午次数,sum(下午次数) 下午次数 from(
select name as 姓名,case when (guahaoshijian>to_date('2010-4-13 00:00:00','yyyy-mm-dd hh24:mi:ss')
and guahaoshijian<to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss')) then count(id) end 上午次数,
case when (guahaoshijian>to_date('2010-4-13 12:00:00','yyyy-mm-dd hh24:mi:ss')) then count(id) end 下午次数
from guahao group by name,guahaoshijian
) group by 姓名