如何在保留 group by 子句的同时在同一个表中再添加一列-PHP中文网问答

文章专题 AI工具学习下载问答源码最近更新

PHP

会员中心讲师中心微信公众号

如何在保留 group by 子句的同时在同一个表中再添加一列

P粉729436537 2024-04-01 10:43:59

[MySQL讨论组]

818

表 1 中的列列表： Plan_ID、Claim_id、Patient_id、B_OR_G

表2中的列列表：奥吉德、沙普兰德

select distinct a.Plan_ID
       , a.Total_Claims
       , Total_Patients
       ,  b.PERIOD
       , b.ORGID,a.B_OR_G
FROM (Select distinct Plan_ID
             , count(distinct Claim_id) as Total_Claims
             , count(distinct Patient_id)  as Total_Patients 
      from table1 group by 1) a
JOIN (select *
             , row_number() over (partition by ORGID,SHAPLANID order by PROCESSINGDATE desc) as rank
      from table2 qualify rank = 1) b
ON LTRIM(a.PLAN_ID, '0') = b.SHAPLANID

在上面的查询中，我想从 table1（即 a）中再提取一个名为“B_or_G”的列，但不会干扰 group by 子句，因为根据我们的要求这是必要的。

有没有更好的方法来做到这一点？谢谢！！

P粉729436537

全部回复(1)

P粉4477850312024-04-02 00:19:32 1楼

我认为您可以使用 ANY_VALUE(B_or_G)

举个例子：

select distinct a.Plan_ID
       , a.Total_Claims
       , Total_Patients
       ,  b.PERIOD
       , b.ORGID,a.B_OR_G
FROM (Select distinct Plan_ID
             , count(distinct Claim_id) as Total_Claims
             , count(distinct Patient_id)  as Total_Patients 
             , ANY_VALUE(B_OR_G)
      from table1 group by 1) a
JOIN (select *
             , row_number() over (partition by ORGID,SHAPLANID order by PROCESSINGDATE desc) as rank
      from table2 qualify rank = 1) b
ON LTRIM(a.PLAN_ID, '0') = b.SHAPLANID

赞 +0

添加回复

专题推荐

更多>

热门话题

热门教程

更多>