你可以用 row_number() :

select a.date, a.id, a.foo, a.purchases,
       (case when a.seqnum = 1 then b.budget end) as budget
from (seleect a.*, row_number() over (partition by date, id order by purchases desc) as seqnum
      from a 
     ) a
     b
     using (date, id);

在b inner查询中，将以下内容添加到所选列：
行号（）在（按日期划分，按采购说明排序）上作为sequencenumber
然后在左边的连接中添加“and b.sequencenumber=1”
如果您真的对所有内容进行硬编码，而不是从实际表中进行选择，则可能是：

WITH a As
(
SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,10 as purchases  Union all
SELECT '2020-04-01', 'test123', 'abc', 0   Union all
SELECT '2020-04-01', 'test123', 'abc', 0
),
b as
(
SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,50 as budget
)
select
a.date,a.id,a.foo,a.purchases,budget
from a
LEFT JOIN b
ON a.date = b.date
    AND a.id = b.id
    AND a.purchases > 0

否则，如果在内部“b”查询中有一个表。然后是这样的：

WITH a As
(
SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,10 as purchases  Union all
SELECT '2020-04-01', 'test123', 'abc', 0   Union all
SELECT '2020-04-01', 'test123', 'abc', 0
),
b as
(
SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,50 as budget,
  ROW_NUMBER() OVER(PARTITION BY date ORDER BY Id) SequenceNumber
)
select
a.date,a.id,a.foo,a.purchases,budget
from a
LEFT JOIN b
ON a.date = b.date
    AND a.id = b.id
    AND b.SequenceNumber = 1