OracleµÄ ¼òµ¥ÐÐתÁÐ
Ó¦¸ö±¨±íÒªÇó×ö¸ö
ʱ¼ä ˫ɫÇò£¬ ʤ¸º²Ê¡£¡£¡£¡£¡£
200905 ÏúÁ¿ ÏúÁ¿
200906 ÏúÁ¿ ÏúÁ¿
200907 ÏúÁ¿ ÏúÁ¿
200908 ÏúÁ¿ ÏúÁ¿
Ò»¸öSQL Óï¾äµÃµ½½á¹ûÊÇ£º
f_yearmonth, lot_name,money
200905 ˫ɫÇò 50
200905 ʤ¸º²Ê 100
200906 ˫ɫÇò 60
200906 ʤ¸º²Ê 70
Select f_yearmonth,
Max(decode(f_lotname,'˫ɫÇò',f_money,0)) As "˫ɫÇò",
Max(decode(f_lotname,'¸£²Ê3D',f_money,0)) as "¸£²Ê3D",
Max(decode(f_lotname,'ʤ¸º²Ê',f_money,0)) as "ʤ¸º²Ê",
Max(decode(f_lotname,'ʱʱ²Ê',f_money,0)) as "ʱʱ²Ê",
Max(decode(f_lotname,'ʱʱÀÖ',f_money,0)) as "ʱʱÀÖ",
from
t_tmp_test_A
Group By f_yearmonth
Order By f_yearmonth
¹Ø¼üÓï¾ä£º
Max(decode(f_lotname,'˫ɫÇò',f_money,0)) As "˫ɫÇò",
Group By f_yearmonth
ӦΪ °´Ê±¼äÖØ×éÖ»ÓÐÒ»Ìõ£¬²¢ÇÒÖ»ÓÐÒ»ÌõÊý¾Ý£º 200905 ˫ɫÇò 50
ÄÇô ÓÃMAXȡһÌõÊý¾Ý ¾ÍÊÇ50
decode Òâ˼˵ Èç¹ûÊǑ˫ɫÇò’ È¡¶ÔÓ¦µÄf_money ²¢ÇÒ·ÅÔÚ ÃüÃûΪ "˫ɫÇò"µÄ ÁÐÉÏ¡£
ºÜÏÔÈ» ÕâÁÐÊǹ̶¨£¡
Ïà¹ØÎĵµ£º
Ò»£¬PL/SQL¿éµÄ½á¹¹ºÍ×é³ÉÔªËØ
PL/SQL³ÌÐòÓÉÉùÃ÷²¿·Ö£¬Ö´Ðв¿·Ö£¬Òì³£´¦Àí²¿·ÖÈý¸ö²¿·Ö×é³É¡£½á¹¹ÈçÏ£º
DECLARE
/*ÉùÃ÷²¿·Ö£ºÔÚ´ËÉùÃ÷PL/SQL±äÁ¿£¬ÀàÐͼ°Óα꣬ÒÔ¼°¾Ö²¿µÄ´æ´¢¹ý³ÌºÍº¯Êý*/
BEGIN
/*Ö´Ðв¿·Ö£º¹ý³Ì¼°sqlÓï¾ä£¬³ÌÐòÖ÷Òª²¿·Ö£¬ÊDZØÐëµÄ*/
EXCEPTION
/*Òì³£´¦Àí²¿·Ö£º´íÎó´¦Àí*/
END
PL/SQL¿ ......
1 . set database to archivelog mode
Sql´úÂë
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE&n ......
ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ( ......
drop table tb_wjf_xh_dg100_50_tmp4 purge;
create table tb_wjf_xh_dg100_50_tmp4
(
servnumber varchar(11)
)
;
declare
vv_cusor_servnumber varchar2(32);
vv_cusor_lost_cnt & ......