以下是我的查询:
SELECT ID ,
COUNT(STATUS)
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3;
我需要结果只显示ID列,而不是每行的计数。
以下是我的查询:
SELECT ID ,
COUNT(STATUS)
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3;
我需要结果只显示ID列,而不是每行的计数。
只需从选择列表中删除它:
select ID -- count(STATUS) removed here
from TABLENAME
where STATUS = 'GREEN'
group by ID
having count(STATUS) > 3
SELECT sub.ID FROM (
SELECT ID ,
COUNT(STATUS)
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3) AS sub;
SELECT
ID
--,COUNT(STATUS) as [Count]
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3;
在 MS SQL (SSMS) 中
SELECT ID --,COUNT(STATUS) as [Status Count]
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3;
如上所述,子查询可以用于复杂的查询。 或者 您也可以使用CTE - 公共表达式, 例如
with Status_count as
(
SELECT ID, COUNT(STATUS) as [Status Count]
FROM tablename
WHERE STATUS = 'GREEN'
GROUP BY ID
HAVING COUNT(STATUS) > 3
)
select ID
from Status_count
CTE(公共表达式)用于简化问题并将其转换为子查询,如果可能的话。
select
语句中删除count(STATUS)
。 - Vamsi Prabhala