-------------删除重复--------------------
delete
FROM
XXTRAIN_TRAINERS_DW dw
WHERE
dw.rowid
NOT
IN
(
SELECT
MAX
(td.rowid)
FROM
XXTRAIN_TRAINERS_DW td
WHERE
td.trainerid
=
2162
GROUP
BY
td.start_time,td.trainerid
)
AND
dw.trainerid
=
2162
---------查看连接--------------
select
a.program,
a.machine,
b.spid,
c.sql_text
from
v$session a,
v$process b,
v$sqlarea c
where
a.paddr
=
b.addr
and
a.sql_hash_value
=
c.hash_value
and
a.sql_address
=
c.address
------------------乘法表-------------------
select
replace
(
reverse
(
SYS_CONNECT_BY_PATH(
REVERSE
(
ROWNUM
||
'
*
'
||
LV
||
'
=
'
||
RPAD(ROWNUM
*
LV,
2
)
),
'
/
'
)),
'
/
'
) NAME, ROWNUM n
from
(
select
level
LV
from
DUAL connect
by
level
<
10
)
where
LV
=
1
connect
by
LV
+
1
=
prior LV
with
t
as
(
select
level
as
n
from
dual connect
by
level
<=
9
)
select
max
(substr(sys_connect_by_path(b.n
||
'
*
'
||
a.n
||
'
=
'
||
a.n
*
b.n,
'
,
'
),
3
))
as
val
from
t a, t b
where
a.n
>=
b.n
start
with
b.n
=
1
connect
by
a.n
=
prior a.n
and
b.n
=
prior b.n
+
1
group
by
a.n
Select
ltrim
(
max
(sys_connect_by_path(cj,
'
,
'
)),
'
,
'
)
From
(
Select
r p,Row_number()
over
(Partition
By
r
Order
By
cj) c ,cj
From
(
Select
r,n
||
'
*
'
||
r
||
'
=
'
||
r
*
n cj
From
(
Select
Rownum r
From
dual Connect
By
Rownum
<
10
) a,
(
Select
Rownum n
From
dual Connect
By
Rownum
<
10
) b
Where
r
>=
n
Order
By
r))
Start
With
c
=
1
Connect
By
c
-
1
=
Prior c
And
p
=
Prior p
Group
By
p
-----------去重----------
select
distinct
数据库编码
select
*
from
nls_database_parameters
where
parameter
=
'
NLS_CHARACTERSET
'
;
posted on 2007-11-15 09:46
交口称赞 阅读(279)
评论(0) 编辑 收藏 所属分类:
数据库