MyException - 我的异常网
当前位置:我的异常网» Sql Server » 这条SQL语句该如何写

这条SQL语句该如何写

www.MyException.Cn  网友分享于:2013-12-27  浏览:6次
这条SQL语句该怎么写
有两个表
table1   (name,zh,zj)
table2   (name,zh,......)

我现在要找出如下记录:
条件:   表一name没在表二中,或者name在表二中,zh不同的记录


------解决方案--------------------
select * from table1 where name not in (select a.name from table1 a inner join table2 b on a.name=b.name)c
------解决方案--------------------
select * from table1 where not exists(select 1 from table2 where table2.name=table1.name)
union all
select * from table1 where exists(select 1 from table2 where table2.name=table1.name and table2.zh <> table1.zh)
------解决方案--------------------
上面加个条件a.zh=b.zh
------解决方案--------------------
select a.*
from table1 a
left join table2 b
on a.name=b.name
where b.name is null or a.zh <> b.zh
------解决方案--------------------
SELECT DISTINCT a.zh FROM table1 a , table2 b WHERE a.name=b.name
------解决方案--------------------
select distinct zh from table1
union
select distinct table2.zh from table2,table1
where table1.zh <> table2.zh

不知道你是不是这意思?
------解决方案--------------------
select a.* from table1 a where a.name not in(select name from table2)
union
select a.* from table1 a where exists(select 1 from table2 where table2.name=a.name and table2.zh <> a.zh)
------解决方案--------------------
--用exists试一下,呵呵
select * from table1 t1 where not exists(select * from table2 t2 where t1.name=t2.name and t1.zh=t2.zh)

------解决方案--------------------
--try

select * from table1 as tmp
where exists(select 1 from table2 where (name <> tmp.name) or (name=tmp.name and zh <> tmp.zh) )

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有