表salesorderheader：
-salesorderid
-orderdate
-customerid
-subtotal表客户：
-customerid
-companyname表公司：
-id
-cname代码：
INSERT INTO company_abc (cid, salestotal, cls, year)
SELECT distinct com.id, SUM(soh.subtotal),
CASE
WHEN SUM(soh.subtotal) <= 0.8 * SUM(soh.subtotal) OVER (PARTITION BY EXTRACT(YEAR FROM soh.orderdate)) THEN 'A'
WHEN SUM(soh.subtotal) <= 0.95 * SUM(soh.subtotal) OVER (PARTITION BY EXTRACT(YEAR FROM soh.orderdate)) AND SUM(soh.subtotal) > 0.8 * SUM(soh.subtotal) OVER (PARTITION BY EXTRACT(YEAR FROM soh.orderdate)) THEN 'B'
ELSE 'C'
END,
EXTRACT(YEAR FROM soh.orderdate)
FROM salesorderheader soh
INNER JOIN customer c ON soh.customerid = c.customerid
INNER JOIN company com ON com.cname = c.companyname
GROUP BY com.id, EXTRACT(YEAR FROM soh.orderdate)
ORDER BY com.id, EXTRACT(YEAR FROM soh.orderdate) desc错误：
SQL Error [42803]: ERROR: column "soh.subtotal" must appear in the GROUP BY clause or be used in an aggregate function
position: 150
error position: line: 4 pos: 149我试图在group by中添加soh.subtotal，但发现了这个错误：
Key (cid, year)=(1102, 2013) already exists.
1条答案
按热度按时间nx7onnlm1#
根据DBMS错误消息的要求，尝试对查询进行一些更正。将windows函数中的
soh.subtotal替换为SUM(soh.subtotal)。SELECT是在GROUP BY之后执行的查询的一部分。因此，对于GROUP BY子句中不包含的值，您必须在SELECT子句中使用SUM(something)，MIN(something)等表达式。对于具有GROUP BY的WINDOWS函数也是如此。