select ID,Item1,Item2,Item3,Item4
into #Temp
from (
select ID='200910',Item1='A',Item2='B',Item3='C',Item4='T1'
union all
select ID='200910',Item1='',Item2='B',Item3='C' ,Item4='G2'
union all
select ID='200910',Item1='A',Item2='D',Item3='C' ,Item4='T3'
union all
select ID='200910',Item1='A',Item2='F',Item3='C' ,Item4='H4'
union all
select ID='200911',Item1='A',Item2='D',Item3='C' ,Item4='T5'
union all
select ID='200911',Item1='A',Item2='',Item3='E' ,Item4='T6'
union all
select ID='200911',Item1='',Item2='',Item3='C' ,Item4='T7'
) Tab
SELECT A.*
into #T1
from #Temp AS A
LEFT JOIN #Temp AS B
ON A.ID = B.ID
AND CASE WHEN ISNULL(NULLIF(A.Item1,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(A.Item2,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(A.Item3,''),'') = '' THEN 1 ELSE 0 END
< CASE WHEN ISNULL(NULLIF(B.Item1,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(B.Item2,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(B.Item3,''),'') = '' THEN 1 ELSE 0 END
AND ISNULL(NULLIF(B.Item1,''),A.Item1) = A.Item1
AND ISNULL(NULLIF(B.Item2,''),A.Item2) = A.Item2
AND ISNULL(NULLIF(B.Item3,''),A.Item3) = A.Item3
WHERE B.ID IS NULL
select ID,Item1,Item2,Item3
into #T2
from (
select ID='200910',Item1='A',Item2='',Item3='C'
union all
select ID='200910',Item1='A',Item2='D',Item3='C'
union all
select ID='200911',Item1='A',Item2='H',Item3='C'
union all
select ID='200911',Item1='',Item2='',Item3='C'
) Tab
select * from #Temp
select * from #T1
select * from #T2
select A.*,B.* from #T2 A
left join #T1 B
ON A.ID=B.ID
AND ISNULL(NULLIF(B.Item1,''),A.Item1) = A.Item1
AND ISNULL(NULLIF(B.Item2,''),A.Item2) = A.Item2
AND
¡¡¡¡º¯ÊýÊÇÒ»ÖÖÓÐÁã¸ö»ò¶à¸ö²ÎÊý²¢ÇÒÓÐÒ»¸ö·µ»ØÖµµÄ³ÌÐò¡£ÔÚSQLÖÐOracleÄÚ½¨ÁËһϵÁк¯Êý£¬ÕâЩº¯Êý¶¼¿É±»³ÆÎªSQL»òPL/SQLÓï¾ä£¬º¯ÊýÖ÷Òª·ÖΪÁ½´óÀࣺ
¡¡¡¡ µ¥Ðк¯Êý
¡¡¡¡ ×麯Êý
¡¡¡¡±¾ÎĽ«ÌÖÂÛÈçºÎÀûÓõ¥Ðк¯ÊýÒÔ¼°Ê¹ÓùæÔò¡£
¡¡¡¡SQLÖеĵ¥Ðк¯Êý
¡¡¡¡SQLºÍPL/SQLÖÐ×Ô´øºÜ¶àÀàÐ͵ĺ¯Êý£¬ÓÐ×Ö·û¡¢Êý×Ö¡¢ÈÕÆÚ¡ ......
Èç¹ûÊÇÀàËÆ"select * from user where uid="+uid +" and pwd="+pwd ºÜÈÝÒ׳öÎÊÌâ
ʹÓà SQLParamenter
°ÑÄãµÄSQLÓï¾äд³É ÀàËÆ´æ´¢¹ý³Ì ......
·¢²¼Ò»¸öʵÓÃС¹¤¾ß£¬¿ÉÒԺܷ½±ãµÄÔÚÊý¾Ý¿âÖÐÕÒµ½°üº¬Ö¸¶¨×Ö·û´®µÄÊý¾Ý±íÃû¼°ÏàÓ¦¼Ç¼£º
/*
¹¦ÄÜ£º²éѯÊý¾Ý¿âÖаüº¬Ö¸¶¨×Ö·û´®µÄÊý¾Ý±íÃû¼°ÏàÓ¦¼Ç¼
×÷Õߣº³Â¼ÓÅô chjpeng#163.com
ÈÕÆÚ£º2009-08-17
*/
declare @key varchar(30)
set @key = 'test' --Ìæ»»ÎªÒª²éÕÒµÄ×Ö·û´®
DECLARE @ ......
ÒÔǰÔÚ°²×°sqlµÄʱºò£¬Èç´ËÌáʾ£¬ÎÒÖ»ÒªÖØÐÂÆô¶¯¼´¿É£¬¿ÉÊǽñÌìÖØÐÂÆô¶¯ÁËN´Î¼ÆËã»ú£¬ÎÊÌâÈ´Ë¿ºÁûÓнâ¾ö£¬ÒÀÈ»ÌáʾÕâÑùµÄ»°¡£“ÒÔǰµÄij¸ö³ÌÐò°²×°ÒÑÔÚ°²×°¼ÆËã»úÉÏ´´½¨¹ÒÆðµÄÎļþ²Ù×÷¡£ÔËÐа²×°³ÌÐò֮ǰ±ØÐëÖØÐÂÆô¶¯¼ÆËã»ú¡£” Ö»ºÃgoogleÒÔÏ£¬×îÖÕµÃÖªÊǰ²×°³ÌÐòÔÚÏÈǰµÄ°²×°¹ý³ÌÖÐ ......
1.ʹÓà ESCAPE ¹Ø¼ü×Ö¶¨ÒåתÒå·û¡£ ÔÚģʽÖУ¬µ±×ªÒå·ûÖÃÓÚͨÅä·û֮ǰʱ£¬¸ÃͨÅä·û¾Í½âÊÍΪÆÕͨ×Ö·û¡£ÀýÈ磬ҪËÑË÷ÔÚÈÎÒâλÖðüº¬×Ö·û´® 5% µÄ×Ö·û´®£¬ÇëʹÓ㺠WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
2.ESCAPE 'escape_character' ÔÊÐíÔÚ×Ö·û´®ÖÐËÑË÷ͨÅä·û¶ø²»Êǽ«Æä×÷ΪͨÅä·ûʹÓᣠescape_character ÊÇ·ÅÔÚͨÅä·ûǰ ......