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

SQLÃæÊÔÌâ[¾­µäÊÕ¼]


SQLÃæÊÔÌ⣨1£©
create table testtable1
(
id int IDENTITY,
department varchar(12)
)
select * from testtable1
insert into testtable1 values('Éè¼Æ')
insert into testtable1 values('Êг¡')
insert into testtable1 values('ÊÛºó')
/*
½á¹û
id department
1   Éè¼Æ
2   Êг¡
3   ÊÛºó
*/
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'ÕÅÈý')
insert into testtable2 values(1,'ÀîËÄ')
insert into testtable2 values(2,'ÍõÎå')
insert into testtable2 values(3,'ÅíÁù')
insert into testtable2 values(4,'³ÂÆß')
/*
ÓÃÒ»ÌõSQLÓï¾ä£¬ÔõôÏÔʾÈçϽá¹û
id dptID department name
1   1      Éè¼Æ        ÕÅÈý
2   1      Éè¼Æ        ÀîËÄ
3   2      Êг¡        ÍõÎå
4   3      ÊÛºó        ÅíÁù
5   4      ºÚÈË        ³ÂÆß
*/
´ð°¸£º
SELECT testtable2.* , ISNULL(department,'ºÚÈË')
from testtable1 right join testtable2 on testtable2.dptID = testtable1.ID
Ò²×ö³öÀ´Á˿ɱÈÕâ·½·¨ÉÔ¸´ÔÓ¡£
sqlÃæÊÔÌ⣨2£©
ÓбíA£¬½á¹¹ÈçÏ£º
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
ÆäÖУºp_IDΪ²úÆ·ID£¬p_NumΪ²úÆ·¿â´æÁ¿£¬s_idΪ²Ö¿âID¡£ÇëÓÃSQLÓï¾äʵÏÖ½«ÉϱíÖеÄÊý¾ÝºÏ²¢£¬ºÏ²¢ºóµÄÊý¾ÝΪ£º
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
ÆäÖУºs1_idΪ²Ö¿â1µÄ¿â´æÁ¿£¬s2_idΪ²Ö¿â2µÄ¿â´æÁ¿£¬s3_idΪ²Ö¿â3µÄ¿â´æÁ¿¡£Èç¹û¸Ã²úÆ·ÔÚij²Ö¿âÖÐÎÞ¿â´æÁ¿£¬ÄÇô¾ÍÊÇ0´úÌæ¡£
½á¹û£º
select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id
SQLÃæÊÔÌ⣨3£©
1£®´¥·¢Æ÷µÄ×÷Óã¿
  ´ð£º´¥·¢Æ÷ÊÇÒ»ÖÐÌØ


Ïà¹ØÎĵµ£º

SQL begin end ¿é×÷ÓÃ

PL/SQL´æ´¢¹ý³Ì±à³Ì ÊÕ²Ø
  /**author huangchaobiao
*Email:huangchaobiao111@163.com
*/
PL/SQL´æ´¢¹ý³Ì±à³Ì(ÉÏ)
1. OracleÓ¦Óñ༭·½·¨¸ÅÀÀ
´ð£º1) Pro*C/C++/... : CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬±ÈOCI¸ü³£ÓÃ;
2) ODBC
3) OCI: CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬ºÍProCºÜÏàËÆ£¬¸üµ×²ã£¬ºÜÉÙÓÃ;
4) SQLJ ......

SQLÓï¾äµ¼Èëµ¼³ö´óÈ«

/*******  µ¼³öµ½excel
exec master..xp_cmdshell ’bcp settledb.dbo.shanghu out c:\temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""’
/***********  µ¼Èëexcel
select *
from opendatasource( ’microsoft.jet.oledb.4.0’,
  ’data source="c:\test.xls";user ......

SQL Serverµ½µ×ÐèҪʹÓÃÄÄЩ¶Ë¿Ú

SQL ServerÔÚ°²×°µ½·þÎñÆ÷ÉϺó£¬ÓÉÓÚ³öÓÚ·þÎñÆ÷°²È«µÄÐèÒª£¬ËùÒÔÐèÒªÆÁ±ÎµôËùÓв»Ê¹ÓõĶ˿ڣ¬Ö»¿ª·Å±ØÐëʹÓõĶ˿ڡ£ÏÂÃæ¾ÍÀ´½éÉÜÏÂSQL Server 2008ÖÐʹÓõĶ˿ÚÓÐÄÄЩ£º
Ê×ÏÈ£¬×î³£ÓÃ×î³£¼ûµÄ¾ÍÊÇ1433¶Ë¿Ú¡£Õâ¸öÊÇÊý¾Ý¿âÒýÇæµÄ¶Ë¿Ú£¬Èç¹ûÎÒÃÇÒªÔ¶³ÌÁ¬½ÓÊý¾Ý¿âÒýÇ棬ÄÇô¾ÍÐèÒª´ò¿ª¸Ã¶Ë¿Ú¡£Õâ¸ö¶Ë¿ÚÊÇ¿ÉÒÔÐ޸ĵģ¬ÔÚ&ldqu ......

ÈÃÈË·¢¿ñµÄsqlÓï¾ä£¡

StringBuilder Asql = new StringBuilder();
            Asql.Append(" select '' as 'ÐòºÅ', T_Station.µµ°¸ºÅ,T_Station.StationName as '̨վÃû' , ");
            Asql.Append("  ÇøÕ¾ºÅ.ÇøÕ ......

SQLÓïÑÔ»ù´¡£¨3£©

GROUP BY×Ó¾ä
Ö¸¶¨²éѯ½á¹ûµÄ·Ö×éÌõ¼þ
Óï·¨£ºGROUP BY [ALL] group_by_expression_r_r [,n]
      [WITH{CUBE|ROLLUP}]
group_by_expression_r_rÖ¸Ã÷·Ö×éÌõ¼þ£¬Í¨³£ÊÇÒ»¸öÁÐÃû£¬µ«²»ÄÜÊÇÁеıðÃû¡£
ALL·µ»ØËùÓвéѯ½á¹ûµÄ×éºÏ¡£Èç¹ûûÓÐÂú×ãwhere×Ó¾äµÄÊý¾ÝÔòÓÉNULLÖµ¹¹³ÉÊý¾Ý¡£ALLµÄÑ¡Ïî²»Ä ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ