with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi'); 高手啊,膜拜
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方 人家也是给你个实例啊,靠
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方 跟数据的行数有区别吗,你把我的临时数据换成你的表不就行了吗?
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方 人家也是给你个实例啊,靠这个方法不实用,我一万行数据,那我要union all一万个语句吗?
可能我的写法让大家误解了,抱歉table表的date字段的值都是20131205 date time 20131205 090010 20131205 090035 20131205 090134 20131205 090155 20131205 090213 20131205 090244
with test as (select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col from dual union all select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual) select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方 人家也是给你个实例啊,靠这个方法不实用,我一万行数据,那我要union all一万个语句吗? 你真不懂?你表里有数据了你还UNION干嘛,我的UNION只是作为临时数据,不用建表,因为我没有你的数据。 如果你有了表,直接 select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi'); OK???
我直接跟你写能运行的记过吧。如果你的date字段值都是20131205就更简单。我把字段当varchar来处理。 假设表名为TEST SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date
我直接跟你写能运行的记过吧。如果你的date字段值都是20131205就更简单。我把字段当varchar来处理。 假设表名为TEST SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date谢谢,是我理解错了,不好意思。 非常感谢大家。
我直接跟你写能运行的记过吧。如果你的date字段值都是20131205就更简单。我把字段当varchar来处理。 假设表名为TEST SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date谢谢,是我理解错了,不好意思。 非常感谢大家。 万事开头难,加油。
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');
高手啊,膜拜
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
人家也是给你个实例啊,靠
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
跟数据的行数有区别吗,你把我的临时数据换成你的表不就行了吗?
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
人家也是给你个实例啊,靠这个方法不实用,我一万行数据,那我要union all一万个语句吗?
date time
20131205 090010
20131205 090035
20131205 090134
20131205 090155
20131205 090213
20131205 090244
(select to_date('20131205 090010', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090035', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090134', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090155', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090213', 'yyyymmdd hh24:mi:ss') col
from dual
union all
select to_date('20131205 090244', 'yyyymmdd hh24:mi:ss') col from dual)
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi');数据有很多行,我打了个比方
人家也是给你个实例啊,靠这个方法不实用,我一万行数据,那我要union all一万个语句吗?
你真不懂?你表里有数据了你还UNION干嘛,我的UNION只是作为临时数据,不用建表,因为我没有你的数据。
如果你有了表,直接
select max(col) from test group by to_char(col, 'yyyymmdd hh24:mi'); OK???
假设表名为TEST
SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date
假设表名为TEST
SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date谢谢,是我理解错了,不好意思。 非常感谢大家。
假设表名为TEST
SELECT date,max(time) from TEST T GROUP BY SUBSTR(TIME,1,4),date谢谢,是我理解错了,不好意思。 非常感谢大家。
万事开头难,加油。