问

具有计数错误的多表查询

只想活得快乐的魔羯发布于 2023-01-19 18:32

select

每当我向下面的select语句添加一个计数时,它会导致它给我一个错误,我不知道为什么:

select "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
"Vehicles"."LicensePlate", COUNT("WorkOrderPart"."WorkPartID") AS "Parts"
FROM "WorkOrder", "Vehicles", "WorkOrderPart"
WHERE "WorkOrder"."VIN" = "Vehicles"."VIN" AND
"WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID";

每当我运行它时,我会收到以下错误:

select "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
       *
ERROR at line 1:
ORA-00937: not a single-group group function

我该怎么办？我正在使用Oracle数据库运行SQL命令行.

1 个回答

如果您有count(或其他聚合函数),则需要group by非聚合项

select 
     "WorkOrder"."WorkOrderID", 
     "Vehicles"."Model", 
     "Vehicles"."Color",
     "Vehicles"."LicensePlate", 
     COUNT("WorkOrderPart"."WorkPartID") AS "Parts"
FROM "WorkOrder", "Vehicles", "WorkOrderPart"
WHERE "WorkOrder"."VIN" = "Vehicles"."VIN" 
AND "WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID"
group by  "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
"Vehicles"."LicensePlate"

顺便说一句,使用显式连接(除非你使用的是史前版本的oracle)代替where条款被认为是一种好习惯.

FROM "WorkOrder" 
     inner join "Vehicles" on "WorkOrder"."VIN" = "Vehicles"."VIN" 
     left join "WorkOrderPart" on "WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID"

2023-01-19 18:36 回答

亚瑟女皇韩版欧美风情女装旗舰店

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签