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£®´¥·¢Æ÷µÄ×÷Óã¿
´ð£º´¥·¢Æ÷ÊÇÒ»ÖÐÌØ
Ïà¹ØÎĵµ£º
1.SqlServer·þÎñʹÓÃÁ½¸ö¶Ë¿Ú£ºTCP-1433¡¢UDP-1434¡£ÆäÖÐ1433ÓÃÓÚ¹©SqlServer¶ÔÍâÌṩ·þÎñ£¬1434ÓÃÓÚÏòÇëÇóÕß·µ»ØSqlServerʹÓÃÁËÄǸöTCP/IP¶Ë¿Ú¡£
¿ÉÒÔʹÓÃSQL ServerµÄÆóÒµ¹ÜÀíÆ÷¸ü¸ÄSqlServerµÄĬÈÏTCP¶Ë¿Ú¡£·½·¨ÈçÏ£º
a¡¢´ò¿ªÆóÒµ¹ÜÀíÆ÷£¬ÒÀ´ÎÑ¡Ôñ×ó²à¹¤¾ßÀ¸µÄ“Microsoft SQL Servers - SQL Server×锣¬ ......
/******* µ¼³öµ½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 ......
¶ÔÏóÃüÃûµÄÔ¼¶¨£ºÊý¾Ý¿âÃû.ËùÓÐÕßÃû.¶ÔÏóÃû
Ç°Á½Õß¿ÉÊ¡ÂÔ£¬Ä¬ÈÏÖµÊý¾Ý¿âÊǵ±Ç°Êý¾Ý¿â£¬ËùÓÐÕßÊÇdbo
±ðÃû£ºÊý¾Ý¿âÃû³Æ as Êý¾Ý¿â±íÃû Ö÷ÒªÊÇÔö¼ÓselectÓï¾äµÄ¿É¶ÁÐÔ£¬Èç¹ûÒѾΪÊý¾Ý±íÖƶ¨Á˱ðÃû£¬Ôò
ÔÚÏàÓ¦µÄSQLÓï¾äÖУ¬¶Ô¸ÃÊý¾Ý±íµÄËùÓÐÏÔʾÒýÓö¼ÒªÊ¹ÓñðÃû£¬¶ø²»ÄÜʹÓÃÊý¾Ý±íÃû¡£
selectÓï¾äÊÇÊý¾Ý¼ìË÷ÖÐ×îƵ·±µÄ»î¶ ......
SELECT ±íÃû = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
Ðò = a.colorder,
×Ö¶ÎÃû = a.name,
±êʶ = CASE COLUMNPROPERTY(a.id,a.name, ......