有一张表如下
id sj value
1 2011-3-20 18:30:00 76.2
2 2011-3-25 18:30:00 70.2
3 2011-3-25 11:30:11 50.2
4 2011-3-26 18:30:00 40.2
5 2011-3-26 15:30:00 76.2
6 2011-3-26 12:30:00 79.2 想要的结果如下: id sj value
1 2011-3-20 18:30:00 76.2
2 2011-3-25 18:30:00 70.2
3 2011-3-26 12:30:00 50.2 对SQL进行分组,我的代码如下,不对
select distinct max(t.coiltemp1) from ufde_byqonl_rzwd t group by extract(day from t.acquisitiontime)
哪位同学能指导下.
id sj value
1 2011-3-20 18:30:00 76.2
2 2011-3-25 18:30:00 70.2
3 2011-3-25 11:30:11 50.2
4 2011-3-26 18:30:00 40.2
5 2011-3-26 15:30:00 76.2
6 2011-3-26 12:30:00 79.2 想要的结果如下: id sj value
1 2011-3-20 18:30:00 76.2
2 2011-3-25 18:30:00 70.2
3 2011-3-26 12:30:00 50.2 对SQL进行分组,我的代码如下,不对
select distinct max(t.coiltemp1) from ufde_byqonl_rzwd t group by extract(day from t.acquisitiontime)
哪位同学能指导下.
with table_a as
(
select 1 id,to_date('2011-3-20 18:30:00','yyyy-mm-dd hh24:mi:ss') sj,76.2 value from dual
union
select 2 id,to_date('2011-3-25 18:30:00','yyyy-mm-dd hh24:mi:ss') sj,70.2 value from dual
union
select 3 id,to_date('2011-3-25 11:30:11','yyyy-mm-dd hh24:mi:ss') sj,50.2 value from dual
union
select 4 id,to_date('2011-3-26 18:30:00','yyyy-mm-dd hh24:mi:ss') sj,40.2 value from dual
union
select 5 id,to_date('2011-3-26 15:30:00','yyyy-mm-dd hh24:mi:ss') sj,76.2 value from dual
union
select 6 id,to_date('2011-3-26 12:30:00','yyyy-mm-dd hh24:mi:ss') sj,79.2 value from dual
)
select max(t.value),trunc(t.sj,'dd')
from table_a t
group by trunc(t.sj,'dd')
select max(value),trunc(sj) from table group by trunc(sj)
select t.id ,t.sj, max(t.value) from ufde_byqonl_rzwd t group by t.sj
trunc()函数的详细解说,哪位请帮忙解释下.
其具体的语法格式如下:
TRUNC(date[, fmt])Date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去下面是该函数的使用情况:
Select Trunc(Sysdate, 'DD') Datetime From DualDateTime
---------------
2011-04-06
Select Trunc(Sysdate, 'DD') Datetime From Dual
中DD 是不是 yyyy-mm-dd中的 dd呢