SQLº¯ÊýÖ®ËÄÉáÎåÈë
SQLº¯ÊýÖ®ËÄÉáÎåÈ루ת×Ôhttp://ln1058.javaeye.com/blog/191502£©
ÎÊÌâ1£º
SELECT CAST('123.456' as decimal) ½«»áµÃµ½ 123£¨Ð¡ÊýµãºóÃæµÄ½«»á±»Ê¡ÂÔµô£©¡£
Èç¹ûÏ£ÍûµÃµ½Ð¡ÊýµãºóÃæµÄÁ½Î»¡£
ÔòÐèÒª°ÑÉÏÃæµÄ¸ÄΪ
SELECT CAST('123.456' as decimal(38, 2)) ===>123.46
×Ô¶¯ËÄÉáÎåÈëÁË£¡ ÎÊÌâ2£º
SELECT ROUND(123.75633, 2, 1), ROUND(123.75633, 2)
ÉÏÃæµÄSQLµÃµ½µÄ2¸öÖµÊDz»Ò»ÑùµÄ£¬Ç°Ò»¸öÊÇ£º123.75000£¬ºóÒ»¸öÊÇ£º123.76000¡£
ÒòΪǰÕßÔÚ½øÐÐËÄÉáÎåÈë֮ǰ£¬Ð¡ÊýµãºóÒѾ±»½ØÈ¡£¬±£ÁôÁË2λ¡£
¶øºóÕßÔòûÓб»½ØÈ¡£¬ËÄÉáÎåÈëʱ×ÔÈ»¾Í»áµÃµ½123.76000 ROUND
·µ»ØÊý×Ö±í´ïʽ²¢ËÄÉáÎåÈëΪָ¶¨µÄ³¤¶È»ò¾«¶È¡£ Óï·¨
ROUND ( numeric_e-xpression , length [ , function ] ) ²ÎÊý
numeric_e-xpression ¾«È·Êý×Ö»ò½üËÆÊý×ÖÊý¾ÝÀàÐÍÀà±ðµÄ±í´ïʽ£¨bit Êý¾ÝÀàÐͳýÍ⣩¡£ length ÊÇ numeric_e-xpression ½«ÒªËÄÉáÎåÈëµÄ¾«¶È¡£length ±ØÐëÊÇ tinyint¡¢smallint »òint¡£µ± length ΪÕýÊýʱ£¬numeric_e-xpression ËÄÉáÎåÈëΪ length ËùÖ¸¶¨µÄСÊýλÊý¡£µ± length Ϊ¸ºÊýʱ£¬numeric_e-xpression Ôò°´ length ËùÖ¸¶¨µÄÔÚСÊýµãµÄ×ó±ßËÄÉáÎåÈë¡£ function ÊÇÒªÖ´ÐеIJÙ×÷ÀàÐÍ¡£function ±ØÐëÊÇ tinyint¡¢smallint »ò int¡£Èç¹ûÊ¡ÂÔ function »ò function µÄֵΪ 0£¨Ä¬ÈÏ£©£¬numeric_e-xpression ½«ËÄÉáÎåÈë¡£µ±Ö¸¶¨ 0 ÒÔÍâµÄֵʱ£¬½«½Ø¶Ï numeric_e-xpression¡£ ·µ»ØÀàÐÍ
·µ»ØÓë numeric_e-xpression ÏàͬµÄÀàÐÍ¡£ ×¢ÊÍ
ROUND ʼÖÕ·µ»ØÒ»¸öÖµ¡£Èç¹û length ÊǸºÊýÇÒ´óÓÚСÊýµãÇ°µÄÊý×Ö¸öÊý£¬ROUND ½«·µ»Ø 0¡£ ʾÀý ½á¹û
ROUND(748.58, -4) 0 µ± length ÊǸºÊýʱ£¬ÎÞÂÛʲôÊý¾ÝÀàÐÍ£¬ROUND ¶¼½«·µ»ØÒ»¸öËÄÉáÎåÈëµÄ numeric_e-xpression¡£ ʾÀý ½á¹û
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00 ʾÀý
A. ʹÓà ROUND ºÍ¹À¼ÆÖµ
ÏÂÀýÏÔʾÁ½¸ö±í´ïʽ£¬ËµÃ÷ʹÓà ROUND º¯ÊýÇÒ×îºóÒ»¸öÊý×ÖʼÖÕÊǹÀ¼ÆÖµ¡£ Select ROUND(123.9994, 3), ROUND(123.9995, 3)
GO ÏÂÃæÊǽá¹û¼¯£º ----------- -----------
123.9990 124.0000 B. ʹÓà ROUND ºÍËÄÉáÎåÈëµÄ½üËÆÖµ
ÏÂÀýÏÔʾËÄÉáÎåÈëºÍ½üËÆÖµ¡£ Óï¾ä ½á¹û
Select ROUND(123.454
Ïà¹ØÎĵµ£º
ͨÅä·û_
"_"ºÅ±íʾÈÎÒâµ¥¸ö×Ö·û,¸Ã·ûºÅÖ»ÄÜÆ¥ÅäÒ»¸ö×Ö·û."_"¿ÉÒÔ·ÅÔÚ²éѯÌõ¼þµÄÈÎÒâλÖÃ,ÇÒÖ»ÄÜ´ú±íÒ»¸ö×Ö·û.Ò»¸öºº×ÖֻʹÓÃÒ»¸ö"_"±íʾ.
Àý×Ó£º
if PATINDEX('%[ß¹-×ö]%','ÐèÒªÅжϵÄ×Ö·û')>0 -- ÅжÏÊÇ·ñÓÐ×Ö·û
print 'Óкº×Ö'
else
print 'ÎÞºº×Ö'
ͨÅä·û%
"%"·ûºÅÊÇ×Ö·ûÆ¥Åä·û,ÄÜÆ¥Åä0¸ö»ò¸ü¶à×Ö·ûµÄÈÎÒⳤ¶ ......
1.Ê×ÏÈÐèÒª½¨Á¢plan table,·ñÔò²»ÄÜʹÓÃ
½¨Á¢·½·¨£º
$oracle\rdbms\adminÏÂÓиö
utlxplan.sql
ÆäÄÚÈÝΪ:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operat ......
ÔÀí£º¶ÔÐèҪȥÖظ´¼Ç¼µÄ×ֶΰ´×éÅÅÐò£¬È»ºóÈ¡ÆäÖÐÒ»Ìõ¼Ç¼¡£ÔÚ×ܲéѯÓï¾äÖÐʹÓÃinÓï·¨¹ýÂË
È¥µôÖظ´¼Ç¼
select * from company where comid in (select Max(comid) from company group by companyname)
µÃµ½Öظ´¼Ç¼Êý
select * from company where comid not in (select Max(comid) from company group by companyn ......
Ò»¡¢PL/SQL³öÏÖµÄÄ¿µÄ
¡¡¡¡½á¹¹»¯²éѯÓïÑÔ(Structured Query
Language£¬¼ò³ÆSQL)ÊÇÓÃÀ´·ÃÎʹØϵÐÍÊý¾Ý¿âÒ»ÖÖͨÓÃÓïÑÔ£¬ËüÊôÓÚµÚËÄ´úÓïÑÔ£¨4GL£©£¬ÆäÖ´ÐÐÌصãÊǷǹý³Ì»¯£¬¼´²»ÓÃÖ¸Ã÷Ö´ÐеľßÌå·½·¨ºÍ;
¾¶£¬¶øÊǼòµ¥µÄµ÷ÓÃÏàÓ¦Óï¾äÀ´Ö±½ÓÈ¡µÃ½á¹û¼´¿É¡£ÏÔÈ»£¬ÕâÖÖ²»¹Ø×¢ÈκÎʵÏÖϸ½ÚµÄÓïÑÔ¶ÔÓÚ¿ª·¢ÕßÀ´ËµÓÐ׿«´óµÄ ......