Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 ServerĬÈϵÄ1433¶Ë¿Ú

1.SqlServer·þÎñʹÓÃÁ½¸ö¶Ë¿Ú£ºTCP-1433¡¢UDP-1434¡£ÆäÖÐ1433ÓÃÓÚ¹©SqlServer¶ÔÍâÌṩ·þÎñ£¬1434ÓÃÓÚÏòÇëÇóÕß·µ»ØSqlServerʹÓÃÁËÄǸöTCP/IP¶Ë¿Ú¡£
¿ÉÒÔʹÓÃSQL ServerµÄÆóÒµ¹ÜÀíÆ÷¸ü¸ÄSqlServerµÄĬÈÏTCP¶Ë¿Ú¡£·½·¨ÈçÏ£º
a¡¢´ò¿ªÆóÒµ¹ÜÀíÆ÷£¬ÒÀ´ÎÑ¡Ôñ×ó²à¹¤¾ßÀ¸µÄ“Microsoft SQL Servers - SQL Server×锣¬ ......

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ÓïÑÔ»ù´¡£¨1£©

¶ÔÏóÃüÃûµÄÔ¼¶¨£ºÊý¾Ý¿âÃû.ËùÓÐÕßÃû.¶ÔÏóÃû
Ç°Á½Õß¿ÉÊ¡ÂÔ£¬Ä¬ÈÏÖµÊý¾Ý¿âÊǵ±Ç°Êý¾Ý¿â£¬ËùÓÐÕßÊÇdbo
±ðÃû£ºÊý¾Ý¿âÃû³Æ as Êý¾Ý¿â±íÃû Ö÷ÒªÊÇÔö¼ÓselectÓï¾äµÄ¿É¶ÁÐÔ£¬Èç¹ûÒѾ­ÎªÊý¾Ý±íÖƶ¨Á˱ðÃû£¬Ôò
ÔÚÏàÓ¦µÄSQLÓï¾äÖУ¬¶Ô¸ÃÊý¾Ý±íµÄËùÓÐÏÔʾÒýÓö¼ÒªÊ¹ÓñðÃû£¬¶ø²»ÄÜʹÓÃÊý¾Ý±íÃû¡£
selectÓï¾äÊÇÊý¾Ý¼ìË÷ÖÐ×îƵ·±µÄ»î¶ ......

SQL SERVER 2005 »ñÈ¡±í½á¹¹ÐÅÏ¢

SELECT ±íÃû   = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
       Ðò     = a.colorder,
       ×Ö¶ÎÃû = a.name,
       ±êʶ   = CASE COLUMNPROPERTY(a.id,a.name, ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ