SQL¾«»ªÊÕ¼¯
order by µÄÊýÖµÐÍÁé»îʹÓÃ
select * from table_a where id=p_id order by decode(º¯Êý,'asc',1,'desc',-1)*jsny;
¿ØÖÆÊÔͼµÄ·ÃÎÊʱ¼ä£º
6.create view ...
as
select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)
ÃîÓÃdecodeʵÏÖÅÅÐò
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
select * from tabname
order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
ÕÒ³öij¸öʱÆÚÄÚ¹¤×÷ÈÕÊý£º
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
in ( '1', '7' )
ÎÒ¾õµÃ²éÑ¯ÖØ¸´¼Ç¼µÄÓï¾ä¾ÍºÜ¾µä
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
ÓÉËüÒýÉêµÄ¾ÍÓкܶàÓÐÓõÄÓï¾ä£¬Èç×òÌì»Ø´ð±ðÈ˵ÄÅÅÐòµÄÄÑÌâ
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id
Ê÷Ðͽṹ±íµÄ²éѯ£º
select ID,PARENT_ID from parent_child
connect by prior id = parent_id
start with id = 1;
1.decodeÕâ¸öº¯ÊýÒ»¶¨ÐèÒª»á£¬ÎÒ¾õµÃsqlµÄÁé»îºÜ¶àµØ·½¶¼ÊÇͨ¹ýÕâ¸öfunctionÀ´ÌåÏֵģ¬Ï൱ÓÚif£¬ºÜºÃÓá£
2.group by£¬Õâ¸ö¶«¶«ÏëÏë¼òµ¥£¬ÆäʵºÃ¶àͳ¼Æ¹¦ÄÜÊÇÀë²»¿ªÕâ¸ö²Ù×÷µÄ¡£oracle8ÖÐÀ©³äÁËgroup by rollupºÍcubeµÄ²Ù×÷¡£ÓÐʱºòÊ¡ÁËÄãºÃ¶à¹¦·òµÄ¡£ÖµµÃ×¢ÒâµÄÊÇ£¬µ±Äã¶ÔÊÂÎï×ö¹ýÓÐЧµÄÈËΪ¹é²¢Ö®ºóÖ´ÐÐgroup by ÍùÍù»á¸üÈÃÈËÐÄ¿õÉñâù¡£
3.ºÜ±íÊúÖõľµäд·¨£¬Ò²Òª¼Çס£ºsum(decode( )) group by ...
×¢Ò⣺ÐèÒªÔÚÒ»¸ösubqueryÖÐÈ·¶¨Ò»¸öºáÖÃÅе㡣
4.Ê÷Ðνṹ±íµÄ±éÀúд·¨£ºselect ...from ....
start with ... connect by prior (¸¸×Ó¹ØÏµ±í´ïʽ)
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
816ÒÔÉÏµÄ Ò»Ð©·ÖÎöº¯ÊýÈç rank() over() and row_number() over()
µ±È»¹ØÓÚ group by rollup/cubeʹÓõÄÈË¿ÖÅÂÌØ±ðÉÙ
ÈçºÎʵÏÖ×î´óÆ¥ÅäµÄ²Ù×÷?
ÀýÈç:¸ø¶¨×Ö·û´® '1234', ¶ø±íÖпÉÄÜÓмǼÏî'1','12','123','1234','12345',Ҫѡ³ö'
Ïà¹ØÎĵµ£º
ºÜÉÙÓÃjoin£¬Õâ´Îѧѧ£¬²¢±¸ÍüÁ½ÆªÎÄÕ£¡
ת×Ô£ºhttp://hcx-2008.javaeye.com/blog/285661
Á¬½Ó²éѯ
ͨ¹ýÁ¬½ÓÔËËã·û¿ÉÒÔʵÏÖ¶à¸ö±í²éѯ¡£Á¬½ÓÊǹØÏµÊý¾Ý¿âÄ£Ð͵ÄÖ÷ÒªÌØµã£¬Ò²ÊÇËüÇø±ðÓÚÆäËüÀàÐÍÊý¾Ý¿â¹ÜÀíϵͳµÄÒ»¸ö±êÖ¾¡£
ÔÚ¹ØÏµÊý¾Ý¿â¹ÜÀíϵͳÖУ¬±í½¨Á¢Ê±¸÷Êý¾ÝÖ®¼äµÄ¹ØÏµ²»±ØÈ·¶¨£¬³£°ÑÒ»¸öʵÌåµÄËùÓÐÐÅÏ¢´æ·ÅÔÚÒ ......
1.ÐÞ¸ÄÁÐ
EXEC sp_rename 'TableName.ColumnName','NewColumName'
2.Ôö¼ÓÁÐ
ALTER TABLE TableName ADD ColumnName int --Type In Here
3.ɾ³ýÁÐ
ALTER TABLE TableName DROP Column ColumnName ......
ºÜÔç¾Í²»ÓÃSQLServer 2005ÁË£¬½ñÌìͻȻ½Óµ½¸öÏîÄ¿£¬Ëµ·ÇÓÃSQLServer²»¿É£¬Ã»°ì·¨£¬Ö»ºÃ°Ñ¾ÃÎ¥µÄÀÏÅóÓÑ×°ÉÏÈ¥ÁË£¬Óöµ½ÁËCOM+µÄĿ¼ҪÇó²»ÐеÄÎÊÌ⣬ÒÔǰÕâ¸öÎÊÌâÎÒҲûÓÐ×¢Ò⣬½ñÌìÏë½â¾öϸø´ó»ï¿´¿´¡£
¡¡¡¡½â¾öµÄ·½·¨ÆäʵºÜ¼òµ¥£¬ÈçÏ£º
¡¡¡¡1¡¢¿ª»§MSDTC·þÎñ£º“¿ªÊ¼-ÔËÐД£¬ÊäÈë"cmd"ºó»Ø³µ ......
--¸Ã±í±£´æ½á¹û
create table c(c1 tinyint, c2 tinyint, c3 tinyint, c4 tinyint, c5 tinyint, c6 tinyint, c7 tinyint, c8 tinyint, c9 tinyint, c10 tinyint, c11 tinyint, c12 tinyint, c13 tinyint, c14 tinyint, c15 tinyint, c16 tinyint, c17 tinyint, c18 tinyint, c19 tinyint, c20 tinyint, c21 tinyint, c22 t ......
Access µÄ SQL ÖУ¬¼ÆËãÏàÓ¦±àºÅµÄ¼Ç¼֮¼ä ¼Ó ¼õ µÄ²éѯÃüÁî
ÀýÈç ¼ÆËãIDΪ 6 µÄ ºÏ¼Æ ¼õÈ¥ ID Ϊ 2¡¢3¡¢4 ÒÔ¼°¼ÓÉÏ ID Ϊ 5 µÄºÏ¼Æ
SELECT SUM(R) AS ½á¹û
from
(
SELECT ºÏ¼Æ AS R from T1 WHERE (ID = 6) UNION
SELECT -ºÏ¼Æ AS R from T1 WHERE (ID = 2) UNION
SELECT -ºÏ¼Æ AS R f ......