34,594
社区成员
发帖
与我相关
我的任务
分享
select count(*) as 常住人口含学生 from 原始 where A102<>2 and A114=1
select count(*) as 学生 from 原始 where A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2
--算了,楼主看下面这个写法吧
select count(*) as 常住人口含学生
from 原始 where A102<>2 and A114=1
and not (A117=1 and A118>=5 and A113<2)
select * from 调查表 a where A102<>2 and A114=1
and not exists(select * from 调查表 b
where a.主键字段1=b.主键字段1 and a.主键字段2=b.主键字段2...
and A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2)
ps:上下两个表必须关联进行比对,所以这样--上面的或者错了,应该是这样
select * from 调查表 where A102<>2 and A114=1
and 主键字段 not in
(select 主键字段 from 调查表 where A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2)
select * from 调查表 a where A102<>2 and A114=1
and not exists(select * from 调查表 a where a.主键字段=b.主键字段 and A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2)
--或者
select * from 调查表 where A102<>2 and A114=1
and 主键字段 not in
(select 主键字段 from 调查表 where a.主键字段=b.主键字段 and A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2)
select * from 调查表 where A102<>2 and A114=1 and not exists(select * from 调查表 where A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2)
--sql2005/2008,except(第一个集合中存在,但是不存在于第二个集合中的数据);
INTERSECT(两个集合中都存在的数据)
--在2000中没有,要用not eexists
select count(*) as 常住人口含学生 from 原始 where A102<>2 and A114=1
select count(*) as 学生 from 原始 where A102<>2 and A114=1 and A117=1 and A118>=5 and A113<2
[code=SQL]
1,delete from tab1 where exists(select 1 from tab2 where COL1=tab1.COL1 AND ...)