错误:靠近第 1 行:靠近"(":语法错误:尝试创建视图时出现 SQLite 错误

Question

我正在尝试在SQLite中创建视图，但显然存在语法错误，我不确定它是什么。这是查询：

CREATE VIEW personDetails(fname, lname, bdate, bplace, carsowned,ticketsRcvd) AS
SELECT p.fname,p.lname,p.bdate,p.bplace,COUNT(DISTINCT(r.vin)),COUNT(DISTINCT(t.violation))
FROM persons p LEFT JOIN registrations r ON p.fname = r.fname
LEFT JOIN tickets t ON r.regno = t.regno
GROUP BY p.fname,p.lname, p.bdate,p.bplace;

Answer 1

这可能更像是一个评论而不是一个答案，但格式并不适合评论。

我会试试这个：

CREATE VIEW personDetails
AS
SELECT p.fname, p.lname, p.bdate, p.bplace,
COUNT(DISTINCT r.vin) carsowned, COUNT(DISTINCT t.violation) ticketsRcvd
FROM persons p
LEFT JOIN registrations r
ON p.fname = r.fname
AND p.lname = r.lname
LEFT JOIN tickets t ON r.regno = t.regno
GROUP BY p.fname, p.lname, p.bdate, p.bplace;

Answer 2

您的视图创建没有错。但是，在创建新视图之前，首先存在删除视图的最佳做法。

DROP VIEW IF EXISTS [personDetails]; 
CREATE VIEW [personDetails](fname, lname, bdate, bplace, carsowned,ticketsRcvd) AS
SELECT p.fname,p.lname,p.bdate,p.bplace,COUNT(DISTINCT(coalesce(r.vin, 0))),COUNT(DISTINCT(coalesce(t.violation, 0)))
FROM persons p 
LEFT JOIN registrations r ON p.fname = r.fname
LEFT JOIN tickets t ON r.regno = t.regno
GROUP BY p.fname,p.lname, p.bdate,p.bplace;