找到有序ITems中的TimeTaken ..

本文介绍了找到有序ITems中的TimeTaken ..的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有一个日期时间表是一列....我创建一个酒店应用程序，现在我需要，如果用户订购1项。 orderid tableid Timetaken（Mins）itemname datetime 1 1 10 Pizza 2013-06-20 05 ： 30 ： 33 。 343 PM then 5分钟后，我再添加一项。 orderid tableid Timetaken（Mins）itemname datetime 1 1 10 Burger 2013-06-20 05 ： 35 ： 33 。 343 PM 现在，我发现两个项目之间的时差...使用Sql Query ... Timedifference = 5mins，timetodelivery是15mins（减少5分钟因为这是等待时间）.... 之后，3分钟我再添加一个项目 orderid tableid Timetaken（分钟）itemname datetime 1 1 10 Burger 2013-06-20 05 ： 38 ： 33 。 343 PM 现在，等待时间是3分钟...... br /> 总时间= 30分钟 TimeDiffernece = {item1到item2是5分钟，item2到item3是3分钟} ==> 8分钟 现在，时间调整= 30分钟-8分钟= 22分钟 然后，我找到时差,,,但是22分钟完成并且额外的3分钟完成...然后我再添加两个它em orderid tableid Timetaken（分钟）itemname datetime 1 1 20 Dhosa 2013-06-20 06 ： 03 ： 33 。 343 PM 1 1 10 Juize 2013-06-20 06 ： 03 ： 33 。 343 PM 如何找到两个项目（30分钟）的当前时间？提前谢谢..... 解决方案 我为测试数据创建了这个表（我建议添加一个主键）： 创建表OrderItem（ OrderId INT NOT NULL， TableId INT NOT NULL， TimeTaken INT NOT NULL， ItemName VARCHAR（20）NOT NULL， OrderTime DATETIME NOT NULL ）; GO INSERT INTO OrderItem VALUES（1,2,10，'Pizza'，'2013-06-20 05：30：33.343 PM'）; INSERT INTO OrderItem VALUES（1,2,10，'Burger'，'2013-06-20 05：35：33.343 PM'）; INSERT INTO OrderItem VALUES（1,2,10，'Burger'，'2013-06-20 05：38：33.343 PM'）; INSERT INTO OrderItem VALUES（1,2,20，'Dhosa'，'2013-06-20 06：03：33.343 PM'）; INSERT INTO OrderItem VALUES（1,2,10，'Juize'，'2013-06-20 06：03：33.343 PM'）; GO 您的问题有点不清楚 - 但我认为这是基于分离不重叠的订单项。为了计算分离的订单，我做了这个： SELECT DISTINCT OrderId，TableId，OrderTime AS EarliestOrderTime FROM OrderItem AS T1 WHERE NOT EXISTS（ SELECT 1 FROM OrderItem AS T2 WHERE T2.OrderId = T1.OrderId AND T2.TableId = T1.TableId AND T2.OrderTime< T1.OrderTime AND DateAdd（mi，T2.TimeTaken，T2.OrderTime）> = T1.OrderTime） 这给出了2行 - 一行用于 17： 30：33.343 和另一个 18：03：33.343 。 I将SQL-Select转换为CTE，以便我可以引用它两次，以便我可以将它连接到关联的订单行： WITH SeparatedOrders_CTE（OrderId，TableId，EarliestOrderTime） AS （ SELECT DISTINCT OrderId，TableId，OrderTime AS EarliestOrderTime FROM OrderItem AS T1 WHERE NOT EXISTS（ SELECT 1 FROM OrderItem AS T2 WHERE T2.OrderId = T1.OrderId AND T2.TableId = T1.TableId AND T2。 OrderTime< T1.OrderTime AND DateAdd（mi，T2.TimeTaken，T2.OrderTime）> = T1.OrderTime）） SELECT SO1.OrderId，SO1.TableId，SO1.EarliestOrderTime， SUM（T1.TimeTaken）AS TotalTimeTaken， DateDiff（mi，SO1.EarliestOrderTime，MAX（T1.OrderTime））AS WaitingTime，（SUM（T1.TimeTaken） - DateDiff（mi， SO1.EarliestOrderTime，MAX（T1.OrderTime）））AS TimeToDelivery FROM SeparatedOrders_CTE as SO1 INNER JOIN OrderItem AS T1 ON T1.OrderId = SO1.OrderId AND T1。 TableId = SO1.TableId AND T1.OrderTime> = SO1.EarliestOrderTime WHERE NOT EXISTS（ SELECT 1 FROM SeparatedOrders_CTE AS SO2 WHERE SO2.OrderId = SO1.OrderId AND SO2.TableId = SO1.TableId AND SO2.EarliestOrderTime> SO1.EarliestOrderTime AND SO2.EarliestOrderTime< = T1.OrderTime） GROUP BY SO1.OrderId，SO1 .TableId，SO1.EarliestOrderTime ORDER BY SO1.OrderId，SO1.TableId，SO1.EarliestOrderTime 给出： OrderId TableId EarliestOrderTime TotalTimeTaken WaitingTime TimeToDelivery -------- -------- --- -------------------- -------------- ----------- ------------- - 1 2 2013 -06-20 17:30:33 。 343 30 8 22 1 2 2013-06-20 18:03:33 。 343 30 0 30 您的问题确实说明如果客户在用餐期间在餐桌之间移动会发生什么。我希望 TableId 实际上应该移动到父表（由 OrderId 键控）。 替代方法可能是使用SQL Server的窗口函数 [ ^ ]或游标。 我希望有所帮助。 问候 Andy I have one table with datetime is a one column....i create a hotel application ,Now i need,if user order 1 item. orderid tableid Timetaken(Mins) itemname datetime 1 1 10 Pizza 2013-06-20 05:30:33.343 PM then after 5 minutes ,i add another One item.orderid tableid Timetaken(Mins) itemname datetime 1 1 10 Burger 2013-06-20 05:35:33.343 PMNow,i find the timedifference between two item....using Sql Query...Timedifference=5mins and timetodelivery is 15mins(Reduce the 5 mins because this is waiting time)....After,3 mins i add another one item orderid tableid Timetaken(Mins) itemname datetime 1 1 10 Burger 2013-06-20 05:38:33.343 PMNow,the waiting time is 3mins....Totaltime=30minsTimeDiffernece={item1 to item2 is 5mins and item2 to item3 is 3mins}==>8minsNow,the timetodelivery=30mins-8mins=22minsThen,I find the time difference,,,But after 22 mins completed and extra 3 mins completed ...then i add another two item orderid tableid Timetaken(Mins) itemname datetime 1 1 20 Dhosa 2013-06-20 06:03:33.343 PM 1 1 10 Juize 2013-06-20 06:03:33.343 PMHow to find the Current time taken in two items(30mins)??Advance thanks..... 解决方案 I created this table for the test data (I recommend adding a primary key):CREATE TABLE OrderItem (OrderId INT NOT NULL,TableId INT NOT NULL,TimeTaken INT NOT NULL,ItemName VARCHAR(20) NOT NULL,OrderTime DATETIME NOT NULL);GOINSERT INTO OrderItem VALUES (1, 2, 10, 'Pizza', '2013-06-20 05:30:33.343 PM');INSERT INTO OrderItem VALUES (1, 2, 10, 'Burger', '2013-06-20 05:35:33.343 PM');INSERT INTO OrderItem VALUES (1, 2, 10, 'Burger', '2013-06-20 05:38:33.343 PM');INSERT INTO OrderItem VALUES (1, 2, 20, 'Dhosa', '2013-06-20 06:03:33.343 PM');INSERT INTO OrderItem VALUES (1, 2, 10, 'Juize', '2013-06-20 06:03:33.343 PM');GOYour question was a bit unclear - but I think it is based on separating the order-items that don't overlap. To work out the separated orders I did this:SELECT DISTINCT OrderId, TableId, OrderTime AS EarliestOrderTimeFROM OrderItem AS T1WHERE NOT EXISTS (SELECT 1 FROM OrderItem AS T2WHERE T2.OrderId = T1.OrderIdAND T2.TableId = T1.TableIdAND T2.OrderTime < T1.OrderTimeAND DateAdd(mi, T2.TimeTaken, T2.OrderTime) >= T1.OrderTime)This gives 2 rows - one for 17:30:33.343 and the other for 18:03:33.343.I converted that SQL-Select into a CTE so that I can reference it twice so that I can join it to the associated order lines:WITH SeparatedOrders_CTE (OrderId, TableId, EarliestOrderTime)AS(SELECT DISTINCT OrderId, TableId, OrderTime AS EarliestOrderTimeFROM OrderItem AS T1WHERE NOT EXISTS (SELECT 1 FROM OrderItem AS T2WHERE T2.OrderId = T1.OrderIdAND T2.TableId = T1.TableIdAND T2.OrderTime < T1.OrderTimeAND DateAdd(mi, T2.TimeTaken, T2.OrderTime) >= T1.OrderTime))SELECT SO1.OrderId, SO1.TableId, SO1.EarliestOrderTime,SUM(T1.TimeTaken) AS TotalTimeTaken,DateDiff(mi, SO1.EarliestOrderTime, MAX(T1.OrderTime)) AS WaitingTime,(SUM(T1.TimeTaken) - DateDiff(mi, SO1.EarliestOrderTime, MAX(T1.OrderTime))) AS TimeToDeliveryFROM SeparatedOrders_CTE as SO1INNER JOIN OrderItem AS T1ON T1.OrderId = SO1.OrderIdAND T1.TableId = SO1.TableIdAND T1.OrderTime >= SO1.EarliestOrderTimeWHERE NOT EXISTS (SELECT 1 FROM SeparatedOrders_CTE AS SO2WHERE SO2.OrderId = SO1.OrderIdAND SO2.TableId = SO1.TableIdAND SO2.EarliestOrderTime > SO1.EarliestOrderTimeAND SO2.EarliestOrderTime <= T1.OrderTime)GROUP BY SO1.OrderId, SO1.TableId, SO1.EarliestOrderTimeORDER BY SO1.OrderId, SO1.TableId, SO1.EarliestOrderTime Which gives:OrderId TableId EarliestOrderTime TotalTimeTaken WaitingTime TimeToDelivery-------- -------- ----------------------- -------------- ----------- --------------1 2 2013-06-20 17:30:33.343 30 8 221 2 2013-06-20 18:03:33.343 30 0 30Your question does say what would happen if customers move between tables during the meal. I would expect that TableId should in-fact be moved to a parent table (keyed by OrderId).Alternative methods might be to use SQL Server's windowing functions[^] or a cursor.I hope that helps.RegardsAndy 这篇关于找到有序ITems中的TimeTaken ..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！