一个表AA
id name ph sz
23 aa 12 yys
23 aa 12 grs
23 aa 12 jys
24 bb 13 yys
25 cc 14 grs
26 dd 15 yys
26 dd 15 grs
27 ee 16 yys
现要求当ph一至时出现yys 的项就要有grs,现要求查出当有yys时没出现grs的项
查询结果是:
id name ph sz
24 bb 13 yys
27 ee 16 yys
要实现这样的查询怎么实现呀
id name ph sz
23 aa 12 yys
23 aa 12 grs
23 aa 12 jys
24 bb 13 yys
25 cc 14 grs
26 dd 15 yys
26 dd 15 grs
27 ee 16 yys
现要求当ph一至时出现yys 的项就要有grs,现要求查出当有yys时没出现grs的项
查询结果是:
id name ph sz
24 bb 13 yys
27 ee 16 yys
要实现这样的查询怎么实现呀
declare @AA table(id int,name varchar(10),ph int,sz varchar(10))
insert @AA
select 23, 'aa', 12, 'yys' union all
select 23, 'aa', 12, 'grs' union all
select 23, 'aa', 12, 'jys' union all
select 24, 'bb', 13, 'yys' union all
select 25, 'cc', 14, 'grs' union all
select 26, 'dd', 15, 'yys' union all
select 26, 'dd', 15, 'grs' union all
select 27, 'ee', 16, 'yys'----查询
select * from @AA as a
where a.sz = 'yys' and not exists(select 1 from @AA where ph = a.ph and sz = 'grs')/**结果
id name ph sz
---------------------------------------------------
24 bb 13 yys
27 ee 16 yys
/
where a.sz='yys'
and a.id not in (select id from AA where ph=a.ph and sz='grs')
declare @AA table(id int,name varchar(10),ph int,sz varchar(10))
insert @AA
select 23, 'aa', 12, 'yys' union all
select 23, 'aa', 12, 'grs' union all
select 23, 'aa', 12, 'jys' union all
select 24, 'bb', 13, 'yys' union all
select 25, 'cc', 14, 'grs' union all
select 26, 'dd', 15, 'yys' union all
select 26, 'dd', 15, 'grs' union all
select 27, 'ee', 16, 'yys'----查询
select * from @AA a
where a.sz = 'yys' and id not in (select id from @aa where sz='grs' )(所影响的行数为 8 行)id name ph sz
----------- ---------- ----------- ----------
24 bb 13 yys
27 ee 16 yys(所影响的行数为 2 行)