易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL 先筛选在关联的例子

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 CASE 语句

 Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
    ......

sql语句归纳(oracle)

对日常工作中用到的感觉有用的sql语句做个归纳,用于今后温故知新。
*复制表:
create table tablename  as  select * from table_src;
create table tablename  as  select * from table_src where 1 <> 1; --只复制表结构 ......

尽量不要拼凑Sql语句,用参数来防注入

    如果是类似"select   *   from   user   where   uid="+uid +"  and   pwd="+pwd 很容易出问题
    使用   SQLParamenter      
    把你的SQL语句写成   类似存储过程   ......

整理比较全的Access SQL注入参考

 Access SQL注入参考
版本 0.2.1
(最近更新 10/10/2007)
原作者不详
 
描述 SQL查询及注释
注释符 Access中没有专门的注释符号.因此"/*", "--"和"#"都没法使用.但是可以使用空字符"NULL"(%00)代替:
' UNION SELECT 1,1,1 from validTableName%00
 
语法错误信息 "[Microsoft][Driver ODBC Micros ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号