如果重复，则更改列的输出。
如果重复，则更改列的输出。
提问于 2014-01-02 01:21:24
我有一个表，它为客户购买的每一种产品都有行。我希望从SELECT查询中输出一个列，它根据客户是否有其他需要交付的项目来显示交付所需的时间。第一个项目需要5分钟才能交付，所有后续项目都需要2分钟才能交付，例如3个项目需要5+2+2=9分钟才能交付。
这就是我目前拥有的(使用w3schools上的Northwind示例数据库来测试查询)：
SELECT orders.customerid,
orders.orderid,
orderdetails.productid,
CASE((SELECT Count(orders.customerid)
FROM orders
GROUP BY orders.customerid))
WHEN 1 THEN '00:05'
ELSE '00:02'
END AS DeliveryTime
FROM orders
LEFT JOIN orderdetails
ON orderdetails.orderid = orders.orderid 这输出'00:05‘的每一个项目，由于计数在我的子查询(我想？)，有什么想法，如何解决这个问题？
回答 3
Stack Overflow用户
回答已采纳
发布于 2014-01-02 03:06:59
格雷戈里的回答很好，下面是我的尝试
-- Without each product line item listed
SELECT O.CustomerId,
O.OrderId,
COUNT(*) AS 'NumberOfProductsOrderd',
CASE COUNT(*)
WHEN 1 THEN 5
ELSE (COUNT(*) * 2) + 3
END AS 'MinutesToDeliverAllProducts'
FROM Orders AS O
INNER JOIN OrderDetails AS D ON D.OrderId = O.OrderId
GROUP BY O.CustomerId, O.OrderId
-- Without each product line item listed
SELECT O.CustomerId,
O.OrderId,
D.ProductId,
CASE
WHEN P.ProductsInOrder = 1 THEN 5
ELSE (P.ProductsInOrder * 2) + 3
END AS 'MinutesToDeliverAllProducts'
FROM Orders AS O
INNER JOIN OrderDetails AS D ON D.OrderId = O.OrderId
INNER JOIN (
SELECT OrderId, COUNT(*) AS ProductsInOrder
FROM OrderDetails
GROUP BY OrderId
) AS P ON P.OrderId = O.OrderId
GROUP BY O.CustomerId,
O.OrderId,
D.ProductId,
P.ProductsInOrderStack Overflow用户
发布于 2014-01-02 02:53:40
尝尝这个
SELECT orders.customerid,
orders.orderid,
orderdetails.productid,
numberorders,
2 * ( numberorders - 1 ) + 5 AS deleveryMinutes
FROM orders
INNER JOIN (SELECT orders.customerid AS countId,
Count(1) AS numberOrders
FROM orders
GROUP BY orders.customerid) t1
ON t1.countid = orders.customerid
LEFT JOIN orderdetails
ON orderdetails.orderid = orders.orderid
ORDER BY customerid Stack Overflow用户
发布于 2014-01-02 20:07:10
以下是所有感兴趣的人的最终代码：
SELECT O.CustomerId,
O.OrderId,
Group_Concat(D.ProductID) AS ProductID,
CASE COUNT(*)
WHEN 1 THEN 5
ELSE (COUNT(*) * 2) + 3
END AS 'MinutesToDeliverAllProducts'
FROM Orders AS O
INNER JOIN OrderDetails AS D ON D.OrderId = O.OrderId
GROUP BY O.CustomerId页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20875056
复制相关文章
相似问题
腾讯云开发者
