在oracle中使用SQL进行计算
问题描述:
嗨我正在使用orcale,并且在表之间创建了一个连接。我现在想插入一个计算,但它一直想出了相同的错误消息“ORA-00933:SQL命令不能正确地结束” 我公司生产的代码是这个..在oracle中使用SQL进行计算
select "CUSTOMER"."CUST_ID" as "CUST_ID",
"CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
"CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
"CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
"CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
"CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
"CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
"CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
"CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
"CUSTOMER"."CUST_TEL" as "CUST_TEL",
"CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
"MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
"ORDER_ITEM"."QUANTITY" as "QUANTITY",
"ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
"ORDER_DETAILS"."ORDER_ID" as "ORDER_ID"
from "ORDER_DETAILS" "ORDER_DETAILS",
"ORDER_ITEM" "ORDER_ITEM",
"MENU_ITEM" "MENU_ITEM",
"CUSTOMER" "CUSTOMER"
where "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
and "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
and "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"`
这是代码生成错误消息
select "CUSTOMER"."CUST_ID" as "CUST_ID",
"CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
"CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
"CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
"CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
"CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
"CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
"CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
"CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
"CUSTOMER"."CUST_TEL" as "CUST_TEL",
"CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
"MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
"ORDER_ITEM"."QUANTITY" as "QUANTITY",
"ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
"ORDER_DETAILS"."ORDER_ID" as "ORDER_ID"
from "ORDER_DETAILS" "ORDER_DETAILS",
"ORDER_ITEM" "ORDER_ITEM",
"MENU_ITEM" "MENU_ITEM",
"CUSTOMER" "CUSTOMER"
where "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
and "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
and "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"
SELECT PRODUCT_PRICE,
QUANTITY,
PRODUCT_PRICE * QUANTITY AS "TOTAL SPENT"
FROM MENU_ITEM,ORDER_ITEM
答
SQL的问题是您的第二个“SELECT”语句将打开一个新的SQL命令。如果您想在查询中添加一个名为“TOTAL_SPENT”的新列,将PRODUCT_PRICE和QUANTITY相乘,请将其添加到主查询的SELECT列表中。添加第二个SELECT语句不会将结果合并到第一个。
你想是这样的:
select "CUSTOMER"."CUST_ID" as "CUST_ID",
"CUSTOMER"."CUST_TITLE" as "CUST_TITLE",
"CUSTOMER"."CUST_FORENAME" as "CUST_FORENAME",
"CUSTOMER"."CUST_SURNAME" as "CUST_SURNAME",
"CUSTOMER"."CUST_ADDRESS_1" as "CUST_ADDRESS_1",
"CUSTOMER"."CUST_ADDRESS_2" as "CUST_ADDRESS_2",
"CUSTOMER"."CUST_TOWN-CITY" as "CUST_TOWN-CITY",
"CUSTOMER"."CUST_COUNTY" as "CUST_COUNTY",
"CUSTOMER"."CUST_POST_CODE" as "CUST_POST_CODE",
"CUSTOMER"."CUST_TEL" as "CUST_TEL",
"CUSTOMER"."CUST_EMAIL" as "CUST_EMAIL",
"MENU_ITEM"."PRODUCT_PRICE" as "PRODUCT_PRICE",
"ORDER_ITEM"."QUANTITY" as "QUANTITY",
"ORDER_ITEM"."ORDER_ID" as "ORDER_ID",
"ORDER_DETAILS"."ORDER_ID" as "ORDER_ID",
PRODUCT_PRICE * QUANTITY AS "TOTAL SPENT"
from "ORDER_DETAILS" "ORDER_DETAILS",
"ORDER_ITEM" "ORDER_ITEM",
"MENU_ITEM" "MENU_ITEM",
"CUSTOMER" "CUSTOMER"
where "ORDER_ITEM"."ORDER_ID"="ORDER_DETAILS"."ORDER_ID"
and "ORDER_DETAILS"."CUST_ID"="CUSTOMER"."CUST_ID"
and "MENU_ITEM"."MENU_ITEM_ID"="ORDER_ITEM"."MENU_ITEM_ID"
根据是其他表中哪些列,你可能需要资格PRODUCT_PRICE和数量与他们的表名,像SQL其他列引用。
+0
非常感谢你!它确实有效,你今天救了我的品牌是我的任务浪费了一天!谢谢 – user2179333 2013-03-18 22:05:00
请发布产生错误消息的实际查询。 – ruakh 2013-03-18 21:24:59
您发布的两个查询中的哪一个会产生错误? – 2013-03-18 21:26:43
这是产生错误消息的代码 – user2179333 2013-03-18 21:29:58