[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ʵ¼ÊÓ¦ÓÃÖÐÎÒÃÇ¿ÉÒÔͨ¹ýsum()ͳ¼Æ³ö×éÖеÄ×ܼƻòÕßÊÇÀÛ¼ÓÖµ£¬¾ßÌåʾÀýÈçÏ£º
1.´´½¨ÑÝʾ±í
create table emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key(empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
2. sum()Óï¾äÈçÏ£º
select deptno,
ename,
sal,
¡¡¡¡--°´ÕÕ²¿ÃÅнˮÀÛ¼Ó£¨order by¸Ä±äÁË·ÖÎöº¯ÊýµÄ×÷Óã¬Ö»¹¤×÷ÔÚµ±Ç°ÐкÍǰһÐУ¬¶ø²»ÊÇËùÓÐÐУ©
sum(sal) over (partition by deptno order by sal) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept, --ͳ¼ÆÒ»¸ö²¿ÃŵÄнˮ
sum(sal) over (order by deptno,sal) CumTot, --ËùÓйÍÔ±µÄнˮһÐÐÒ»ÐеÄÀÛ¼Ó
sum(sal) over () TotSal --ͳ¼Æ×Üнˮ
from emp
order by deptno, sal
3. ½á¹ûÈçÏ£º
10 MILLER 1300.00 1300 8750 1300 29025
10 CLARK 2450.00 3750 8750 3750 29025
10 KING 5000.00 8750 8750 
Ïà¹ØÎĵµ£º
/*=================== µ¼Èë/µ¼³ö Excel µÄ»ù±¾·½·¨ ===================*/
¡¡¡¡
¡¡¡¡´ÓExcelÎļþÖÐ,µ¼ÈëÊý¾Ýµ½SQLÊý¾Ý¿âÖÐ,ºÜ¼òµ¥,Ö±½ÓÓÃÏÂÃæµÄÓï¾ä:
¡¡¡¡
¡¡¡¡/*===================================================================*/
¡¡¡¡--Èç¹û½ÓÊÜÊý¾Ýµ¼ÈëµÄ±íÒѾ´æÔÚ
¡¡¡¡insert into ±í select * from
¡¡ ......
´óÅúÁ¿²åÈëÊý¾Ýʱ 1,myisam±í,¿ÉÒÔÏÈͨ¹ý alter table table_name disable keys;#ÏȹرձíµÄË÷Òý¼ì²é,×¢ÒâÊÇ·ÇΨһË÷Òý! load data infile ¡®/path/file¡¯ into table table_name; alter table table_name anable keys;#ÔÙ´ò¿ªË÷Òý ¿É´ó´ó¼Ó¿ìµ¼Èë.»¹ÓпÉÒÔÉèÖÃbulk_insert_buffer_sizeÖµÀ´Ìá¸ß²åÈëËÙ¶È ......
1.ÔÚ²éѯ·ÖÎöÆ÷ϲéѯExcelÎĵµ
Select * from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source = "c:\²âÊÔ.xls";User ID = Admin;Password=;Extended properties=Excel8.0)....Sheet1$
2.´ÓÊý¾Ý¿âÖе¼³öÊý¾Ý²¢´æµ½ÎļþÖÐ
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"."- ......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËÆºõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......
ÔÚʹÓÃODP.NET½øÐÐOracle±à³Ìʱ£¬ÓÐʱºòSQLÓï¾ä·Ç³£¸´ÔÓ£¬ÐèÒª²ÉÓö¯Ì¬¹¹Ôì²éѯÓï¾äµÄÇé¿ö£¬ÓÐÁ½ÖÖ·½·¨¿ÉÒÔ¹¹Ô춯̬µÄSQLÓï¾ä£¬²¢Ö´Ðзµ»Ø½á¹û¼¯¡£
1¡¢ÔÚÊý¾Ý·ÃÎʲ㹹ÔìSQLÓï¾ä
ÀýÈçÏÂÃæµÄÓï¾ä£¬½«¹¹ÔìÍêÕûµÄSQLÓï¾ä¸³Öµ¸øCommandText£¬ÔÙ´«µÝµ½Êý¾Ý¿â½øÐÐÖ´ÐУ¬·µ»Ø½á¹û¼¯¡£
loadCommand.CommandType = Com ......