ORACLE数据库查询,求数据库高手进(100分)

  • 主题发起人 主题发起人 sdtrm
  • 开始时间 开始时间
S

sdtrm

Unregistered / Unconfirmed
GUEST, unregistred user!
我有一张表,如下结构...<br>id &nbsp;name time<br>1 &nbsp; a &nbsp; &nbsp;2008-9-5 0:02:02<br>2 &nbsp; b &nbsp; &nbsp;2008-9-6 1:01:01<br>3 &nbsp; c &nbsp; &nbsp;2008-9-7 1:01:01<br>4 &nbsp; d &nbsp; &nbsp;2008-9-5 1:01:01<br>5 &nbsp; a &nbsp; &nbsp;2008-9-5 0:02:01<br>6 &nbsp; e &nbsp; &nbsp;2008-9-5 1:02:01<br><br>想得出两种结果<br>一个为按日期对name进行分组,计算出每一天内name出现的次数<br>name &nbsp; namecount &nbsp;time<br>a &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5<br>b &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-6 <br>c &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-7 <br>d &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5 <br>e &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5<br>一个为按小时对name进行分组,计算出一天内每一小时name出现的次数<br>name &nbsp; namecount &nbsp;time<br>a &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5 0<br>d &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5 1<br>e &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-9-5 1
 
按日期分组<br>SELECT t.name,count(t.name) as namecount &nbsp;,trunc(t.time) as time<br>FROM SYSTEM.tablename t<br>GROUP BY t.name, trunc(t.time)<br>按小时分组<br>SELECT t.name,count(t.name) as namecount &nbsp;,to_char(t.time,'HH24') as time<br>FROM SYSTEM.tablename t<br>WHERE Trunc(t.time) = to_date('2008-9-5','yyyy-mm-dd')<br>GROUP BY t.hphm, to_char(t.time,'HH24')
 
后退
顶部