Êý¾Ý²É¼¯Öг£ÓõÄ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ºóÃæµÄÏà¹ØÔËË㣬ÒÔ´ïµ½¼õÉÙÊý¾Ý¿âϵ
Ïà¹ØÎĵµ£º
Ò»£®WITH ASµÄº¬Òå
WITH AS¶ÌÓҲ½Ð×ö×Ó²éѯ²¿·Ö£¨subquery factoring£©£¬¿ÉÒÔÈÃÄã×öºÜ¶àÊÂÇ飬¶¨ÒåÒ»¸öSQLƬ¶Ï£¬¸ÃSQLƬ¶Ï»á±»Õû¸öSQLÓï¾äËùÓõ½¡£ÓеÄʱºò£¬ÊÇΪÁËÈÃSQLÓï¾äµÄ¿É¶ÁÐÔ¸ü¸ßЩ£¬Ò²ÓпÉÄÜÊÇÔÚUNION ALLµÄ²»Í¬²¿·Ö£¬×÷ΪÌṩÊý¾ÝµÄ²¿·Ö¡£
Ìرð¶ÔÓÚUNION ALL±È½ÏÓÐÓá£ÒòΪUNION ALLµÄÿ¸ö²¿·Ö¿ÉÄÜÏàͬ£¬µ ......
ʹÓÃ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 ......
1. GROUP BY ÊÇ·Ö×é²éѯ, Ò»°ã GROUP BY ÊǺ;ۺϺ¯ÊýÅäºÏʹÓÃ
group by ÓÐÒ»¸öÔÔò,¾ÍÊÇ select ºóÃæµÄËùÓÐÁÐÖÐ,ûÓÐʹÓþۺϺ¯ÊýµÄÁÐ,±ØÐë³öÏÖÔÚ group by ºóÃ棨ÖØÒª£©
ÀýÈç,ÓÐÈçÏÂÊý¾Ý¿â±í£º
A B
1 abc
1 bcd
1 asdfg
Èç¹ûÓÐÈ ......
ÉùÃ÷×Ö¶ÎÓ³Éä
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface FiledRef
{
String fieldName();
}
ÉùÃ÷±íÓ³Éä
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableRef
{
& ......
--·Ö½â×Ö·û´®°üº¬µÄÐÅÏ¢ÖµºóÈ»ºóºÏ²¢µ½ÁíÍâÒ»±íµÄÐÅÏ¢
--(°®Ð¾õÂÞ.ع»ª(Ê®°ËÄê·çÓê,ÊصñùɽѩÁ«»¨¿ª) 2007-12-23 ¹ã¶«ÉîÛÚ)
/*ÎÊÌâÃèÊö
tba
ID classid name
1 1,2,3 Î÷·þ
2 2,3 ÖÐɽװ
3&n ......