Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle SQL¾«ÃîSQLÓï¾ä½²½â


--ÐÐÁÐת»» ÐÐתÁÐ
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4;
SELECT * from t_change_lc;
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
from t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
--ÐÐÁÐת»» ÁÐתÐÐ
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
from t_change_lc a
GROUP BY a.card_code
ORDER BY 1;
SELECT * from t_change_cl;
SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
from (SELECT a.*, b.rn
from t_change_cl a,
(SELECT ROWNUM rn from dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;
--ÐÐÁÐת»» ÐÐתÁÐ ºÏ²¢
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q from t_change_lc;
SELECT * from t_change_lc_comma;
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
from (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
from t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--ÐÐÁÐת»» ÁÐתÐÐ ·Ö¸î
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
from (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
from t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIO


Ïà¹ØÎĵµ£º

oracle ±í¿Õ¼ä²Ù×÷

oracle±í¿Õ¼ä²Ù×÷Ïê½â
  1
  2
  3×÷Õߣº   À´Ô´£º    ¸üÐÂÈÕÆÚ£º2006-01-04 
  5
  6 
  7½¨Á¢±í¿Õ¼ä
  8
  9CREATE TABLESPACE data01
 10DATAFILE '/ora ......

SQL×¢ÈëרÌâ

 £Ó£Ñ£Ì×¢ÈëÊÇ´ÓÕý³£µÄWWW¶Ë¿Ú·ÃÎÊ£¬¶øÇÒ±íÃæ¿´ÆðÀ´¸úÒ»°ãµÄWebÒ³Ãæ·ÃÎÊûʲôÇø±ð£¬ ËùÒÔÄ¿Ç°ÊÐÃæµÄ·À»ðǽ¶¼²»»á¶Ô£Ó£Ñ£Ì×¢Èë·¢³ö¾¯±¨£¬Èç¹û¹ÜÀíԱû²é¿´IISÈÕÖ¾µÄÏ°¹ß£¬¿ÉÄܱ»ÈëÇֺܳ¤Ê±¼ä¶¼²»»á·¢¾õ¡£
    Ëæ×ÅB/SģʽӦÓÿª·¢µÄ·¢Õ¹£¬Ê¹ÓÃÕâÖÖģʽ±àдӦÓóÌÐòµÄ³ÌÐòÔ±Ò²Ô½À´Ô½¶à¡£µ«ÊÇÓÉÓÚÕâ¸öÐÐÒµµÄ ......

oracleµÄnvlº¯ÊýµÄÓ÷¨

oracleµÄnvlº¯ÊýµÄÓ÷¨ ͨ¹ý²éѯ»ñµÃij¸ö×ֶεĺϼÆÖµ£¬Èç¹ûÕâ¸öֵλnull½«¸ø³öÒ»¸öÔ¤ÉèµÄĬÈÏÖµ
select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1ÕâÀï¹ØÐĵÄnvlµÄÓ÷¨£¬nvl£¨arg,value£©´ú±íÈç¹ûÇ°ÃæµÄargµÄֵΪnullÄÇô·µ»ØµÄֵΪºóÃæµÄvalue
Èç: NVL(a,b)¾ÍÊÇÅжÏaÊÇ·ñÊÇNULL,Èç¹û²»ÊÇ·µ»ØaµÄÖµ£¬Èç¹ ......

ORACLE to_char(),to_date()Ó÷¨

to_date() function
1.ÈÕÆÚ¸ñʽ²ÎÊý º¬Òå˵Ã÷ 
D Ò»ÖÜÖеÄÐÇÆÚ¼¸ 
DAY ÌìµÄÃû×Ö£¬Ê¹ÓÿոñÌî³äµ½9¸ö×Ö·û 
DD ÔÂÖеĵڼ¸Ìì 
DDD ÄêÖеĵڼ¸Ìì 
DY ÌìµÄ¼òдÃû 
IW ISO±ê×¼µÄÄêÖеĵڼ¸ÖÜ 
IYYY ISO±ê×¼µÄËÄλÄê·Ý 
YYYY ËÄλÄê·Ý 
YYY,YY,Y Äê·ÝµÄ×îºóÈýÎ ......

OracleµÄrownumÔ­ÀíºÍʹÓÃ(·ÖÒ³²éѯ)

 
ÒªÏÔʾ1µ½2ÐÐÔò¿ÉÒÔͨ¹ý
select * from dangan where rownum between 1 and 2
ÔÚOracle
ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum
¾Í¸ã¶¨ÁË¡£
select *
from emp where rownum
<= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum
ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp
where row ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ