我创建了一个VIEW (VISIT_VIEW)包含两列的市民和count(visitsDoctors) AS ToTal_Visits：
CREATE VIEW VISIT_VIEW AS
SELECT citizen, COUNT(doctor) AS Total_Visits
FROM citizensvisits
GROUP BY citizen, doctor;
这返回了每个公民去看医生多少次的谎言
citizen1 1
citizen1 2
citizen1 5
...
citizen3 10
citizen3 4
然后，我需要创建一个新视图，该视图将保存TotalVisits的最大值，并针对每个公民
CREATE VIEW MAXVISITS AS
SELECT citizens, Total_Visits AS MaxTotal_Visits
FROM VISIT_VIEW
WHERE MaxTotal_Visits = max("Total_Visits")
GROUP BY citizens, MaxTotal_Visits;
所以当尝试
SELECT *
FROM MAXVISIT_VIEW
我懂了
结果：滥用集合：max（）
我究竟做错了什么 ？
编辑：我已经放置了VISIT_VEW代码，并澄清一下，我需要一个新视图，该视图应列出每个公民的最大访问次数，即
citizen1 5
citizen3 10
您不能MAX()直接使用聚合函数。
您必须再次聚合：
CREATE VIEW MAXVISITS AS
SELECT citizen, MAX(Total_Visits) AS MaxTotal_Visits
FROM VISIT_VIEW
GROUP BY citizen
或不使用VISIT_VIEW，直接从带有MAX()窗口功能的表中选择：
CREATE VIEW MAXVISITS AS
SELECT DISTINCT citizen,
MAX(COUNT(*)) OVER (PARTITION BY citizen) AS Total_Visits
FROM citizensvisits
GROUP BY citizen, doctor;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句