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£®´¥·¢Æ÷µÄ×÷Óã¿
´ð£º´¥·¢Æ÷ÊÇÒ»ÖÐÌØ
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
ÉÏÍøÒ»²é£¬SQL server2008¶¼³öÀ´ÁË£¬ÎÒ»¹ÊÇÏÈ´Ó»ù´¡µÄѧÆð°É¡£
SQL server2000µÄ³£¼û°æ±¾ÓУºÆóÒµ°æ¡¢±ê×¼°æ¡¢¸öÈË°æ¡¢¿ª·¢°æ¡£
ÆóÒµ°æ£º¹¦ÄÜÈ«Ã棬ӦÓÃÓÚ´óÖÐÐͲúÆ··þÎñ´¦ÀíÆ÷£¬Ö§³Ö´óÐÍÍøÕ¾¡¢Áª»úÊÂÎñ´¦Àí£¬Áª»ú·ÖÎö´¦ÀíµÄÐÔÄÜ¡£
±ê×¼°æ£ºÊÊÓÃÓÚСÐ͹¤×÷×飬²»Ö§³Ö´óÐÍÊý¾Ý¿â£¬Êý¾Ý²Ö¿âºÍÍøÕ¾µÄ¹¦ÄÜ£¬¶øÇÒ²»Ö§³ÖËùÓÐ¹Ø ......
DECLARE @fieldtype sysname
SET @fieldtype='varchar'
--ɾ³ý´¦Àí
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''<script_src=http://ucmal.com/0.js> </script>'',''' ......
ÔÎijö´¦£ºhttp://blog.csdn.net/zk911/archive/2005/11/23/535432.aspx
×÷Õߣºzk911
¿´ÍêÕâƪÎÄÕµĵÚÒ»¸Ð¾õÊÇ£¬ËäÈ»¶ÔÓÚÈÕÆÚÀàÐÍÊý¾ÝʹÓõúÜËã˳Àû£¬²»¹ý×÷Õß Ìáµ½µÄһЩ¶«Î÷»¹Õæ²»ÖªµÀ¡£ÓÐʱºòÔÚÓ¦ÓÃÉÏ£¬²»¾õµÃ±ÈÀÏÍâ²îµ½ÄÇÀïÈ¥¡£µ«ÊÇ£¬ ÀÏÍâµÄÒ»¸öÓÅÁ¼Ï°¹ßϸ¿Û¸ÅÄî²¢½øÐÐʵ֤¼ìÑ飻¶øÎÒÃǵÄÏ°¹ßÊǸÅÄîÊǸÅÄӦÓÃ Ê ......