Nested Aggregate functions in mysql

[jackofalltrades]

-- (Q3)  Find Insurance Company with the maximal number of members (patients).

SELECT Temp.Company, Temp.ID, Temp.Amount
FROM  (SELECT I.name AS Company, I.cid AS ID, COUNT(P.pid) AS Amount
    FROM Insurance_Company I, Patients P
    WHERE P.cid = I.cid 
    GROUP BY ID
    ) AS Temp
WHERE Temp.Amount = (SELECT MAX (Temp.Amount)
           FROM Temp)

אני לומד מהמצגות של רמקרישנן וכך מתארים את האגרגציה המקוננת, אבל כשאני רוצה להריץ את זה במיי-אסקיואל הוא אומר:

( Error Code: 1146. Table 'medical_service.temp' doesn't exist

אבל זה כן עובד כשאני מחליף את המילה Temp ב-From השני לכל הבלוק From הגדול. יש דרך לעשות את זה כמו שאני תכננתי?

[domiel]

תנסה להעיף את הas, לא סגור על הסינטקס של mysql, אבל בימינו לא צריך לסמן כל alias עם as.

חוץ מזה שselect המקונן בwhere הוא רעיון רע מבחינת ביצועים.

[jackofalltrades]

את האליאס הוא מחייב: נותן שגיאה וכותב במפורש שכל מכונן חייב אליאס. לגביי הסלקט, עוד לא הגעתי לאופטיציזציה

ומסתבר שאין דרך אחרת, המיי עדיין לא תומך

[domiel]

SELECT Temp.Company, Temp.ID, Temp.Amount
FROM  (SELECT I.name AS Company, I.cid AS ID, COUNT(P.pid) AS Amount    
	FROM Insurance_Company I, Patients P    
	WHERE P.cid = I.cid     
	GROUP BY ID) AS Temp
WHERE Temp.Amount = (SELECT MAX (Temp.Amount)
			FROM Temp) 

שתי בעיות- ID לא קיים (ה select נעשה אחרי הgroup by), ואתה לא יכול להציג נתון בgroup by מבלי שהוא יהיה כחלק מהgroup by/ בעל פונקציה אנליטית.

SELECT Temp.Company, Temp.ID, Temp.Amount
FROM  (
	SELECT I.name AS Company, I.cid AS ID, COUNT(P.pid) AS Amount    
	FROM Insurance_Company I, Patients P    
	WHERE P.cid = I.cid     
	GROUP BY P.cid, I.name 
    ) AS Temp
	WHERE Temp.Amount = (SELECT MAX (Temp.Amount)           
				FROM Temp) 

ואנחנו עדיין לא שם, אם נלך בגישתך אז עדיף:

with temp as(
	SELECT I.name AS Company, I.cid AS ID, COUNT(P.pid) AS Amount
	FROM Insurance_Company I, Patients P    
	WHERE P.cid = I.cid     
	GROUP BY P.cid, I.name 
)
SELECT Temp.Company, Temp.ID, Temp.Amount
FROM temp
WHERE Temp.Amount = (SELECT MAX (Temp.Amount)           
			FROM Temp)

ובכלל אם כבר עדיף:

SELECT I.name Company, I.cid ID, COUNT(P.pid) Amount
FROM Insurance_Company I Join Patients P using(cid)
GROUP BY cid, I.name having max(count(*))=count(*)