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

[ת]sql·Ö×éͳ¼Æ

--°´Ä³Ò»×ֶηÖ×éÈ¡×î´ó(С)ÖµËùÔÚÐеÄÊý¾Ý   
Êý¾ÝÈçÏ£º
name val memo
a    2   a2(aµÄµÚ¶þ¸öÖµ)
a    1   a1--aµÄµÚÒ»¸öÖµ
a    3   a3:aµÄµÚÈý¸öÖµ
b    1   b1--bµÄµÚÒ»¸öÖµ
b    3   b3:bµÄµÚÈý¸öÖµ
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--´´½¨±í²¢²åÈëÊý¾Ý£º
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(aµÄµÚ¶þ¸öÖµ)')
insert into tb values('a',    1,   'a1--aµÄµÚÒ»¸öÖµ')
insert into tb values('a',    3,   'a3:aµÄµÚÈý¸öÖµ')
insert into tb values('b',    1,   'b1--bµÄµÚÒ»¸öÖµ')
insert into tb values('b',    3,   'b3:bµÄµÚÈý¸öÖµ')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go
--Ò»¡¢°´name·Ö×éÈ¡val×î´óµÄÖµËùÔÚÐеÄÊý¾Ý¡£
--·½·¨1£º
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--·½·¨2£º
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--·½·¨3£º
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--·½·¨4£º
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--·½·¨5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo        


Ïà¹ØÎĵµ£º

sqlÓï¾ä¼¯ºÏ

A¡£
SQLÓï¾äµÄ²¢¼¯UNION£¬½»¼¯JOIN(ÄÚÁ¬½Ó£¬ÍâÁ¬½Ó)£¬½»²æÁ¬½Ó(CROSS
JOINµÑ¿¨¶û»ý)£¬²î¼¯(NOT IN)
1.
a. ²¢¼¯UNION
SELECT column1, column2 from table1
UNION
SELECT column1, column2 from table2
b. ½»¼¯JOIN
SELECT * from table1 AS a JOIN table2 b ON a.name=b.name
c. ²î¼¯NOT IN
SELECT * from tabl ......

ÔÚSQL ServerÖÐÈçºÎÊä³öÐкÅ


Ô­ÎĵØÖ·£ºhttp://www.dingos.cn/index.php?topic=1688.0
OracleÓÐrownumÓÃÓÚ·ÃÎʱíÖÐÐкš£ÄÇôÔÚSQL ServerÖÐÊÇ·ñÓеÈЧµÄÄØ£¿»òÕßÔÚSQL ServerÖÐÈçºÎÊä³öÐкţ¿
-----------------------------------
ÔÚSQL ServerÖÐûÓÐÖ±½ÓµÈЧÓÚOracleµÄrownum
-----------------------------------
Ñϸñ˵À´£¬ÔÚ¹ØϵÊý¾Ý¿âÖУ ......

SQL Server CONVERT() º¯Êý


Ô­Îijö´¦£ºhttp://www.dingos.cn/index.php?topic=1874.0
¶¨ÒåºÍÓ÷¨
CONVERT() º¯ÊýÊÇ°ÑÈÕÆÚת»»ÎªÐÂÊý¾ÝÀàÐ͵ÄͨÓú¯Êý¡£
CONVERT() º¯Êý¿ÉÒÔÓò»Í¬µÄ¸ñʽÏÔʾÈÕÆÚ/ʱ¼äÊý¾Ý
Óï·¨
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) ¹æ¶¨Ä¿±êÊý¾ÝÀàÐÍ£¨´øÓпÉÑ¡µÄ³¤¶È£©¡£data_to_be_conv ......

Orcale µÄSQL Óï¾äÈ¡µÃϵͳµ±Ç°Ê±¼äÓãºsysdate

Orcale µÄSQL Óï¾äÈ¡µÃϵͳµ±Ç°Ê±¼äÓãºsysdate
    µ±ÐèÒªÔÚϵͳµ±Ç°ÈÕÆÚÉϼõÈ¥Ò»Ììʱ¿ÉÒÔÓà  sysdate-1
     ¸½£ºµ±Ö»¶ÔÒ»¶¨ÊýÁ¿µÄ¼Ç¼¸ÐÐËȤʱ¿ÉÒÔÈç rownum<100
select * from SLYC_CUSTINFO_T where indbtime>sysdate-1 and OFFICE_CODE='46' and rownum<1 ......

SQL server´¥·¢Æ÷ÈëÃÅ2 ÖØÃüÃû´¥·¢Æ÷


1.Óòéѯ·ÖÎöÆ÷ÖØÃüÃû
exec sp_rename Ô­Ãû³Æ, ÐÂÃû³Æ
sp_rename ÊÇ SQL Server™ ×Ô´øµÄÒ»¸ö´æ´¢¹ý³Ì£¬ÓÃÓÚ¸ü¸Äµ±Ç°Êý¾Ý¿âÖÐÓû§´´½¨µÄ¶ÔÏóµÄÃû³Æ£¬Èç±íÃû¡¢ÁÐ±í¡¢Ë÷ÒýÃûµÈ¡£
2.ÓÃÆóÒµ¹ÜÀíÆ÷ÖØÃüÃû
ÔÚ±íÉϵãÓÒ¼ü->“ËùÓÐÈÎÎñ”->“¹ÜÀí´¥·¢Æ÷”£¬Ñ¡ÖÐËùÒªÖØÃüÃûµÄ´¥·¢Æ÷£¬Ð޸Ĵ¥·¢Æ÷ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ