如何实现这个选择查询？ [英] How to achieve this select query?

问题描述

我正在尝试这个选择查询

 选择 sum（ Recp_Qty  -  Issd_Qty） as  AJob_av 
 来自 Reciepts 其中 JobNo！= '  stk' 
 组 按 IT_Name 
 
 联盟 
 
 选择 sum（Recp_Qty  -  Issd_Qty） as  AStk_av 
 来自 Reciepts 其中 JobNo = '  stk' 
  group   by  IT_Name 

这是以这种格式提供输出

 AJob_a v 
  5  
  8  
  10  
  12  
  15  

但我希望以下面的格式显示

 AJob_av AStk_av 
  5   0  
  12   0  
  15   0  
  0   8  
  0   10  

我该怎么办？那个？

解决方案

只需删除联合，并括起你的select语句：

 选择 
（选择 sum（Recp_Qty  -  Issd_Qty）
 来自 Reciepts 其中 JobNo！= < span class =code-string>'  stk' 
  group  < span class =code-keyword> by  IT_Name） as  AJob_av，
（ select  sum（Recp_Qty  -  Issd_Qty）
 来自 Reciepts 其中 JobNo =   stk' 
  group   by  IT_Name） as  AStk_av 

 选择 sum（Recp_Qty  -  Issd_Qty）  AJob_av， 0   as  AStk_av 
  from  Reciepts  where  JobNo！= '  stk' 
  group   by  IT_Name 
 
  UNION  
 
 选择  0   as  AJob_av，sum（Recp_Qty  -  Issd_Qty） as  AStk_av 
 < span class =code-keyword> from  Reciepts 其中 JobNo = '  stk' 
  group   by  IT_Name 

I am trying this select query

select sum(Recp_Qty - Issd_Qty) as AJob_av
 from Reciepts where JobNo != 'stk'
 group by IT_Name

 Union

 select sum(Recp_Qty - Issd_Qty) as AStk_av
 from Reciepts where JobNo = 'stk'
 group by IT_Name

this is giving output in this format

AJob_av
5
8
10
12
15

but i want to display in below format

AJob_av    AStk_av
5           0
12          0
15          0
0           8
0           10

how can i do that?

解决方案

Just drop the union, and bracket your select statements:

select 
(select sum(Recp_Qty - Issd_Qty) 
 from Reciepts where JobNo != 'stk'
 group by IT_Name) as AJob_av,
(select sum(Recp_Qty - Issd_Qty)
 from Reciepts where JobNo = 'stk'
 group by IT_Name) as AStk_av

select sum(Recp_Qty - Issd_Qty) as AJob_av, 0 as AStk_av
 from Reciepts where JobNo != 'stk'
 group by IT_Name

UNION 

select 0 as AJob_av,sum(Recp_Qty - Issd_Qty) as AStk_av
 from Reciepts where JobNo = 'stk'
 group by IT_Name

这篇关于如何实现这个选择查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！