将两个sql查询合并为一个查询

7gyucuyw 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(422)

我有一张有结构的table

TicketID - ID of the Ticket
CreateTime - Time when the ticket is created
FinishTime - Time when the ticket is finished handled

我的目标是根据小时数找出这些值

Number of Tickets Received per hour
Number of Tickets handled per hour

到目前为止，我有两个不同的问题

Select count(Receipt.ID) as ReceiveCount,
       Receipt.hours as PST,
      (Receipt.hours+12.5) as IST 
from
      (Select ReceiptScanRequestTable.ReceiptScanRequestID as ID,
              from_unixtime(CreateTime/1000 , '%H') as hours 
       from 
              ReceiptScanRequestTable 
       where 
              CreateTime>{hour_filter_value}  
      ) as Receipt 
Group By 
      Receipt.hours

这将返回每小时收到的票数

Select count(Receipt.ID) as FinishCount,
           Receipt.hours as PST,
          (Receipt.hours+12.5) as IST 
    from
          (Select ReceiptScanRequestTable.ReceiptScanRequestID as ID,
                  from_unixtime(FinisheTime/1000 , '%H') as hours 
           from 
                  ReceiptScanRequestTable 
           where 
                  CreateTime>{hour_filter_value}  
          ) as Receipt 
    Group By 
          Receipt.hours

这将返回每小时完成的票数
我的目标是将这两个查询组合成一个查询！

sql mysql Database

来源：https://stackoverflow.com/questions/50260071/combine-two-sql-queries-into-one-single-query

2条答案

按热度按时间

798qvoo81#

试试这个：

select receivecount, finishcount from (Select count(Receipt.ID) as ReceiveCount,
       Receipt.hours as PST,
      (Receipt.hours+12.5) as IST 
from
      (Select ReceiptScanRequestTable.ReceiptScanRequestID as ID,
              from_unixtime(CreateTime/1000 , '%H') as hours 
       from 
              ReceiptScanRequestTable 
       where 
              CreateTime>{hour_filter_value}  
      ) as Receipt 
Group By 
      Receipt.hours) a 
cross apply
(Select count(Receipt.ID) as FinishCount,
           Receipt.hours as PST,
          (Receipt.hours+12.5) as IST 
    from
          (Select ReceiptScanRequestTable.ReceiptScanRequestID as ID,
                  from_unixtime(FinisheTime/1000 , '%H') as hours 
           from 
                  ReceiptScanRequestTable 
           where 
                  CreateTime>{hour_filter_value}  
          ) as Receipt 
    Group By 
          Receipt.hours)b

赞(0）回复(0）举报 2021-06-25

ogsagwnx2#

你可以用 union all 然后聚合：

select hours, sum(receive) as numreceived, sum(finish) as numfinished,
       (hours + 12.5) as IST 
from ((Select from_unixtime(CreateTime/1000 , '%H') as hours, 1 as receive, 0 as finish
       from ReceiptScanRequestTable 
       where CreateTime > {hour_filter_value}
      ) union all
      (Select from_unixtime(FinisheTime/1000 , '%H') as hours, 0, 1
       from ReceiptScanRequestTable 
       where CreateTime>{hour_filter_value}  
      )
     ) r
group by hours;

赞(0）回复(0）举报 2021-06-25

我来回答

将两个sql查询合并为一个查询

2条答案

相关问题

热门标签

最新问答