将值应用于组

需要帮助，如果具有相同Groupid的任何ID在"应付款"中为"是"，请在"结果"中添加"是"值，否则为空。这应该适用于以数百个GroupID分组的数百个ID。

分组ID是222是333是444是555是777是888

您的问题有点不清楚，因为缺少关于约束、表中所有值等的信息。根据给定的信息，您的任务可以通过以下查询完成：

with groupData as
(
Select groupid,payable from put_your_table_name_here
where payable is not null
group by groupid
)
Select pt.id
,pt.groupId
,gd.payable
,pt.result
from put_your_table_name_here pt
left join groupData gd on gd.groupid=pt.groupid

查询有它的缺点——您应该提供更多关于约束的信息，但通常情况下它应该可以工作。如果您不想在payable列中有null值，可以将左联接更改为联接。

WITH CTE(ID,    GroupID,    Payable)AS
(
SELECT 111,'A','YES'
UNION ALL
SELECT 222,'A',''
UNION ALL
SELECT 333,'A',''
UNION ALL
SELECT 444,'B',''
UNION ALL
SELECT 555,'B','YES'
UNION ALL
SELECT 777,'B',''
UNION ALL
SELECT 888,'C',''

)
SELECT C.ID,C.GroupID,C.Payable,F.FLAG
FROM CTE AS C
JOIN
(
SELECT X.GROUPID,MAX(PAYABLE)FLAG
FROM CTE AS X
GROUP BY X.GroupID
)F ON C.GroupID=F.GroupID
ORDER BY C.ID;

你可以试试这样的或

SELECT C.ID,C.GroupID,C.Payable,
FIRST_VALUE(C.PAYABLE)OVER(PARTITION BY C.GROUPID ORDER BY C.PAYABLE    DESC)XCOL
FROM CTE AS C
ORDER BY C.ID;

with data (ID,GroupID,Payable) as (
Select 111, 'a',    'Yes' from dual union all
Select 222, 'a',    null from dual union all
Select 333, 'a',    null from dual union all
Select 444, 'b',    null from dual union all
Select 555, 'b',    'Yes' from dual union all
Select 777, 'b',    null from dual union all
Select 888, 'c',    null from dual
)
,result as(
select  GroupID, case when Count(*) > 1 then 'Yes' else null end Result
from data
group by  GroupID)
Select * from data
Join result on data.GroupID = result.GroupID
order by data.ID,data.GroupID

Db小提琴链接

相关内容

最新更新

热门标签：