Oracle SQL Query - 如果客户在多个订单中有超过3个项目，则将其纳入查询(Oracle SQL Query - Include customer in query if they have more than 3 items across multiple orders)
编辑：添加了我想出的查询。
我是SQL初学者(将其作为我目前正在使用的数据库管理课程的一部分进行教学)。 我的一个项目是创建一个围绕客户和订单的数据库。 对于这个项目，我必须制作四个表格：
Customer (PK = CustomerID),
Invoice (PK = InvoiceID/ FK = CustomerID),
Product (PK = ProductSKU), and
Invoice_Item (PK = FK Invoice ID + FK ProductSKU).
我必须提出一个问题，询问“客户订购了超过3件某件商品的问题？” 该查询包含CustomerID (C_ID), CustomerName (C_BUS), InvoiceID (I_NUM), ProductSKU (P_SKU),和ProductAmountOrdered (II_ORDERED) ( Invoice_Item表的属性)的字段。 另请注意，查询中的Invoice_ID和ProductSKU属性来自Invoice_Item表。 现在，我在一些联接的帮助下回答了这个问题，但是有一个问题。 如果客户在多个订单中订购了3件以上的商品(例如，他们在一个订单中订购2件，另一个订购2件)，那么客户仍然满足标准。 这是绊倒我的部分。 做这件事的最好方法是什么？ 我环顾四周，并没有发现任何似乎能解决这个问题的事情。 任何帮助将不胜感激！
这是我迄今为止的代码：
SELECT CUSTOMER.C_ID, C_BUS, P_SKU, INVOICE.I_NUM, INVOICE_ITEM.II_ORDERED
FROM CUSTOMER, INVOICE, INVOICE_ITEM
WHERE CUSTOMER.C_ID = INVOICE.C_ID
AND INVOICE.I_NUM = INVOICE_ITEM.I_NUM
AND INVOICE_ITEM.P_SKU = 'P0548'
AND II_ORDERED > 3
ORDER BY C_BUS DESC;
我明白，这可能不是最好的语法，但这是我使用我的教科书作为参考。
EDIT: Added the query I came up with.
I'm a beginner at SQL (having it be taught as part of a database management class I am currently taking). One of my projects is to create a database revolving around customers and their orders. For this project, I have to make four tables:
Customer (PK = CustomerID),
Invoice (PK = InvoiceID/ FK = CustomerID),
Product (PK = ProductSKU), and
Invoice_Item (PK = FK Invoice ID + FK ProductSKU).
I have to make a query that asks the question of "What customers have ordered more than 3 of a certain item?" The query contains the fields of the CustomerID (C_ID), CustomerName (C_BUS), InvoiceID (I_NUM), ProductSKU (P_SKU), and ProductAmountOrdered (II_ORDERED) (attribute of the Invoice_Item table). Also note that the Invoice_ID and ProductSKU attributes in the query are coming from the Invoice_Item table. Now, I answered the question with the help of some joins, but there is a catch. The customer still satisfies the criteria if they have ordered more than 3 items across multiple orders (say they order 2 in one order and 2 in another). This is the part that is tripping me up. What is the best way to go about doing this? I have looked around and haven't found anything that seems like it would solve this question. Any help wold be greatly appreciated!
Here is the code that I have so far:
SELECT CUSTOMER.C_ID, C_BUS, P_SKU, INVOICE.I_NUM, INVOICE_ITEM.II_ORDERED
FROM CUSTOMER, INVOICE, INVOICE_ITEM
WHERE CUSTOMER.C_ID = INVOICE.C_ID
AND INVOICE.I_NUM = INVOICE_ITEM.I_NUM
AND INVOICE_ITEM.P_SKU = 'P0548'
AND II_ORDERED > 3
ORDER BY C_BUS DESC;
I understand that this might not be the best syntax, but this is what I managed using my textbook as a reference.
原文：https://stackoverflow.com/questions/42915866
更新时间：2019-09-07 00:19
相关问答
使用MIN和MAX获取第一个和最后一个订单日期，使用julianday()计算范围： SELECT customers.name,
customers.customer_id,
COUNT(orders.order_id) AS Orderscount,
MIN(col_name) AS firstOrder,
MAX(col_name) AS lastOrder,
ROUND(julianday(MAX(col_name)) - julianday(MIN(co
...
尝试这个 SELECT U.UserID, COUNT(O.OrderID) OrderCount
FROM Users U INNER JOIN Orders O ON U.UserID = O.UserID
INNER JOIN Line-Items L ON O.OrderID = L.OrderID
Where L.manufacturer = 'X-Parts'
Group BY U.UserID
Having count(O.orderID) >= 1
示例
...
SELECT
o.CustomerId,
COUNT(DISTINCT o.OrderId) AS OrderCount,
COUNT(oi.OrderItemId) AS OrderItemCount,
COUNT(oi.OrderItemId) / COUNT(DISTINCT o.OrderId) avg
FROM OrderItem oi
INNER JOIN Order o ON o.OrderId = oi.OrderId
WHERE o.Cate
...
每个都是单独的陈述。 通过Connection#createStatement()或通过对Statement#execute(String)多个SQL调用，通过单独的Statement对象一次发出一个。 相反，您在一个描述的陈述中想要它们的原因是什么？ Each of those are separate statements. Issue one at a time via separate Statement objects via Connection#createStatement() o
...
尝试这个 SELECT customerID, name
FROM Customer
WHERE noOfDvdHiring > (select AVG(noOfDvdHiring) from customer);
Try this SELECT customerID, name
FROM Customer
WHERE noOfDvdHiring > (select AVG(noOfDvdHiring) from customer);
SELECT DISTINCT customerid
FROM ORDERS O
INNER JOIN PRODUCTS P
ON (P.prodid = O.prodid)
INNER JOIN ORDERLINES OL
ON (O.prodid = OL.prodid AND O.orderid = P.orderid)
GROUP BY O.customerid,O.orderid
HAVING
COUNT(DISTINCT p.category) >= 3
在你的JOINS风格(我最
...
只是根据这些值是空还是null来构建查询 如果你开始使用的东西总是如1=1那么你只需应用其他过滤器并用AND启动它们 sql = "SELECT project.start_date, project.projectID, project.project_title, customer.customer, " &\
"project.end_date, project.project_manager, project_status.project_status " &\
"FROM pr
...
您需要加入订单详细信息并按聚合进行分组： SELECT c.CompanyName AS "Company Name",
c.Address AS "Address",
sum(od.Unitprice * od.quantity) as "Orders"
FROM Customers c LEFT JOIN
Orders o
ON c.CustomerID = o.CustomerID left join
[Order Details]
...
对您的要求最宽松的解释是将最高买入价与最低卖出价进行比较，看看前者是否大于后者。 可以实现的一种方法是通过Id进行分组并使用聚合来获得这些高买入和低卖出价格。 SELECT Id
FROM Orders
GROUP BY Id
HAVING MAX(CASE WHEN buy = 'true' THEN price END) >
MIN(CASE WHEN buy = 'false' THEN price END)
The most lenient interpretation
...
不应该是这样的吗？ SELECT * FROM Orders NATURAL JOIN Items_Orders NATURAL JOIN Items
WHERE customer= 'username'
Shouldn't it be something like this? SELECT * FROM Orders NATURAL JOIN Items_Orders NATURAL JOIN Items
WHERE customer= 'username'
772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
