时间戳

首页 > 解决方案 > 有什么方法可以避免在 sql plus 中加入笛卡尔连接

sql - 有什么方法可以避免在 sql plus 中加入笛卡尔连接

问题描述

我有下面给出的三个表：

supplied_items(SUPPLIER_ID,ITEM_ID,QUANTITY,COST_PRICE);
ORDERS(ORDER_ID,CUSTOMER_ID,EMPLOYEE_ID,ITEM_ID,PRICE,QUANTITY,TOTAL_PRICE,ODATE);
EXPENSES(EXPENSE_ID,EDATE,DESCRIPTION,PAYMENT_TYPE,AMOUNT);

我必须计算净利润。但是当我在查询中包含费用表时，它会与其他表的行生成笛卡尔积。以下是我的查询：

SELECT
    SUM(orders.quantity * orders.price) AS "Sale",
    SUM(orders.quantity * supplied_items.cost_price) AS "COST",
    SUM(orders.quantity *(orders.price - supplied_items.cost_price)) AS "Profit",
    SUM(expenses.amount)
FROM
    orders
    LEFT OUTER JOIN supplied_items ON orders.item_id = supplied_items.item_id
    CROSS JOIN expenses;

标签： sqlsqlplus

解决方案

似乎您需要费用表的全部结果：

SELECT
    SUM(orders.quantity * orders.price) AS "Sale",
    SUM(orders.quantity * supplied_items.cost_price) AS "COST",
    SUM(orders.quantity *(orders.price - supplied_items.cost_price)) AS "Profit",
    MAX(select SUM(expenses.amount) from expenses) as "Expenses"
FROM
    orders
    LEFT OUTER JOIN supplied_items ON orders.item_id = supplied_items.item_id

推荐阅读