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

Êý¾Ý²É¼¯Öг£ÓõÄSQLÓï¾ä¼°¼¼ÇÉ

Êý¾Ý²É¼¯Öг£ÓõÄSQLÓï¾ä
   ÏàͬµÄSQLÓï¾äÔËÓõ½²»Í¬Êý¾Ý¿âÖлáÓÐÂÔ΢µÄ²î±ð£¬¶Ô×Ö·û±äÁ¿µÄÒªÇó£¬Ïà¹Øº¯ÊýµÄ±ä»¯£¬ÒÔ¼°Óï·¨¹æÔòµÄ²»Í¬µÈµÈ£¬ÀýÈ磺oracleÊý¾Ý¿âÖжÔ×Ö¶ÎÃüÃû±ðÃûʱ²»ÐèÒªas ×Ö·û£¬Ã»ÓÐmonth()£¬year£¨£©µÈʱ¼äº¯ÊýµÈµÈ£¬accessÊý¾Ý¿âÖÐÔÚʹÓÃinner joinÖ´ÐÐÄÚ²¿ÁªºÏʱÌõ¼þÐèÓ㨣©£¬µ±È»»¹ÓкܶàµÄϸ΢²î±ð£¬´ó¼Ò¿ÉÒÔ×Ô¼ºÈ¥Ñ°ÕÒ×ܽᡣÏÂÃæµÄʾÀýÒÔSQL SERVERΪ»ù´¡±àд¡£
1. ³éÈ¡·ÇÖظ´Êý¾Ý
select distinct var1 from tableName;
2. ³éȡij¸öʱ¼ä¶Î¼äµÄÊý¾Ý
select var1,var2 from Êý¾Ý±í where ×Ö¶ÎÃû between ʱ¼ä1 and ʱ¼ä2; 
3. Á¬½Ó¶à¸ö±äÁ¿
select '123'+cast(456 as varchar);
select '123'+cast(456 as varchar)+'789';
4. ÓÃSQLÓï¾äÕÒ³ö±íÃûΪTable1ÖеĴ¦ÔÚID×Ö¶ÎÖÐ1-200Ìõ¼Ç¼ÖÐName×ֶΰüº¬wµÄËùÓмǼ
select * from Table1 where id between 1 and 200 and Name like '%w%';  
5. ÕÒ³öÓµÓг¬¹ý10Ãû¿Í»§µÄµØÇøµÄÁбí
select country from test group by country having count(customerId)>10; 
6. ¹ØÓÚÈ¡³öÿ¸ö²¿ÃŹ¤×Ê×î¸ßµÄÇ°ÈýÈË
select * from table t where ¹¤×Ê in (select top 3 ¹¤×Ê from table where ²¿ÃÅ = t.²¿ÃÅ order by ¹¤×Ê desc);   
7. Á½¸ö½á¹¹ÍêÈ«ÏàͬµÄ±íaºÍb£¬Ö÷¼üΪindex£¬Ê¹ÓÃSQLÓï¾ä£¬°Ña±íÖдæÔÚµ«ÔÚb±íÖв»´æÔÚµÄÊý¾Ý²åÈëµÄb±íÖÐ
insert into b select * from a where not exists(select * from b where "index"=a."index");   
8.´ÓÒ»¸öÊý¾Ý¿âÖеĶà¸öÊý¾Ý±íÌáÈ¡Ïà¹Ø±äÁ¿
Select table1.var1,table2.var2,table2.var3,
from table1 inner join table2
On tabel1.var1=table2.var1
Inner join table3
On tabel1.var2=table3.var2
(order by ……)
SQL²éѯÏà¹ØС¼¼ÇÉ 
·Ê¹ÓÃANDʱ£¬½«²»ÎªÕæµÄÌõ¼þ·ÅÔÚÇ°Ãæ
    Êý¾Ý¿âϵͳ×ñÑ­ÔËËã·ûµÄÓÅÏȼ¶£¬²¢ÇÒÔËËã¹ý³ÌÊÇ´Ó×óÖÁÓҵģ¬½«Ìõ¼þ²»ÎªÕæµÄ·ÅÔÚÇ°Ã棬ÔòÄܹ»Ê¡È¥andºóÃæµÄÏà¹ØÔËË㣬ÒÔ´ïµ½¼õÉÙÊý¾Ý¿âϵ


Ïà¹ØÎĵµ£º

SQL ÖÐWith as µÄÓ÷¨

Ò»£®WITH ASµÄº¬Òå
WITH AS¶ÌÓҲ½Ð×ö×Ó²éѯ²¿·Ö£¨subquery factoring£©£¬¿ÉÒÔÈÃÄã×öºÜ¶àÊÂÇ飬¶¨ÒåÒ»¸öSQLƬ¶Ï£¬¸ÃSQLƬ¶Ï»á±»Õû¸öSQLÓï¾äËùÓõ½¡£ÓеÄʱºò£¬ÊÇΪÁËÈÃSQLÓï¾äµÄ¿É¶ÁÐÔ¸ü¸ßЩ£¬Ò²ÓпÉÄÜÊÇÔÚUNION ALLµÄ²»Í¬²¿·Ö£¬×÷ΪÌṩÊý¾ÝµÄ²¿·Ö¡£
Ìرð¶ÔÓÚUNION ALL±È½ÏÓÐÓá£ÒòΪUNION ALLµÄÿ¸ö²¿·Ö¿ÉÄÜÏàͬ£¬µ ......

SQL server 2008 ´´½¨Ö÷¼üΪ×ÔÔöÁеıí

ʹÓÃTranact-SQL ±àд´úÂëÀ´´´½¨Ò»¸öÐÂ±í£º
 
USE [OnlineJudge]
GO
/****** Object: Table [dbo].[User1] Script Date: 05/17/2010 14:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User2](/*notice convert the tablename*/
[num] [int]IDENTITY(1,1) NOT N ......

sql group by Óë havingµÄÓ÷¨

1. GROUP BY ÊÇ·Ö×é²éѯ, Ò»°ã GROUP BY ÊǺ;ۺϺ¯ÊýÅäºÏʹÓÃ
group by ÓÐÒ»¸öÔ­Ôò,¾ÍÊÇ select ºóÃæµÄËùÓÐÁÐÖÐ,ûÓÐʹÓþۺϺ¯ÊýµÄÁÐ,±ØÐë³öÏÖÔÚ group by ºóÃ棨ÖØÒª£©
ÀýÈç,ÓÐÈçÏÂÊý¾Ý¿â±í£º
A    B
1    abc
1    bcd
1    asdfg
 Èç¹ûÓÐÈ ......

java Annotation Æ´×°SQLÓï¾ä

ÉùÃ÷×Ö¶ÎÓ³Éä
@Target(ElementType.FIELD)  
@Retention(RetentionPolicy.RUNTIME) 
public @interface FiledRef
{
    String fieldName();
}
ÉùÃ÷±íÓ³Éä
@Target(ElementType.TYPE)  
@Retention(RetentionPolicy.RUNTIME)
public @interface TableRef
{
 & ......

sqlÌæ»»Óï¾ä

--·Ö½â×Ö·û´®°üº¬µÄÐÅÏ¢ÖµºóÈ»ºóºÏ²¢µ½ÁíÍâÒ»±íµÄÐÅÏ¢
--(°®Ð¾õÂÞ.ع»ª(Ê®°ËÄê·çÓê,ÊصñùɽѩÁ«»¨¿ª)  2007-12-23  ¹ã¶«ÉîÛÚ)
/*ÎÊÌâÃèÊö
tba
ID  classid   name
1     1,2,3   Î÷·þ
2     2,3     ÖÐɽװ
3&n ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ