如何将GRACREQPROV_GRP的connector列用于GRFNMWRT_GRP子查询并实现排序逻辑？

问题分析 #

你遇到的“列未找到”错误，根源是**grfn子查询里引用的CONNECTOR并非你想要的GRACREQPROV_GRP表的列**：当前grfn子查询仅基于GRFNMWRT_GRP表，而GRACREQPROV_GRP（对应别名gpv）的CONNECTOR列不在这个子查询的上下文里，系统自然找不到你需要的字段。

你的需求是根据GRACREQPROV_GRP.connector的值计算rank，这里提供两种直接可行的修正方案：

方案一：主查询中直接计算rank（更简洁） #

既然已经通过左连接将gpv.CONNECTOR带入主查询上下文，直接在这里用CASE语句生成rank即可，无需在子查询中处理：

select distinct 
  -- 注意：原SQL中`gruser`表未在FROM/JOIN中声明，需补充关联或修正字段
  gruser.USERID as userid,
  gpv.CONNECTOR AS Server,
  req.REQNO AS Ticket_no,
  grfinst.CREATED_BY Ticket_created_by,
  zgrac.ZEMPNAME AS Ticket_created_by_NAME,
  grfn.CHANGED_BY Ticket_assigned_to,
  zgrac.ZEMPNAME AS Ticket_assigned_to_by_NAME,
  grfinst.INSTANCE_STATUS,
  grfinst.APPROVAL_STATUS,
  -- 直接基于gpv.CONNECTOR计算rank
  CASE 
    WHEN gpv.CONNECTOR IN ('ASP', 'P') THEN 1
    WHEN gpv.CONNECTOR LIKE 'GRP%' THEN 2
    ELSE NULL -- 可根据需求补充其他场景的默认值
  END AS rnk
from 
  `GRAC_GRP` req
LEFT JOIN 
  (select distinct MANDT,REQ_ID,CONNECTOR from`GRACREQPROV_GRP`) gpv
ON
  req.MANDT=gpv.MANDT
  AND req.REQ_ID=gpv.REQ_ID
left join 
  (select distinct MANDT,EXTERNAL_KEY,FINISHED_AT,CREATED_BY,INSTANCE_ID,PROCESS_ID, INSTANCE_STATUS, APPROVAL_STATUS,EXTERNAL_KEY_DIS from `GRFNMWRT_GRP`) grfinst 
on req.MANDT=grfinst.MANDT
AND req.REQNO=grfinst.EXTERNAL_KEY_DIS
LEFT JOIN 
  (select distinct MANDT,ZEMPID,ZEMPNAME FROM `ZGRAC_EMP_GRP`) zgrac
on grfinst.MANDT=zgrac.MANDT
AND grfinst.CREATED_BY=zgrac.ZEMPID
LEFT JOIN 
  (select distinct CHANGED_BY,INSTANCE_ID,LINEITEM_STATUS,TIMESTAMP_LONG
   from `GRFNMWRT_GRP`
  ) grfn
ON grfinst.INSTANCE_ID= grfn.INSTANCE_ID
AND grfn.CHANGED_BY= zgrac.ZEMPID
WHERE  
  REQ.REQTYPE IN (1,2,3,4,5,6,124,125)
  AND INSTANCE_STATUS IN("RUNNING","FINISHED")
  AND LINEITEM_STATUS IN ("APPROVED", "REJECTED", "ONHOLD") 

方案二：子查询中关联获取CONNECTOR计算rank #

如果必须在grfn子查询内处理rank逻辑，可通过关联GRAC_GRP和GRACREQPROV_GRP拿到目标CONNECTOR：

select distinct 
  -- 注意：原SQL中`gruser`表未在FROM/JOIN中声明，需补充关联或修正字段
  gruser.USERID as userid,
  gpv.CONNECTOR AS Server,
  req.REQNO AS Ticket_no,
  grfinst.CREATED_BY Ticket_created_by,
  zgrac.ZEMPNAME AS Ticket_created_by_NAME,
  grfn.CHANGED_BY Ticket_assigned_to,
  zgrac.ZEMPNAME AS Ticket_assigned_to_by_NAME,
  grfinst.INSTANCE_STATUS,
  grfinst.APPROVAL_STATUS,
  grfn.rnk
from 
  `GRAC_GRP` req
LEFT JOIN 
  (select distinct MANDT,REQ_ID,CONNECTOR from`GRACREQPROV_GRP`) gpv
ON
  req.MANDT=gpv.MANDT
  AND req.REQ_ID=gpv.REQ_ID
left join 
  (select distinct MANDT,EXTERNAL_KEY,FINISHED_AT,CREATED_BY,INSTANCE_ID,PROCESS_ID, INSTANCE_STATUS, APPROVAL_STATUS,EXTERNAL_KEY_DIS from `GRFNMWRT_GRP`) grfinst 
on req.MANDT=grfinst.MANDT
AND req.REQNO=grfinst.EXTERNAL_KEY_DIS
LEFT JOIN 
  (select distinct MANDT,ZEMPID,ZEMPNAME FROM `ZGRAC_EMP_GRP`) zgrac
on grfinst.MANDT=zgrac.MANDT
AND grfinst.CREATED_BY=zgrac.ZEMPID
LEFT JOIN 
  (select 
     grfn_inner.CHANGED_BY,
     grfn_inner.INSTANCE_ID,
     grfn_inner.LINEITEM_STATUS,
     grfn_inner.TIMESTAMP_LONG,
     -- 关联拿到gpv的CONNECTOR并计算rank
     CASE 
       WHEN gpv_inner.CONNECTOR IN ('ASP', 'P') THEN 1
       WHEN gpv_inner.CONNECTOR LIKE 'GRP%' THEN 2
       ELSE NULL
     END AS rnk
   from `GRFNMWRT_GRP` grfn_inner
   -- 关联GRAC_GRP和GRACREQPROV_GRP获取目标CONNECTOR
   JOIN `GRAC_GRP` req_inner ON grfn_inner.MANDT = req_inner.MANDT AND grfn_inner.EXTERNAL_KEY_DIS = req_inner.REQNO
   LEFT JOIN (select distinct MANDT,REQ_ID,CONNECTOR from`GRACREQPROV_GRP`) gpv_inner ON req_inner.MANDT=gpv_inner.MANDT AND req_inner.REQ_ID=gpv_inner.REQ_ID
  ) grfn
ON grfinst.INSTANCE_ID= grfn.INSTANCE_ID
AND grfn.CHANGED_BY= zgrac.ZEMPID
WHERE  
  REQ.REQTYPE IN (1,2,3,4,5,6,124,125)
  AND INSTANCE_STATUS IN("RUNNING","FINISHED")
  AND LINEITEM_STATUS IN ("APPROVED", "REJECTED", "ONHOLD") 

内容的提问来源于stack exchange，提问作者Sandeep Mohanty