MySQL-WHERE 절에서 COUNT (*) 사용

MySQL-WHERE 절에서 COUNT (*) 사용

---

MySQL에서 다음을 달성하려고합니다 ( pseudo코드 참조 )

SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC

WHERE 절에서 (SELECT ...)를 사용하지 않고이 작업을 수행하는 방법이 있습니까? 자원 낭비처럼 보일 것입니다.

---

이 시도;

select gid
from `gd`
group by gid 
having count(*) > 10
order by lastupdated desc

---

당신이하려는 일이 확실하지 않습니다 ... 아마도

SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num > 10 ORDER BY lastupdated DESC

---

SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;

편집 (만약 당신이 원하는 경우) :

SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC

---

시험

SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC

---

조항이없는 학술 용 버전 :

select *
from (
   select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;

---

WHERE 절에는 집계 함수 (예 : COUNT, MAX 등)를 사용할 수 없습니다. 따라서 대신 HAVING 절을 사용합니다. 따라서 전체 쿼리는 다음과 유사합니다.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

---

-30 분마다 기록이없는 기상 관측소 검색

SELECT stationid
FROM weather_data 
WHERE  `Timestamp` LIKE '2011-11-15 %'  AND 
stationid IN (SELECT `ID` FROM `weather_stations`)
GROUP BY stationid 
HAVING COUNT(*) != 48;

-yapiskan의 위치가 ..에서 선택됩니다.

---

난 당신이 추가 할 수 없습니다 생각 count()과 함께 where. 이제 이유를 보자 ....

where is not same as having , having means you are working or dealing with group and same work of count , it is also dealing with the whole group ,

now how count it is working as whole group

create a table and enter some id's and then use:

select count(*) from table_name

you will find the total values means it is indicating some group ! so where does added with count() ;

---

COUNT(*) can only be used with HAVING and must be used after GROUP BY statement Please find the following example:

SELECT COUNT(*), M_Director.PID FROM Movie
INNER JOIN M_Director ON Movie.MID = M_Director.MID 
GROUP BY M_Director.PID
HAVING COUNT(*) > 10
ORDER BY COUNT(*) ASC

참고URL : https://stackoverflow.com/questions/301793/mysql-using-count-in-the-where-clause