这个SQL该如何改？

Question

如下所示，此语句该如何改？
select comm_aopportunityid from (
SELECT comm_aopportunityid,comm_deleted,count(comm_aopportunityid)
FROM communication
 
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1
)

Answer 1

select comm_aopportunityid 
from 
(
SELECT comm_aopportunityid,comm_deleted,count(comm_aopportunityid)
FROM communication
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1
) t

Answer 2

select comm_aopportunityid from (
SELECT comm_aopportunityid,comm_deleted,count(comm_aopportunityid)
FROM communication
 
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1
) AS T 

Answer 3

select comm_aopportunityid from (
SELECT comm_aopportunityid,comm_deleted,count(comm_aopportunityid)
FROM communication
 
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1
) as t --如果from 里面用子查询 选出新表 一定要为他加别名~

Answer 4

1、给结果集起个别名
2、给count(comm_aopportunityid) 字段起个名称
select comm_aopportunityid from (
SELECT comm_aopportunityid,comm_deleted,count(comm_aopportunityid) as cnt
FROM communication
 
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1
) as tab或者
只用里面的结果集
SELECT comm_aopportunityid
FROM communication 
where comm_deleted is null
GROUP BY comm_aopportunityid,comm_deleted
HAVING count(comm_aopportunityid) > 1