好奇怪的式样要求哦。 with TEST as ( select 'A02' AS str,'2013-1-11' as str2 FROM DUAL UNION ALL select 'A01' AS str,'2013-1-10' as str2 FROM DUAL UNION ALL select 'A03' AS str,'2013-1-12' as str2 FROM DUAL ) select row_number() over(partition BY DY, DM, DD ORDER BY str2) AS RN, STR, STR2 FROM (select to_char(to_date(str2, 'YYYY-MM-DD'), 'YYYY') AS DY, to_char(to_date(str2, 'YYYY-MM-DD'), 'MM') AS DM, to_char(to_date(str2, 'YYYY-MM-DD'), 'DD') AS DD, STR, STR2 FROM TEST)
select id, username, password, putdate from 表 group by putdate order by putdate asc
不知道你想对哪个元素分组,目测,ID, USENAME, PWD都不好分组,唯有日期可以分组。 不知道分组后需要什么信息。另外排序只要order by putdate asc就可以了。
你是想要这样吗? select to_char(t.putdate, 'yyyy') as year, to_char(t.putdate, 'mm') as month, to_char(t.putdate, 'dd') as day from testtable t group by to_char(t.putdate, 'yyyy'), to_char(t.putdate, 'mm'), to_char(t.putdate, 'dd') order by day
为什么不用month等时间函数把信息提出来,而用to_char函数??时间函数效率要差?
select extract(year from to_date('20140204','yyyymmdd')) "年",extract(month from to_date('20140204','yyyymmdd')) "月",extract(day from to_date('20140204','yyyymmdd')) "日" from dual;
with TEST as (
select 'A02' AS str,'2013-1-11' as str2 FROM DUAL
UNION ALL
select 'A01' AS str,'2013-1-10' as str2 FROM DUAL
UNION ALL
select 'A03' AS str,'2013-1-12' as str2 FROM DUAL
)
select row_number() over(partition BY DY, DM, DD ORDER BY str2) AS RN,
STR,
STR2
FROM (select to_char(to_date(str2, 'YYYY-MM-DD'), 'YYYY') AS DY,
to_char(to_date(str2, 'YYYY-MM-DD'), 'MM') AS DM,
to_char(to_date(str2, 'YYYY-MM-DD'), 'DD') AS DD,
STR,
STR2
FROM TEST)
group by putdate
order by putdate asc
不知道分组后需要什么信息。另外排序只要order by putdate asc就可以了。
select to_char(t.putdate, 'yyyy') as year,
to_char(t.putdate, 'mm') as month,
to_char(t.putdate, 'dd') as day
from testtable t
group by to_char(t.putdate, 'yyyy'),
to_char(t.putdate, 'mm'),
to_char(t.putdate, 'dd')
order by day