select aa, bb from tab group by aa, bb having count(*) = 1;你结贴率 0,当然没什么人帮你了。
结果展示: AA BB 1 C 1 A 2 B 2 A
declare @a table(id int,name varchar(20))insert into @a select 1,'a' insert into @a select 2,'b' insert into @a select 1,'c' insert into @a select 3,'d' insert into @a select 4,'a' insert into @a select 2,'a'select * from @a group by id,name having count(*)=1------------------ id name 1 a 2 a 4 a 2 b 1 c 3 d
我写了一个。。但是查出来的结果不对 select * from a where a.aa in(select aa from a where a.aa=aa and a.bb<>bb )
with dm as( select '1' c1,'a' c2 from dual union all select '2' c1,'b' c2 from dual union all select '1' c1,'c' c2 from dual union all select '3' c1,'d' c2 from dual union all select '4' c1,'a' c2 from dual union all select '2' c1,'a' c2 from dual ) select * from dm where c1 in ( select c1 from dm group by c1 having count(*)>1)
select distinct(BB),AA from Table group by BB,AA order by BB asc
select * from a where aa in (select aa from a group by aa having count(bb)>1) order by aa; AA BB ---------- ---------- 1 c 1 a 2 a 2 b
大虾米 这个查询 只得出来C1>1的结果。但没有得当C1相等时并且C2不相等的结果
select * from A where aa not in (select a from A group by a having(a>1))或者是 select * from A where aa not in (select a from A group by a having(a>2)) 不知道对你有没有帮助。
不好意思 写错了 select * from A where aa not in (select aa from A group by aa having(aa>1))或者是 select * from A where aa not in (select aa from A group by aa having(aa>2))
select * from A where aa not in(select aa from A group by aa having count (*)=1) 我试了一下,好像是可以的。
SQL> with T as( 2 select '1' AA,'a' BB from dual 3 union all 4 select '2' AA,'b' BB from dual 5 union all 6 select '1' AA,'c' BB from dual 7 union all 8 select '3' AA,'d' BB from dual 9 union all 10 select '4' AA,'a' BB from dual 11 union all 12 select '2' AA,'a' BB from dual) 13 select * from T where AA in ( 14 select AA from T group by AA having count(*)>1);
这个答案20楼是否满意?楼主的结贴率本来是不想回这贴的。with t as ( select 1 aa, 'a' bb from dual union all select 2 aa, 'b' bb from dual union all select 1 aa, 'c' bb from dual union all select 3 aa, 'd' bb from dual union all select 4 aa, 'a' bb from dual union all select 2 aa, 'a' bb from dual ) select * from t where aa in (select aa from t group by aa having count(*) > 1);
如果按19楼的数据进行过滤的SQL如下: with t as ( select 127542 gdid, 100158 supid, 4 wmid from dual union all select 127498, 100018 supid, 4 wmid from dual union all select 126159, 100124, 2 from dual union all select 127500, 100158, 2 from dual union all select 127520, 100158, 2 from dual union all select 131100, 100158, 2 from dual union all select 127522, 100173, 1 from dual union all select 132975, 100156, 1 from dual union all select 131106, 100158, 4 from dual ) select * from t where supid in (select supid from t group by supid having count(*) > 1) and (supid,wmid) not in (select supid,wmid from t group by supid,wmid having count(*) > 1);
我找到了方法了。。 select distinct r1.aa,r1.bb from (select * from a_test where aa in (select aa from a_test group by aa having (count(bb)>1))) r1; 上面这个语句就是了。。 折腾了挺久的,, 可以达到楼主的要求了。
SELECT * FROM A TA WHERE EXISTS (SELECT 1 FROM A WHERE AA = TA.AA AND BB <> TA.BB)
[SYS@orcl] SQL>WITH DM AS 2 (SELECT '1' C1, 'a' C2 3 FROM DUAL 4 UNION ALL 5 SELECT '2' C1, 'b' C2 6 FROM DUAL 7 UNION ALL 8 SELECT '1' C1, 'c' C2 9 FROM DUAL 10 UNION ALL 11 SELECT '3' C1, 'd' C2 12 FROM DUAL 13 UNION ALL 14 SELECT '4' C1, 'a' C2 15 FROM DUAL 16 UNION ALL 17 SELECT '2' C1, 'a' C2 FROM DUAL) 18 SELECT * 19 FROM DM 20 WHERE C1 IN (SELECT C1 FROM DM GROUP BY C1 HAVING COUNT(DISTINCT C2) > 1) 21 ;C C - - 1 c 1 a 2 a 2 b
一个自连接,不就OK了吗SELECT a1.AA,a1.BB FROM A a1 JOIN A a2 ON a1.AA=a2.AA AND a1.BB<>a2.BB;
select aa, bb from tab
group by aa, bb
having count(*) = 1;你结贴率 0,当然没什么人帮你了。
AA BB
1 C
1 A
2 B
2 A
declare @a table(id int,name varchar(20))insert into @a select 1,'a'
insert into @a select 2,'b'
insert into @a select 1,'c'
insert into @a select 3,'d'
insert into @a select 4,'a'
insert into @a select 2,'a'select * from @a group by id,name having count(*)=1------------------
id name
1 a
2 a
4 a
2 b
1 c
3 d
select * from a where a.aa in(select aa from a where a.aa=aa and a.bb<>bb )
select '1' c1,'a' c2 from dual union all
select '2' c1,'b' c2 from dual union all
select '1' c1,'c' c2 from dual union all
select '3' c1,'d' c2 from dual union all
select '4' c1,'a' c2 from dual union all
select '2' c1,'a' c2 from dual
)
select * from dm where c1 in (
select c1 from dm group by c1 having count(*)>1)
---------- ----------
1 c
1 a
2 a
2 b
select * from A where aa not in (select a from A group by a having(a>2))
不知道对你有没有帮助。
select * from A where aa not in (select aa from A group by aa having(aa>1))或者是
select * from A where aa not in (select aa from A group by aa having(aa>2))
我试了一下,好像是可以的。
2 select '1' AA,'a' BB from dual
3 union all
4 select '2' AA,'b' BB from dual
5 union all
6 select '1' AA,'c' BB from dual
7 union all
8 select '3' AA,'d' BB from dual
9 union all
10 select '4' AA,'a' BB from dual
11 union all
12 select '2' AA,'a' BB from dual)
13 select * from T where AA in (
14 select AA from T group by AA having count(*)>1);
AA BB
-- --
1 c
1 a
2 a
2 b
SQL>
gdid supid wmid
127542 100158 4
127498 100018 4
126159 100124 2
127500 100158 2
127520 100158 2
131100 100158 2
127522 100173 1
132975 100156 1
131106 100158 4
要得出SUPID字段下相同并且WMID不同的记录;备注:
supid中相同的数据也可以能有上百个,但是我要过滤的是该字段相同。并且WMID不同的数据
select 1 aa, 'a' bb from dual union all
select 2 aa, 'b' bb from dual union all
select 1 aa, 'c' bb from dual union all
select 3 aa, 'd' bb from dual union all
select 4 aa, 'a' bb from dual union all
select 2 aa, 'a' bb from dual )
select * from t where aa in (select aa from t group by aa having count(*) > 1);
with t as (
select 127542 gdid, 100158 supid, 4 wmid from dual union all
select 127498, 100018 supid, 4 wmid from dual union all
select 126159, 100124, 2 from dual union all
select 127500, 100158, 2 from dual union all
select 127520, 100158, 2 from dual union all
select 131100, 100158, 2 from dual union all
select 127522, 100173, 1 from dual union all
select 132975, 100156, 1 from dual union all
select 131106, 100158, 4 from dual
)
select * from t
where supid in (select supid from t group by supid having count(*) > 1) and
(supid,wmid) not in (select supid,wmid from t group by supid,wmid having count(*) > 1);
select distinct r1.aa,r1.bb from (select * from a_test where aa in (select aa from a_test group by aa having (count(bb)>1))) r1;
上面这个语句就是了。。
折腾了挺久的,, 可以达到楼主的要求了。
FROM A TA
WHERE EXISTS (SELECT 1
FROM A
WHERE AA = TA.AA
AND BB <> TA.BB)
[SYS@orcl] SQL>WITH DM AS
2 (SELECT '1' C1, 'a' C2
3 FROM DUAL
4 UNION ALL
5 SELECT '2' C1, 'b' C2
6 FROM DUAL
7 UNION ALL
8 SELECT '1' C1, 'c' C2
9 FROM DUAL
10 UNION ALL
11 SELECT '3' C1, 'd' C2
12 FROM DUAL
13 UNION ALL
14 SELECT '4' C1, 'a' C2
15 FROM DUAL
16 UNION ALL
17 SELECT '2' C1, 'a' C2 FROM DUAL)
18 SELECT *
19 FROM DM
20 WHERE C1 IN (SELECT C1 FROM DM GROUP BY C1 HAVING COUNT(DISTINCT C2) > 1)
21 ;C C
- -
1 c
1 a
2 a
2 b
FROM A a1 JOIN A a2
ON a1.AA=a2.AA
AND a1.BB<>a2.BB;