我一直在尝试编写下面的SQL查询，并获得如下查询所给出的结果

Question

select
     TRN_ADP_Items_Tracker.request_id,
     TRN_ADP_Stationary_Request.requester_name,
     item_id,   
    case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=6 then sum(item_qty_traded) else 0 end as JUNE,
    case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=7 then sum(item_qty_traded) else 0 end as JULY,
    case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=8 then sum(item_qty_traded) else 0 end as AUG

  from TRN_ADP_Items_Tracker
  join TRN_ADP_Stationary_Request
  on TRN_ADP_Items_Tracker.request_id  =  TRN_ADP_Stationary_Request.stationary_request_id
  group by TRN_ADP_Stationary_Request.requester_name,item_id,
      MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE);

request requester item
_id      _name    _id    JUNE   JULY    AUG 
3   Prasad    150    2  0   0
3   Prasad    154    2  0   0
1   Sneha     150    15 0   0
1   Sneha     150    0  15  0
1   Sneha     150    0  0   6
1   Sneha   154 4   0   0

但在这里我想要的结果如下：

request_id  requester_name  item_id  JUNE  JULY  AUG 
1        Sneha      150      15     15    6

如果前3列数据相同，那么月份数据应该在1行中出现，如上所述。

Answer 1

您需要将GROUP_ID更正为select中只有三列

select
 TRN_ADP_Items_Tracker.request_id,
 TRN_ADP_Stationary_Request.requester_name,
 item_id

从…

group by TRN_ADP_Stationary_Request.requester_name,item_id,
  MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)

Answer 2

需要从选择列表中取出request_id列。

select
         --TRN_ADP_Items_Tracker.request_id,
         TRN_ADP_Stationary_Request.requester_name,
         item_id,   
        case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=6 then sum(item_qty_traded) else 0 end as JUNE,
        case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=7 then sum(item_qty_traded) else 0 end as JULY,
        case when MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE)=8 then sum(item_qty_traded) else 0 end as AUG

      from TRN_ADP_Items_Tracker
      join TRN_ADP_Stationary_Request
      on TRN_ADP_Items_Tracker.request_id  =  TRN_ADP_Stationary_Request.stationary_request_id
      group by TRN_ADP_Stationary_Request.requester_name,item_id,
          MONTH(TRN_ADP_Items_Tracker.ITEM_QTY_TRADE_DATE);