SQL:嵌套查询中的 Geeting Order 状态时间戳 [英] SQL : Geeting Order's status timestamp in a nested query

问题描述

我正在处理一个订单表，其中包含有关已分配订单的所有详细信息.示例数据库示例是

I'm working on a Order Table which has all the details regarding the order's that were allocated. The sample DB example is

Order ID   Order Status     Action Date
23424      ALC              1571467792094280
23424      PIK              1571467792999990
23424      PAK              1571469792999990
23424      SHP              1579967792999990
33755      ALC              1581467792238640
33755      PIK              1581467792238640
33755      PAK              1581467792238640
33755      SHP              1581467792238640

在表中我有订单 ID、状态、action_date(当订单状态根据更新时间戳有更新时更新的操作日期，action_date 是 unix 时间)

In the table I have order ID , status, action_date (the action dates updated when ever there is an update on order status against the update timestamp, the action_date is unix time)

我正在尝试编写一个可以为我提供订单 ID、ALC_AT、PIK_AT、PAK_AT、SHP_AT 的查询基本上所有时间戳更新都针对一行内的订单 ID，我知道可以通过嵌套查询来完成，但是，我无法弄清楚如何去做.

I'm trying to write a query that can provide me the Order ID, ALC_AT, PIK_AT, PAK_AT, SHP_AT Basically all the timestamp updates against a Order ID within one row, I know it can be done via Nested Query but, I'm unable to figure how how to do it.

任何帮助将不胜感激.

编辑(根据要求提供示例结果):

Edit (As asked to provide the sample result ) :

Order ID   Order Status     ALC_AT             PIK_AT            PAK_AT              SHP_AT
23424      SHP              1571467792094280   1571467792999990  1571469792999990    1579967792999990

推荐答案

我不确定在 mysql 中是如何完成的.但下面描述了它将如何在 Oracle 中完成.您可以在 mysql 中搜索更多 PIVOT 以帮助您.

I am not sure how it is done in mysql. But below describes how it will be done in Oracle. You can searh more for PIVOT in mysql to help you in the same.

select * 
  from (select order_id,
               status, 
               action_date 
          from order)  
 pivot (max(status) 
        for app_id in ( 'ALC' as 'ALC_AT', 'PIK' as 'PIK_AT', 'PAK' as 'PAK_AT', 'SHP' as 'SHP_AT'))

希望对您有所帮助.

为 mysql

select * 
  from (select "order.order_number",
               "shipment.status", 
               from_unixtime("action_date"/1000000) as "action_date"
          from order_table 
         where "order.order_number" = '2019-10-19-N2-6411')
  pivot (max("action_date")
         for "shipment_status" in ( 'ALC' AS 'ALC_AT', 'PIK' AS 'PIK_AT', 'PAK' 
                                     AS 'PAK_AT', 'SHP' AS 'SHP_AT')) 

这篇关于SQL:嵌套查询中的 Geeting Order 状态时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！