例子:
有一个表 Table1,有两个字段 ACCOUNT_ID CATEGORY_ID.ACCOUNT_ID CATEGORY_NAME
1 a
2 b
3 a
1 c
2 c
4 a
1 e
a对应1.3.4 , c对应1.2.
b只对应2, e只对应1。问题:我想查找 CATEGORY_NAME所对应的ACCOUNT_ID只有一个种类的。就像b只对应2,e只对应1。
就是想找b和e 怎么找????
用selece from where 怎么实现
有一个表 Table1,有两个字段 ACCOUNT_ID CATEGORY_ID.ACCOUNT_ID CATEGORY_NAME
1 a
2 b
3 a
1 c
2 c
4 a
1 e
a对应1.3.4 , c对应1.2.
b只对应2, e只对应1。问题:我想查找 CATEGORY_NAME所对应的ACCOUNT_ID只有一个种类的。就像b只对应2,e只对应1。
就是想找b和e 怎么找????
用selece from where 怎么实现
from table1
group by CATEGORY_NAME
having count(distinct ACCOUNT_ID)=1
select * from ta a
where not exists(select 1 from ta b where a.CATEGORY_ID=b.CATEGORY_ID and a.rowid!=b.rowid)
from Table1 a
where not exists(
select 1 from Table1
where CATEGORY_NAME=a.CATEGORY_NAME
and ACCOUNT_ID!=a.ACCOUNT_ID)
from Table1
group by CATEGORY_NAME
having count(distinct ACCOUNT_ID)=1
非常感谢 wildwave的答案,多谢了!!
select CATEGORY_NAME from(
select account_id,CATEGORY_NAME from temp group by account_id,CATEGORY_NAME
) group by CATEGORY_NAME having count(CATEGORY_NAME) = 1
)group by account_id,CATEGORY_NAME