m******u
发帖数: 12400 | 1 question: A "Single Item Order" is a customer order where only one item is
ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.
There is a table:
SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID,
UnitPrice, UnitPriceDiscount)
My understanding is: for any order if ProductID = 1, then the order is a
single-item order. The this question is only a simple select query. Actually
, the question is a medium level problem. I guess I misunderstand the
problem.Any idea? | m*********a
发帖数: 3299 | 2 这个SaleOrderDetailID是啥意思。
我觉着这个意思是一个order有一个SalesOrderID
然后可以order多个product
所以应该选count(SalesOrderID)=1 and OrderQty=1的记录
SalesOrderID OrderQty ProductID
1 1 123
1 1 456
2 1 123
3 2 123
上面的数据中，只有SalesOrderID 2才是你要的结果
Order.
Actually
【在 m******u 的大作中提到】
: question: A "Single Item Order" is a customer order where only one item is
: ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.
: There is a table:
: SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID,
: UnitPrice, UnitPriceDiscount)
: My understanding is: for any order if ProductID = 1, then the order is a
: single-item order. The this question is only a simple select query. Actually
: , the question is a medium level problem. I guess I misunderstand the
: problem.Any idea?
| a9
发帖数: 21638 | 3 应该是有两个表，SalesOrder表和这个SalesOrderDetail表。
SalesOrderID是第一个表的ID，SalesOrderDetailID是这个表的ID
这样可能就需要group by什么的来统计了。
Order.
Actually
【在 m******u 的大作中提到】
: question: A "Single Item Order" is a customer order where only one item is
: ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.
: There is a table:
: SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID,
: UnitPrice, UnitPriceDiscount)
: My understanding is: for any order if ProductID = 1, then the order is a
: single-item order. The this question is only a simple select query. Actually
: , the question is a medium level problem. I guess I misunderstand the
: problem.Any idea?
| m******u
发帖数: 12400 | 4 谢谢楼上两位，你们的理解是对的，一个orderid（一个order）可以有多个productid
（多个product）
只有orderid和production 1对1 对应的才是single-item order。 | j*m
发帖数: 833 | 5 Use this to identify all orders with single item order
select SalesOrderID
where OrderQty = 1 -- if QorderQty > 1, can't be a single item order
group by 1
having count(distict ProductID) = 1 ;
Everything else should follow
Order.
Actually
【在 m******u 的大作中提到】
: question: A "Single Item Order" is a customer order where only one item is
: ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.
: There is a table:
: SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID,
: UnitPrice, UnitPriceDiscount)
: My understanding is: for any order if ProductID = 1, then the order is a
: single-item order. The this question is only a simple select query. Actually
: , the question is a medium level problem. I guess I misunderstand the
: problem.Any idea?
| m******u
发帖数: 12400 | 6 singe-item order doesn't mean buying one thing (quantity = 1).
My understanding is: Select count(ProductID) ItemPerOrder group by
SalesOrderID having ItemPerOrder = 1. | m*********a
发帖数: 3299 | 7 Select SalesOrderID
from SalesOrderDetail
where OrderQTY=1
Group by SalesOrderID
having Count(SalesOrderID)=1
【在 m******u 的大作中提到】
: singe-item order doesn't mean buying one thing (quantity = 1).
: My understanding is: Select count(ProductID) ItemPerOrder group by
: SalesOrderID having ItemPerOrder = 1.
| s**********o
发帖数: 14359 | |
|
