SQL ORACLE三个查询的问题

snz8szmq 于 2023-01-20 发布在 Oracle

关注(0)|答案(2)|浏览(156)

我有三个表orders、orderdetails和customers。
orders表包含以下列：

ORDERID, CUSTOMERID, EMPLOYEEID, TERRITORYID,
ORDERDATE, REQUIREDDATE, SHIPPEDDATE,
SHIPVIA, FREIGHT, SHIPNAME,
SHIPADDRESS, SHIPCITY, SHIPREGION,
SHIPPOSTALCODE, SHIPCOUNTRY

orderdetails表包含以下列：

ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT

customers表包含以下列：

CUSTOMERID, COMPANYNAME, CONTACTNAME,
CONTACTTITLE, ADDRESS, CITY, REGION, 
POSTALCODE, COUNTRY, PHONE, FAX

我必须执行三个查询：

- 1**如果总金额大于10000，则总金额较高的订单列表（单价 * 数量 *（1-折扣））

我是这样做的：

SELECT orderid  
FROM orders  
JOIN orderdetails USING (orderid)
GROUP BY orderid
HAVING SUM (unitprice * quantity * (1 - discount)) > 10000;

- 2**总金额最高的订单（含折扣）

我是这样做的：

SELECT MAX (orderid),
       SUM (unitprice * quantity * (1 - discount)) as price
FROM orders  
JOIN orderdetails USING (orderid)
ORDER BY price DESC;

- 3**显示所有订单运费超过2000的客户的customerid和运费（运输）

我没能做到这一点

SELECT customerid,freight
FROM customers  
JOIN orders USING (customerid)
HAVING Count (freight) > 2000;

我想知道我是否正确地做了#1和#2，或者有没有更好的方法来做，以及我如何解决#3。
非常感谢大家。

oracle

来源：https://stackoverflow.com/questions/75162607/sql-oracle-problems-with-three-queries

2条答案

按热度按时间

xwbd5t1u1#

您不需要customers表（因为orders表同时包含freight和customerid），但需要有一个GROUP BY子句，并在SELECT子句中聚合freight列（可能希望使用SUM而不是COUNT，但您没有提供任何示例数据，因此很难判断）：

SELECT customerid,
       SUM(freight)
FROM   orders
GROUP BY
       customerid
HAVING SUM(freight) > 2000;

赞(0）回复(0）举报 2023-01-20

jv4diomz2#

示例数据如下所示：

WITH 
    orders (ORDERID, CUSTOMERID, EMPLOYEEID, TERRITORYID, ORDERDATE, REQUIREDDATE, SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME,
            SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE, SHIPCOUNTRY) AS
        (
            Select 101, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 102, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  250, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 103, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 104, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  750, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 105, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  950, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 106, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1250, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 107, 3, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 2500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 108, 4, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  850, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 109, 4, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  950, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 110, 5, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1150, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual 
        ),
    details (ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT) AS
        (
            Select 101, 1001, 700, 5, 0.1 From Dual Union All
            Select 101, 1002, 700, 5, 0.1 From Dual Union All
            Select 102, 1003, 800, 2, 0.1 From Dual Union All
            Select 102, 1004, 700, 2, 0.1 From Dual Union All
            Select 103, 1003, 900, 5, 0.3 From Dual Union All
            Select 103, 1004, 700, 6, 0.1 From Dual Union All
            Select 103, 1005, 700, 7, 0.1 From Dual Union All
            Select 104, 1001, 700, 1, 0.1 From Dual Union All
            Select 104, 1002, 900, 1, 0.2 From Dual Union All
            Select 105, 1002, 900, 3, 0.1 From Dual Union All
            Select 106, 1005, 700, 1, 0.1 From Dual Union All
            Select 107, 1003, 700, 7, 0.1 From Dual Union All
            Select 108, 1004, 700, 8, 0.2 From Dual Union All
            Select 109, 1003, 900, 8, 0.1 From Dual Union All
            Select 109, 1004, 900, 9, 0.3 From Dual Union All
            Select 110, 1005, 700, 9, 0.1 From Dual 
        ),
    customers(CUSTOMERID, COMPANYNAME, CONTACTNAME, CONTACTTITLE, ADDRESS, CITY, REGION, POSTALCODE, COUNTRY, FAX) AS
        (
            Select 1, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 2, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 3, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 4, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 5, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 6, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual 
        )

你的3个答案如下：
x一个一个一个一个x一个一个二个一个x一个一个三个一个

赞(0）回复(0）举报 2023-01-20

我来回答

SQL ORACLE三个查询的问题

2条答案

相关问题

热门标签

最新问答