从我的oracle群(14559844)里总结出来的学习笔记
1〉怎么写sql语句删除表中重复的数据
循序渐进,有以下几种写法,不过最后一个应该是效率最高的吧
- delete from tab_nm a where rowid >(select min(rowid) from tab_nm b where
a.k=b.k)
INSERT INTO A
SELECT DISTINCT * FROM B
CREATE TABLE A
AS
SELECT DISTINCT * FROM B ;
DROP TABLE B;
RANAME A TO B;
2〉select length(to_char)用例参考
自己没有试过 直接从QQ记录里拷贝过来的
张三公子(65366533) 08:08:53
SQL> select length(to_char('1234','099999')) from dual;
LENGTH(TO_CHAR('1234','099999'
------------------------------
7
SQL> select (to_char('1234','099999')) from dual;
(TO_CHAR('1234','099999'))
--------------------------
001234
SQL> select (to_char('+1234','099999')) from dual;
(TO_CHAR('+1234','099999'))
---------------------------
001234
SQL> select (to_char('-1234','099999')) from dual;
(TO_CHAR('-1234','099999'))
---------------------------
-001234
张三公子(65366533) 08:10:45
是隐式的符号位...利用to_char进行数字型转换的 + 与 -的表示,空格表示正数,负的表示-
也可以利用 S来显示说明:
SQL> select (to_char('1234','S099999')) from dual;
(TO_CHAR('1234','S099999'))
---------------------------
+001234
SQL> select (to_char('-1234','S099999')) from dual;
(TO_CHAR('-1234','S099999'))
----------------------------
-001234
2009-08-06 16:35:59
sql里查询数据的时候进行格式化.可以用cast或者convert。举例如下,想把查询的结果按照decimal(18,2)格式输出。在mssql2005里测试通过。
SELECT CAST(AVG(mony) AS decimal(18, 2)) AS bb FROM t_table
SELECT CONVERT(decimal(10, 2), AVG(mony)) AS Expr1 FROM t_table