TIME TEST1 ID
2010-4-14 1 1
2010-4-15 2 2
2010-4-16 5 3
2010-4-14 4 4
2010-4-22 5 5
2010-4-22 8 6
2010-4-22 8 7有这么个表求条sql语句 我要用条SQL语句 查询得到下面的结果TIME TEST1 ID
2010-4-14 2010414001 1
2010-4-15 2010415001 2
2010-4-16 2010416001 3
2010-4-14 2010414002 4
2010-4-22 2010422001 5
2010-4-22 2010422002 6
2010-4-22 2010422003 7
2010-4-14 1 1
2010-4-15 2 2
2010-4-16 5 3
2010-4-14 4 4
2010-4-22 5 5
2010-4-22 8 6
2010-4-22 8 7有这么个表求条sql语句 我要用条SQL语句 查询得到下面的结果TIME TEST1 ID
2010-4-14 2010414001 1
2010-4-15 2010415001 2
2010-4-16 2010416001 3
2010-4-14 2010414002 4
2010-4-22 2010422001 5
2010-4-22 2010422002 6
2010-4-22 2010422003 7
select TIME,to_char(TIME,'YYYYMMDD')||rn, ID
from
(
select TIME, ID ,rownum as rn
from tab
group by time,id
)
to_char(time,'yyyymmdd')||to_char(row_number()over(partition by time order by id),'fm000') test1,id
from table1
from
(
select TIME, ID ,RANK() OVER (PARTITION BY TIME ORDER BY ID) as rn
from tab
group by time,id
)
select TIME,to_char(TIME,'yyyymmdd')||to_char(rn,'fm000') as TEST1,ID
(select TIME,TEST1,ID,rownum() over(partition by TIME order by ID asc) rn
from tb) a