在SQLITE中获取视图的最大值时滥用了aggregate:max()

Question

我创建了一个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

Answer 1

不能直接使用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;

Answer 2

我可能大错特错，但我认为您需要进行一次选择

类似的东西，但不完全是

CREATE VIEW MAXVISITS AS
SELECT citizens,  Total_Visits AS MaxTotal_Visits
FROM VISIT_VIEW
WHERE MaxTotal_Visits = (SELECT MAX(Total_Visits) FROM VISIT_VIEW)
GROUP BY citizens, MaxTotal_Visits;

可能存在也可能没有其他问题。包括我的解决方案。