有表RDR1
DOCENTRY DISCPRCNT TARGETTYPE
33932 50 -1
33932 40 -1
33932 60 -1
33932 50 -1
33932 40 15
33932 60 -1
33932 50 -1
33932 40 15
33932 60 15
33933 0 -1
33933 0 15
33933 10 -1
33934 100 -1
33934 100 -1
33934 0 15
33934 10 -1
33935 100 -1
33935 100 -1
33935 0 -1
33935 10 -1
33936 100 15
33936 100 -1
33936 0 -1
33936 10 -1想要得到这样的结果,在相同DOCENTRY下,有DISCPRCNT=100的,也有TARGETTYPE<>-1的,就显示DOCENTRY。结果是33934,33936
DOCENTRY DISCPRCNT TARGETTYPE
33932 50 -1
33932 40 -1
33932 60 -1
33932 50 -1
33932 40 15
33932 60 -1
33932 50 -1
33932 40 15
33932 60 15
33933 0 -1
33933 0 15
33933 10 -1
33934 100 -1
33934 100 -1
33934 0 15
33934 10 -1
33935 100 -1
33935 100 -1
33935 0 -1
33935 10 -1
33936 100 15
33936 100 -1
33936 0 -1
33936 10 -1想要得到这样的结果,在相同DOCENTRY下,有DISCPRCNT=100的,也有TARGETTYPE<>-1的,就显示DOCENTRY。结果是33934,33936
drop table tb
Go
Create table tb([DOCENTRY] int,[DISCPRCNT] int,[TARGETTYPE] int)
Insert tb
select 33932,50,-1 union all
select 33932,40,-1 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,15 union all
select 33933,0,-1 union all
select 33933,0,15 union all
select 33933,10,-1 union all
select 33934,100,-1 union all
select 33934,100,-1 union all
select 33934,0,15 union all
select 33934,10,-1 union all
select 33935,100,-1 union all
select 33935,100,-1 union all
select 33935,0,-1 union all
select 33935,10,-1 union all
select 33936,100,15 union all
select 33936,100,-1 union all
select 33936,0,-1 union all
select 33936,10,-1
Go
Select distinct [DOCENTRY]
from tb t
where exists(select 1
from tb
where [DOCENTRY]=t.[DOCENTRY] and DISCPRCNT=100)
and exists(select 1
from tb
where [DOCENTRY]=t.[DOCENTRY] and TARGETTYPE<>-1)
/*
DOCENTRY
-----------
33934
33936(2 個資料列受到影響)*/
from tb t1 inner join tb t2 on t1.DOCENTRY=t2.DOCENTRY and t1.DISCPRCNT=100 and t2.TARGETTYPE<>-1
group by t1.DOCENTRY
from [Table] a where exists(select 1 from [Table] where docentry=a.docentry and discprcnt<>-1)
where discprcnt=100
from T as A
where exists( select 1 from T where DOCENTRY=A.DOCENTRY and DISCPRCNT=100)
and exists (select 1 from T where DOCENTRY=A.DOCENTRY and TARGETTYPE<>-1)
drop table tb
Go
Create table tb([DOCENTRY] int,[DISCPRCNT] int,[TARGETTYPE] int)
Insert tb
select 33932,50,-1 union all
select 33932,40,-1 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,15 union all
select 33933,0,-1 union all
select 33933,0,15 union all
select 33933,10,-1 union all
select 33934,100,-1 union all
select 33934,100,-1 union all
select 33934,0,15 union all
select 33934,10,-1 union all
select 33935,100,-1 union all
select 33935,100,-1 union all
select 33935,0,-1 union all
select 33935,10,-1 union all
select 33936,100,15 union all
select 33936,100,-1 union all
select 33936,0,-1 union all
select 33936,10,-1
Go
Select t1.DOCENTRY
from tb t1 inner join tb t2 on t1.DOCENTRY=t2.DOCENTRY and t1.DISCPRCNT=100 and t2.TARGETTYPE<>-1
group by t1.DOCENTRY /*DOCENTRY
-----------
33934
33936(所影响的行数为 2 行)*/
from [Table] a where exists(select 1 from [Table] where docentry=a.docentry and TARGETTYPE<>-1)
and discprcnt=100
select distinct docentry
from [Table] a where exists(select 1 from [Table] where docentry=a.docentry and discprcnt<>-1)
and discprcnt=100
intersect
select docentry from [RDR1] where TARGETTYPE<>-1
/*
docentry
-----------
33934
33936(2 行受影响)
*/这样行吗
drop table tb
Go
Create table tb([DOCENTRY] int,[DISCPRCNT] int,[TARGETTYPE] int)
Insert tb
select 33932,50,-1 union all
select 33932,40,-1 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,-1 union all
select 33932,50,-1 union all
select 33932,40,15 union all
select 33932,60,15 union all
select 33933,0,-1 union all
select 33933,0,15 union all
select 33933,10,-1 union all
select 33934,100,-1 union all
select 33934,100,-1 union all
select 33934,0,15 union all
select 33934,10,-1 union all
select 33935,100,-1 union all
select 33935,100,-1 union all
select 33935,0,-1 union all
select 33935,10,-1 union all
select 33936,100,15 union all
select 33936,100,-1 union all
select 33936,0,-1 union all
select 33936,10,-1
Go
SELECT DOCENTRY
FROM tb
WHERE DISCPRCNT=100 OR TARGETTYPE<>-1
GROUP BY DOCENTRY
HAVING MAX(CASE WHEN DISCPRCNT=100 THEN 100
ELSE 0 END
) =100
AND MAX(CASE WHEN TARGETTYPE<>-1 THEN 100
ELSE 0 END
) =100
from tb t1 inner join tb t2 on t1.DOCENTRY=t2.DOCENTRY and t1.DISCPRCNT=100 and t2.TARGETTYPE<>-1
group by t1.DOCENTRY