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£®´¥·¢Æ÷µÄ×÷Óã¿
´ð£º´¥·¢Æ÷ÊÇÒ»ÖÐÌØ
Ïà¹ØÎĵµ£º
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 ......
/******* µ¼³öµ½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 2008ÖÐʹÓõĶ˿ÚÓÐÄÄЩ£º
Ê×ÏÈ£¬×î³£ÓÃ×î³£¼ûµÄ¾ÍÊÇ1433¶Ë¿Ú¡£Õâ¸öÊÇÊý¾Ý¿âÒýÇæµÄ¶Ë¿Ú£¬Èç¹ûÎÒÃÇÒªÔ¶³ÌÁ¬½ÓÊý¾Ý¿âÒýÇ棬ÄÇô¾ÍÐèÒª´ò¿ª¸Ã¶Ë¿Ú¡£Õâ¸ö¶Ë¿ÚÊÇ¿ÉÒÔÐ޸ĵģ¬ÔÚ&ldqu ......
StringBuilder Asql = new StringBuilder();
Asql.Append(" select '' as 'ÐòºÅ', T_Station.µµ°¸ºÅ,T_Station.StationName as '̨վÃû' , ");
Asql.Append(" ÇøÕ¾ºÅ.ÇøÕ ......
GROUP BY×Ó¾ä
Ö¸¶¨²éѯ½á¹ûµÄ·Ö×éÌõ¼þ
Óï·¨£ºGROUP BY [ALL] group_by_expression_r_r [,n]
[WITH{CUBE|ROLLUP}]
group_by_expression_r_rÖ¸Ã÷·Ö×éÌõ¼þ£¬Í¨³£ÊÇÒ»¸öÁÐÃû£¬µ«²»ÄÜÊÇÁеıðÃû¡£
ALL·µ»ØËùÓвéѯ½á¹ûµÄ×éºÏ¡£Èç¹ûûÓÐÂú×ãwhere×Ó¾äµÄÊý¾ÝÔòÓÉNULLÖµ¹¹³ÉÊý¾Ý¡£ALLµÄÑ¡Ïî²»Ä ......