求一查询语句，困扰了许久

Question

以是表结构和数据：
id         a_id         b_id        flag001        a001         b001          Y
002        a001         b002          N
003        a002         b001          Y
004        a002         b002          Y其中 id,a_id,b_id是主键，
现在要查a_id的个数，要求是：查flag是Y的，重复的算一个，如果相同a_id的不同记录中有N的，不计算入个数。
比如上面的记录中，a001记录中有一条记录flag是N，所以不算，a002中有两条记录，并且falg都是Y,算一条。
则结果应只有1条.
请问这个查询语句该如何写，或者如果写不出来，有没有什么解决方案。
分不多，保证及时结贴

Answer 1

select count(distinct aid) from table
where flag<>'N'

Answer 2

如果你的数据只是上面的举例的话， bjt_的代码已经可以满足了。
如果有其它，你再多举些例子。

Answer 3

不对啊，bjt_ 给的方法是将查出两条，但要求是如果a_id对应的多条记录的flag中有一个是N，就一个也不能算，只有当flag全是Y时，才能算一条的。

Answer 4

我想到了一个办法，做两个查询，一个查出所有不重复的a-id个数，一个查出a-id对应的flag是N的个数。
select count(distinct a_id) from table
select count(distinct a_id) from table where flag = 'N'
这两个结果相减不就是想要的结果了吗？不知道这样想对不对，别人是不是有更好的写法或解决方案呢？

Answer 5

select count(distinct aid) 
from table A
where not exists (select 1
                  from table B
                  where A.aid = B.aid
                    and B.flag <> 'N')

Answer 6

select count(distinct aid) 
from table A
where not exists (select 1
                  from table B
                  where A.aid = B.aid
                    and B.flag <> 'N')

Answer 7

SELECT COUNT(DISTINCT A.A_ID) AS AID
  FROM table_name A
 WHERE NOT EXISTS (SELECT 1
                        FROM table_name B
                       WHERE A.A_ID = B.A_ID
                         AND B.FLAG = 'N ')

Answer 8

能给讲讲select 1 
                  from table B 
                  where A.aid = B.aid 
                    and B.flag  < >  'N '
完全不明白。

Answer 9

create table TabY(id varchar2(10),a_id varchar2(10),b_id varchar2(10), flag char(1));
insert into TabY
select '001','a001','b001','Y' from dual
union all
select '002','a001','b002','N' from dual
union all
select '003','a002','b001','Y' from dual
union all
select '004','a002','b002','Y' from dual;select  count(A.a_id) as toalRecords from TabY A
where not exists (select 1 from TabY B where id>A.id and B.flag<>'N' and B.B_ID>A.b_Id) and A.Flag<>'N'
测试结果：
        toalRecords 
  1 1
楼主，试试

Answer 10

相减的效率高
select aid from table group by aid
minus 
select aid from table where flag = 'N' group by aid

Answer 11

SELECT COUNT(DISTINCT A.A_ID) AID
  FROM table_name A
 WHERE a.FLAG =  'Y'
  and NOT EXISTS (SELECT 1
                        FROM table_name B
                       WHERE A.A_ID = B.A_ID
                         AND B.FLAG =  'N  ')

Answer 12

select count(distinct aid)  
from table A 
where not exists (select 1 
                  from table B 
                  where A.aid = B.aid 
                    and B.flag  < >  'N ')写错了 应该是 = 才对select count(distinct aid)  
from table A 
where not exists (select 1 
                  from table B 
                  where A.aid = B.aid 
                    and B.flag = 'N ')意思是取出table中aid的不重复数量，其中aid不存在有flag='N'

Answer 13

select * from TabY a,TabY b
where a.a_id=b.a_id and a.b_id<>b.b_id and a.flag=b.flag;这个当模版,求a_id个数的话:select count(distinct(a.a_id)) from TabY a,TabY b
where a.a_id=b.a_id and a.b_id<>b.b_id and a.flag=b.flag;试下,有问题继续跟

Answer 14

少了个条件select * from TabY a,TabY b
where a.a_id=b.a_id and a.b_id<>b.b_id and a.flag=b.flag and a.flag='Y';这个当模版,求a_id个数的话: select count(distinct(a.a_id)) from TabY a,TabY b
where a.a_id=b.a_id and a.b_id<>b.b_id and a.flag=b.flag and a.flag='Y';

Answer 15

select count(distinct aid) from table where aid not exists (select aid from table where flag ='N')

Answer 16

嗬嗬,为什么一定用到not exists.
变相的考虑一下问题，
假如group by a_id的话,min(flag) 要么是n,要么是Y,用having去掉n的，不就出来了吗？是不是比exist的效率高一些呢？

Answer 17

我觉得是
select count(*) from talbename
where flag="Y"
group by a_id

Answer 18

18楼说的有道理~~select count(a_id) from talbename group by a_id having min(flag)='Y';

Answer 19

select count(distinct a_id) from TABLE where a_id NOT IN(select a_id from TABLE where flag = 'N')
已经验证

Answer 20

select count(*) from (select a_id,flag from TABLE where flag ='Y' union select a_id,flag from TABLE where flag ='Y' ) tmp_TB

Answer 21

对了,“"Y "”中的“"”要写成单引号“'”，sorry...

Answer 22

嗬嗬,为什么一定用到not exists. 
变相的考虑一下问题， 
假如group by a_id的话,min(flag) 要么是n,要么是Y,用having去掉n的，不就出来了吗？ 是不是比exist的效率高一些呢？ 
我很喜欢你的思考方式，
请问如何得知这样做的效率要比exist高呢

Answer 23

ycy1984 你自己加些测试数据试试可行不

Answer 24

xiaoxiao1984  支持你的写法清晰明白，佩服

Answer 25

18的兄弟，你用函数就一定效率比 not exists 高吗？ 要看具体的数据表中的数据定吧

Answer 26

select count(distinct aid) from yy where aid not in (select aid from yy where flag='n');

Answer 27

liuzi123兄弟，其实，我们可以比较一下的。
数据越多，我觉得exist的效率越差。这次用到的a_id也不是数据库的主键。我这里没有测试环境，你那里可不可以做一个百万条左右的测试表呢？测试一下速度。
在a_id没有索引和有索引的两个情况下。

Answer 28

class_song：想知道效率的话，只有看执行计划了。而且，这次用到的表，假如只是在没有索引的a_id上面的话，我觉得，exist的做法会比较慢。

Answer 29

select count (*) from (select a_id from taby group by a_id 
minus  
select a_id from taby where flag =  'N ' group by a_id)楼主试试

Answer 30

22楼的问题：
select   count(*)   from   (select   a_id,flag   from   TABLE   where   flag   = "Y "   union   select   a_id,flag   from   TABLE   where   flag   = "Y "   )   tmp_TB这么说，我们假如只有一条记录的话，选出来是几呢？假如有两条记录呢？3条呢？完全一样的条件，选择出来，union起来。是区分不开N的记录的。

Answer 31

qiubite520：
既然你都已经用了group by,为什么不用having,而选择用minus呢？赫赫。

Answer 32

select sum(Y*N) From (
  select A_ID, max((case when Flag ='Y' then 1 else -99 end )) Y ,
                         min((case when Flag ='N' then 0 else 1  end )) N
  From taby 
  Group by A_ID ) d呵呵,我也来写写..

Answer 33

1.建立测试表
CREATE TABLE CROATIA.T1
(
    ID                             VARCHAR2(3) NOT NULL,
    A_ID                           VARCHAR2(4) NOT NULL,
    B_ID                           VARCHAR2(4) NOT NULL,
    FLAG                           CHAR(1),
    CONSTRAINT PK_T1 PRIMARY KEY (ID, A_ID, B_ID) USING INDEX
)2。加入100万条数据。
begin
 for i in 1..500 loop
   for j in 0..999 loop
     if j mod 3 = 0 then
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B001','Y');
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B002','Y');
     elsif  j mod 3 = 1 then
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B001','Y');
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B002','N');
     else
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B001','N');
insert into t1 values (trim(to_char(i,'000')),'A' || trim(to_char(j,'000')),'B002','N');
end if;
   end loop;
 commit;
 end loop;
end;
/3。执行计划。A。SELECT COUNT(DISTINCT A.A_ID) AID FROM T1 WHERE a.FLAG =   'Y' and NOT EXISTS (SELECT 1  FROM T1 B  WHERE A.A_ID = B.A_ID AND B.FLAG =   'N')  COST BYTE
 select statement optimizer_mode _ALL_ROWA 2835 14
  sort group by 14
    hash join right anti 2835 14
      table access full T1 799 3110093
      table access full T1 799 3113705B。select sum(count(distinct a_id)) from T1 group by a_id having min(flag)= 'Y';
COST BYTE
 select statement optimizer_mode _ALL_ROWA 975 7
  sort aggregate 975 7
   filter
    sort group by 975 7
      table access full T1 695 62237984.执行时间  1* SELECT COUNT(DISTINCT A.A_ID) AID FROM T1 a WHERE a.FLAG =   'Y' and NOT EXISTS (SELECT 1  FROM T1 B  WHERE A.A_ID = B.A_ID AND B.FLAG =   'N')
SQL> 
SQL> /       AID
----------
       334经过: 00:00:48.42SQL> 
SQL> select sum(count(distinct a_id)) from T1 group by a_id having min(flag)= 'Y';SUM(COUNT(DISTINCTA_ID))
------------------------
                     334经过: 00:00:10.87
SQL> 比exist的好处就是少了一次全表扫描，时间大约只有其的22%。

Answer 34

5。建立了index以后的执行结果。
SQL> SELECT COUNT(DISTINCT A.A_ID) AID FROM T1 a WHERE a.FLAG =   'Y' and NOT EXISTS (SELECT 1  FROM T1 B  WHERE A.A_ID = B.A_ID AND B.FLAG =   'N');       AID
----------
       334经过: 00:00:41.15
SQL>  select sum(count(distinct a_id)) from T1 group by a_id having min(flag)= 'Y';SUM(COUNT(DISTINCTA_ID))
------------------------
                     334经过: 00:00:07.37

Answer 35

对Index执行了两次扫描，这个就是差别的所在了。

Answer 36

建立index以后，exist的cost大约1868，group by的大约351。select count(*) from (select distinct a_id from t1 minus select distinct a_id from t1 where flag='N');
这样的写法，大约需要6526，是最差的。

Answer 37

select aid from table group by aid 
minus  
select aid from table where flag =  'N ' group by aid这个差的原因，在于，group by以后，还需要做一个sort的操作。花的代价很高。
和exist一样，做了两次扫描。不过，相对于sort的操作，代价只有大概1/10。

Answer 38

Croatia:
 能不能帮忙把:
 select sum(Y*N) From ( 
  select A_ID, max((case when Flag = 'Y ' then 1 else -99 end )) Y , 
                         min((case when Flag = 'N ' then 0 else 1  end )) N 
  From taby  
  Group by A_ID ) d 的测试结果也来看看 ?我在DB2中只有几条记录的比较了 这个 和 你的那个B)的情况,效率好一点点..不过要加索引的话,最好: (a_id,Flag) 一起加索引.

Answer 39

SELECT COUNT(DISTINCT AID) 
FROM (SELECT DISTINCT A.A_ID AID FROM T1 WHERE a.FLAG = 'Y') a 
WHERE a.AID NOT EXISTS (SELECT DISTINCT A_ID FROM T1 WHERE FLAG = 'N') b ; 
终结版

Answer 40

select flag,a_id,count(a_id) as '个数' from @t group by flag,a_id 

Answer 41

To zhaowentao:
你的做法我已经说明了。基本上是最差的效率。To pumawang:
你的做法,其实和我提出的方案基本上是类似的.你还把逻辑复杂化了.
我这里没有办法测,要回去看.基本上,效率应该是差不多的.但是应该是略差一点。因为对flag字段作了条件选择。
不过,我想提醒你的是,你用了-99，我想是用错了，应该是0，不是吗？
index的话，不仅仅是一个，而是两个。
只用在a_id上面的，和用在a_id,Flag的。

Answer 42

Croatia:
  你好,能不能帮比较一下:
select sum(Y*N) From ( 
  select A_ID, max((case when Flag = 'Y ' then 1 else -99 end )) Y , 
                         min((case when Flag = 'N ' then 0 else 1  end )) N 
  From taby  
  Group by A_ID ) d 
这个的性能情况么 ?

Answer 43

晕,提交了才看到 Croatia 的回答..

Answer 44

TO charry0110:
不太明白你想干什么，赫赫。
不过，颜色很不错，赫赫。

Answer 45

用 -99 是没有问题的.
关键是 N 那个地方用了 0 .因为 0 * X 肯定是0 的,而不管X是否是0还是-99..手边没有Oracle,我在DB2 中试了一下,索引其实用一个就足够了,
(a_id,Flag),对Groupby 这类的检索用索引覆盖最有效.试了30万条数据,两个效率我这个略微好一点,相差1%左右,
我想可能是一个用函数,另一个用min()='Y'的过虑条件了吧.不过DB2不支持sum(count(distinct a_id)) 的写法,
用:
select count(*) From (select count(a_id) d  from taby group by a_id having min(flag)= 'Y ' ) a;
来替代了.

Answer 46

To pumawang:
我想，你的DB2应该是装在正式服务器上面的吧，所以，执行的时间差距才会比较小。我的oracle可是跑在我的笔记本上面的，所以，时间差距看得比较明显。你可以看看db2的执行计划，看看他们的cost来比较一下。
还有就是，数据越多，差距越大。30万和100万并不是简单的3：10的差距。赫赫。差距大的原因：
1。exist做了两次全表扫描；
2。exist需要把两次全表扫描的结果join起来。这就是比较大的代价了。

Answer 47

a)
SELECT COUNT(DISTINCT A.A_ID) AID FROM T1 WHERE a.FLAG =    'Y ' and NOT EXISTS (SELECT 1  FROM T1 B  WHERE A.A_ID = B.A_ID AND B.FLAG =    'N ')  b1)
select count(*) From (select count(a_id) d  from taby group by a_id having min(flag)= 'Y ' ) a;b2)
select sum(count(distinct a_id)) from T1 group by a_id having min(flag)=  'Y '; c)
select sum(Y*N) From ( 
  select A_ID, max((case when Flag = 'Y ' then 1 else -99 end )) Y , 
                         min((case when Flag = 'N ' then 0 else 1  end )) N 
  From taby  
  Group by A_ID ) d 我比较的是 b1) 21163 ,c) 21157 ,测试服务器,看的是成本.
拜托你下班后,用Oracle 100万数据分析一下这个三个SQL.
我觉得是 C)效率最好, A)最容易懂, B)想法最好.

Answer 48

Oracle上面分析的：操作                                             对象                                         Cost                Byte
A)
-SELECT STATEMENT                         OPTIMIZER_MODE = ALL_ROWS               1868                14
 -SORT GROUP BY                                                                                       14
   -HASH JOIN RIGHT ANTI                                                          1868                14
     -BITMAP CONVERSION TO ROWIDS                                                 315                 3110093
      └BITMAP INDEX FAST FULL SCAN       IDX_T1_3
     -BITMAP CONVERSION TO ROWIDS                                                 315                 3113705
      └BITMAP INDEX FAST FULL SCAN       IDX_T1_3
   
B1)
-SELECT STATEMENT                         OPTIMIZER_MODE = ALL_ROWS               351                 
 -SORT AGGREGATE
   -VIEW                                                                          351                
    -FILTER
     -SORT GROUP BY NOSORT                                                        351                 6223798
      -BITMAP CONVERSION TO ROWIDS                                                351                 6223798
       └BITMAP INDEX FULL SCAN           IDX_T1_3  B2)
-SELECT STATEMENT                         OPTIMIZER_MODE = ALL_ROWS               351                 7
 -SORT AGGREGATE                                                                  351                 7
  -FILTER
     -SORT GROUP BY                                                               351                 7
      -BITMAP CONVERSION TO ROWIDS                                                351                 6223798
       └BITMAP INDEX FULL SCAN           IDX_T1_3  
C)
-SELECT STATEMENT                         OPTIMIZER_MODE = ALL_ROWS               351                 6
 -SORT AGGREGATE                                                                                      6
   -VIEW                                                                          351                 5334684
     -SORT GROUP BY NOSORT                                                        351                 6223798
      -BITMAP CONVERSION TO ROWIDS                                                351                 6223798
       └BITMAP INDEX FULL SCAN           IDX_T1_3  补充：
IDX_T1_3是建立在A_ID, FLAG的索引。

Answer 49

执行时间：SQL> SELECT COUNT(DISTINCT A.A_ID) AID FROM T1 a WHERE a.FLAG = 'Y' and NOT EXISTS (SELECT 1  FROM T1 B  WHERE A.A_ID = B.A_ID AND B.FLAG = 'N') ;       AID
----------
       334経過: 00:00:31.06SQL> select count(*) From (select count(a_id) d  from t1 group by a_id having min(flag)=  'Y' ) a;  COUNT(*)
----------
       334経過: 00:00:00.90SQL> select sum(count(distinct a_id)) from T1 group by a_id having min(flag)=   'Y';SUM(COUNT(DISTINCTA_ID))
------------------------
                     334経過: 00:00:02.03SQL> select sum(Y*N) From ( select A_ID, max((case when Flag='Y' then 1 else -99 end)) Y ,min((case when Flag='N' then 0 else 1 end)) N From T1 Group 
by A_ID ) d;  SUM(Y*N)
----------
       334経過: 00:00:01.54

Answer 50

嗬嗬，是我的那个笔记本跑得好累呀！还有，顺便说一句：昨天测试下来，最快的语句：
select count(*) From (select a_id from t1 group by a_id having min(flag) = 'Y' ) a;时间大约是00:00:00.79 Cost也是351

Answer 51

学习,
select count(*) From (select a_id from t1 group by a_id having min(flag) =  'Y ' ) a; 
这个写的太精妙了.

Answer 52

欢迎加入本人的QQ群，群号5586686，身份认证输入如：JAVA、JSP、STRUTS等都可以，非软件开发人员勿加，谢谢合作！