查询语句求大神

[cyh811122]

表Ａ，
数据如下：
　id name aa bb
1 a1 1 3
2 a1 2 4
3 a2 1 5
4 a3 2 6
5 a4 2 7
6 a5 1 13
7 a5 2 14

不创建临时表的情况怎么写查询语句?最后想实现的是　　同时aa字段同时存在　　１　和２　的才取出
结果为：
　id name aa bb
1 a1 1 3
2 a1 2 4
6 a5 1 13
7 a5 2 14

[卖水果的net]

如果不建临时表，可以使用 with as 块。

[cyh811122]

引用 1 楼 wmxcn2000 的回复:

如果不建临时表，可以使用 with as 块。

给简单写个呗，谢谢。

[jdsnhan]

SQL> select * from a;
        ID NAME         AA         BB
---------- ---- ---------- ----------
         1 a1            1          3
         2 a1            2          4
         3 a2            1          5
         4 a3            2          6
         8 a3            2          6
         5 a4            2          7
         6 a5            1         13
         7 a5            2         14
8 rows selected

SQL> 
SQL> select * from a where name in (
  2  select name from a
  3  where aa in (1,2)
  4  group by name
  5  having count(distinct aa) >1)
  6  ;
        ID NAME         AA         BB
---------- ---- ---------- ----------
         1 a1            1          3
         2 a1            2          4
         6 a5            1         13
         7 a5            2         14

[xiaoguanzhao]

with a as ( select '1' id ,'a1' name,'1' aa ,'3' bb from dual union all select '2','a1','2','4' from dual union all select '3','a2','1','5' from dual union all select '4','a3','2','6' from dual union all select '5','a4','2','7' from dual union all select '6','a5','1','13' from dual union all select '7','a5','2','14' from dual ) select * from a where a.name in ( select name from ( select name,listagg(a.aa,',') within group(order by name) as cc from a group by name ) t where instr(t.cc,'1')>0 and instr(t.cc,'2')>0) order by 1;

[cyh811122]

select name,count(aa) from s where aa in ('1' , '2') group by name having COUNT(distinct [aa ])>1