To :bernice99(宝宝) 我改成: 不好意思哦,我刚才搞错了,现在可以查出来了,但是结果和select * from table1 一样啊???
select a.*,b.result from tb1 a inner join tb2 b on a.jgnum=b.jgnum where b.result='pass'
select a.* from table1 a left join( select distinct jcnum from table1 a where not exists( select * from table2 where jgnum=a.jgnum and result='pass') )b on a.jcnum=b.jcnum where b.jcnum is not null
TO :hdhai9451请问如果我的result不一定是pass呢,也许是其它值
--查反了 select a.* from table1 a left join( select distinct jcnum from table1 a where not exists( select * from table2 where jgnum=a.jgnum and result='pass') )b on a.jcnum=b.jcnum where b.jcnum is null
--测试--测试数据 create table table1(id int,jcnum int,ypnum varchar(10),jgnum varchar(10)) insert table1 select 1,200401,'sp01','jg1' union all select 1,200401,'sp02','jg2' union all select 1,200401,'sp03','jg3' union all select 2,200402,'sp04','jg4' union all select 2,200402,'sp04','jg5' union all select 3,200403,'sp05','jg6' union all select 3,200403,'sp06','jg7' union all select 3,200403,'sp06','jg8'create table table2(jgnum varchar(10),result varchar(10)) insert table2 select 'jg1','pass' union all select 'jg2','pass' union all select 'jg4','pass' union all select 'jg6','pass' union all select 'jg7','pass' union all select 'jg8','pass' go--查询 select a.* from table1 a left join( select distinct jcnum from table1 a where not exists( select * from table2 where jgnum=a.jgnum and result='pass') )b on a.jcnum=b.jcnum where b.jcnum is null go--删除测试 drop table table1,table2/*--测试结果id jcnum ypnum jgnum ----------- ----------- ---------- ---------- 3 200403 sp05 jg6 3 200403 sp06 jg7 3 200403 sp06 jg8(所影响的行数为 3 行)--*/
declare @tb1 table ([id] int,jcnum char (10),ypnum char(10),jgnum char(10)) insert into @tb1 select 1, '200401', 'sp01', 'jg1' union all select 1, '200401', 'sp02', 'jg2' union all select 1, '200401', 'sp03', 'jg3' union all select 2, '200402', 'sp04', 'jg4' union all select 2, '200402', 'sp04', 'jg5' union all select 3, '200403', 'sp05', 'jg6' union all select 3, '200403', 'sp06', 'jg7' union all select 3, '200403', 'sp06', 'jg8'declare @tb2 table (jgnum char(10),result char(10)) insert into @tb2 select 'jg1', 'pass' union all select 'jg2', 'pass' union all select 'jg4', 'pass' union all select 'jg6', 'pass' union all select 'jg7', 'pass' union all select 'jg8', 'pass' select t1.* from @tb1 t1 inner join (select a.[id],a.jcnum,mycount=count(a.jgnum),mycount2=count(b.result) from @tb1 a left join @tb2 b on a.jgnum=b.jgnum group by a.[id],a.jcnum)t2 on t1.[id]=t2.[id] and t1.jcnum=t2.jcnum and t2.mycount=t2.mycount2/* id jcnum ypnum jgnum ----------- ---------- ---------- ---------- 3 200403 sp05 jg6 3 200403 sp06 jg7 3 200403 sp06 jg8 (所影响的行数为 3 行) */
select * from table1 a where a.jcnum not in(select a.jcnum from (select a.*,b.result from table1 a left join table2 b on a.jgnum=b.jgnum) a where a.result is null)
select * from table1 a where a.jcnum not in(select a.jcnum from (select a.*,b.result from table1 a left join table2 b on a.jgnum=b.jgnum) a where a.result is null)id jcnum ypnum jgnum ----------- ----------- ---------- ---------- 3 200403 sp05 jg6 3 200403 sp06 jg7 3 200403 sp06 jg8(所影响的行数为 3 行)
查询分析器中提示:服务器: 消息 107,级别 16,状态 3,行 1
列前缀 'table1' 与查询中所用的表名或别名不匹配。
服务器: 消息 107,级别 16,状态 1,行 1
列前缀 'table2' 与查询中所用的表名或别名不匹配。
我改成:
不好意思哦,我刚才搞错了,现在可以查出来了,但是结果和select * from table1 一样啊???
on a.jgnum=b.jgnum
where b.result='pass'
from table1 a
left join(
select distinct jcnum from table1 a
where not exists(
select * from table2
where jgnum=a.jgnum and result='pass')
)b on a.jcnum=b.jcnum
where b.jcnum is not null
select a.*
from table1 a
left join(
select distinct jcnum from table1 a
where not exists(
select * from table2
where jgnum=a.jgnum and result='pass')
)b on a.jcnum=b.jcnum
where b.jcnum is null
create table table1(id int,jcnum int,ypnum varchar(10),jgnum varchar(10))
insert table1 select 1,200401,'sp01','jg1'
union all select 1,200401,'sp02','jg2'
union all select 1,200401,'sp03','jg3'
union all select 2,200402,'sp04','jg4'
union all select 2,200402,'sp04','jg5'
union all select 3,200403,'sp05','jg6'
union all select 3,200403,'sp06','jg7'
union all select 3,200403,'sp06','jg8'create table table2(jgnum varchar(10),result varchar(10))
insert table2 select 'jg1','pass'
union all select 'jg2','pass'
union all select 'jg4','pass'
union all select 'jg6','pass'
union all select 'jg7','pass'
union all select 'jg8','pass'
go--查询
select a.*
from table1 a
left join(
select distinct jcnum from table1 a
where not exists(
select * from table2
where jgnum=a.jgnum and result='pass')
)b on a.jcnum=b.jcnum
where b.jcnum is null
go--删除测试
drop table table1,table2/*--测试结果id jcnum ypnum jgnum
----------- ----------- ---------- ----------
3 200403 sp05 jg6
3 200403 sp06 jg7
3 200403 sp06 jg8(所影响的行数为 3 行)--*/
insert into @tb1
select 1, '200401', 'sp01', 'jg1' union all
select 1, '200401', 'sp02', 'jg2' union all
select 1, '200401', 'sp03', 'jg3' union all
select 2, '200402', 'sp04', 'jg4' union all
select 2, '200402', 'sp04', 'jg5' union all
select 3, '200403', 'sp05', 'jg6' union all
select 3, '200403', 'sp06', 'jg7' union all
select 3, '200403', 'sp06', 'jg8'declare @tb2 table (jgnum char(10),result char(10))
insert into @tb2
select 'jg1', 'pass' union all
select 'jg2', 'pass' union all
select 'jg4', 'pass' union all
select 'jg6', 'pass' union all
select 'jg7', 'pass' union all
select 'jg8', 'pass'
select t1.* from @tb1 t1 inner join (select a.[id],a.jcnum,mycount=count(a.jgnum),mycount2=count(b.result) from @tb1 a left join @tb2 b on a.jgnum=b.jgnum group by a.[id],a.jcnum)t2 on t1.[id]=t2.[id] and t1.jcnum=t2.jcnum and t2.mycount=t2.mycount2/*
id jcnum ypnum jgnum
----------- ---------- ---------- ----------
3 200403 sp05 jg6
3 200403 sp06 jg7
3 200403 sp06 jg8 (所影响的行数为 3 行)
*/
----------- ----------- ---------- ----------
3 200403 sp05 jg6
3 200403 sp06 jg7
3 200403 sp06 jg8(所影响的行数为 3 行)