A表如下 NAME TIME CD
TONY 2 aa
MIKY 1 bb
RITA 3 cc想得如下结果 NAME TIME CD
TONY 1 aa
TONY 1 aa
MIKY 1 bb
RITA 1 cc
RITA 1 cc
RITA 1 cc该怎么写呢?
TONY 2 aa
MIKY 1 bb
RITA 3 cc想得如下结果 NAME TIME CD
TONY 1 aa
TONY 1 aa
MIKY 1 bb
RITA 1 cc
RITA 1 cc
RITA 1 cc该怎么写呢?
SQL> SELECT ANAME,
2 1 "TIME",
3 CD
4 FROM A,
5 (SELECT ROWNUM RN
6 FROM DUAL
7 CONNECT BY ROWNUM <= (SELECT MAX(ATIME) FROM A)-1
8 )TT
9 WHERE RN <= ATIME
10 ORDER BY 3
11 ;ANAME TIME CD
----- ---------- --
TONY 1 AA
TONY 1 AA
MIKY 1 BB
RITA 1 CC
RITA 1 CC
RITA 1 CC6 rows selectedSQL>
SELECT NAME,1 amount,cd
FROM (SELECT OBJECT_ID - 1 RN, T4.*
FROM DBA_OBJECTS, T4
WHERE OBJECT_ID - 1 <= T4.AMOUNT)
ORDER BY CD;输出:
NAME AMOUNT CD
TONY 1 aa
TONY 1 aa
MIKY 1 bb
RITA 1 cc
RITA 1 cc
RITA 1 cc