ORACLE GROUPINGº¯ÊýµÄʹÓÃ
GROUPINGº¯Êý¿ÉÒÔ½ÓÊÜÒ»ÁУ¬·µ»Ø0»òÕß1¡£Èç¹ûÁÐֵΪ¿Õ£¬ÄÇôGROUPING()·µ»Ø1£»Èç¹ûÁÐÖµ·Ç¿Õ£¬ÄÇô·µ»Ø0¡£GROUPINGÖ»ÄÜÔÚʹÓÃROLLUP»òCUBEµÄ²éѯÖÐʹÓᣵ±ÐèÒªÔÚ·µ»Ø¿ÕÖµµÄµØ·½ÏÔʾij¸öֵʱ£¬GROUPING()¾Í·Ç³£ÓÐÓá£
¹ØÓÚROLLUPºÍCUBEº¯ÊýµÄʹÓã¬Çë²Î¼ûÎÒµÄÁíһƪÎÄÕ¡£
http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx
1¡¢ÔÚROLLUPÖжԵ¥ÁÐʹÓÃGROUPING()
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
¼ÓÉÏGROUPINGÀ´¿´¿´
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
¿ÉÒÔ¿´µ½£¬Îª¿ÕµÄµØ·½·µ»Ø1£¬·Ç¿ÕµÄµØ·½·µ»Ø0¡£
2¡¢Ê¹ÓÃCASEת»»GROUPING()µÄ·µ»ØÖµ
¿ÉÄÜÄã»á¾õµÃÇ°ÃæµÄ0ºÍ1Ì«¿ÝÔïÁË£¬´ú±í²»ÁËÈκÎÒâÒ壬˵°×Á˾ÍÊDz»¹»ÈËÐÔ»¯£¬ºÇºÇ¡£Õâ¸öʱºòÎÒÃÇ¿ÉÒÔʹÓÃCASEÀ´×ª»»ÎªÒ
Ïà¹ØÎĵµ£º
ÔÚʹÓÃOracleÖÐʹÓÃRownum£¬»áÓÐЩÎó½â£¬ÏÖÕûÀíÏÂÍøÓÑÃǵÄÎÄÕ£¬Ï£ÍûÄÜÓÐËù°ïÖú£¬ºÇºÇ
¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>,>=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËƺõÊÇĪÃûÆäÃîµÄ½á¹û ......
½ñÌìÔÚһ̨2003serverÉÏ°²×° 9i¿Í»§¶Ë¡£
ÅäÖÃÁËtnsÖ®ºóÁ¬½Ó£¬±¨´í "ORA-06413 Á¬½Óδ´ò¿ª´íÎó".
°ÑÆäËü·þÎñÆ÷µÄtnsname.oraÖ±½Ó¿½±´¹ýÀ´Ò²²»ÐС£
ºóÀ´²éÁËÒ»ÏÂ×ÊÁÏ£¬ËµÓ¦ÓóÌÐò·¾¶Àï±ßÓÐÌØÊâ×Ö·û¡£
Õâ²Å»ÐÈ»´óÎò£¬Á½ÄêÇ°Õâ¸öÎÊÌâÒѾÕÛÄ¥¹ýÎÒÒ»´ÎÁË£¬ÄÔ×Ó²»ºÃ£¬¾¹È»¸øÍüÁË¡£
ÔÒò¾ÍÊÇ64λϵͳ»·¾³Ï°²×°oracle£¬oralc ......
--oralceÖдӱíÖÐËæ»úÈ¡³önÌõ¼Ç¼
select * from (select t.*,dbms_random.random num from fms_branch_info t order by num)
where rownum <= 5;
--È¡Ç°Ê®ÐÐ
SELECT t.* from fms_branch_info t
WHERE ROWNUM != 10
--WHERE ROWNUM between 1 and 10
ORDER BY branch_code;
......
declare
sqlstring varchar2(100);
emprow emp_bak%rowtype;
myno emp_bak.empno%type;
begin
--sqlstring:='create table emp_bak as select * from emp';
sqlstring:='select * from emp_bak where empno=:myno and sal>:mysal';
myno:='&ÇëÊäÈëÔ±¹¤±àºÅ';
--¶¯Ì¬SQ ......