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语句写成 类似存储过程 ......
