sqlite - 滥用聚合 : max() when getting the max of a view in SQLITE

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 

然后我需要创建一个新 View ，该 View 将保存 TotalVisits 和每个公民的 MAX

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

我得到

Result: misuse of aggregate: max()

我究竟做错了什么 ？
编辑:我已经把 VISIT_VEW代码并澄清，我需要一个新 View ，该 View 应该列出每个公民的 MAX 访问次数，即

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;