在另一个表中添加行存在标志，并使用 group by-解网

问：

对于下面的查询，我想有一个标志，其计算结果为 0 如果表中不存在 from 视图和否则为 1isHoldbillNoviewBillsonHold

select max(t.id) TrackingID , max(vb.billNo) billNo, cb.id  ,
max(case when vb.[count] > 1 then 1 else 0 end) isMultiple ,
max(case when t.TrackingID = 31 then 1 else 0 end) IsCancelled,
max(case when exists (select 1 from OnHold oh
where oh.billNo = billNo) then 1 else 0 end) IsHold

from viewBills vb
join tracking t on vb.billNo = t.billNo
join customerBills cb on vb.billNo = cb.billNo 
join customerPieces cp on  cb.id = cp.customerBillId
where cb.statusid <> 3
group by cb.id

我在执行时收到此错误

Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery.

这是合理的，但如何实现呢？

sql sql server 布尔逻辑

select max(t.id) as TrackingID , max(vb.billNo) as billNo, cb.id ,
       max(case when vb.[count] > 1 then 1 else 0 end) as isMultiple,
       max(case when t.TrackingID = 31 then 1 else 0 end) as IsCancelled,
       max(case when oh.billNo is not null then 1 else 0 end) as IsHold
from viewBills vb join
     tracking t
     on vb.billNo = t.billNo join
     customerBills cb
     on vb.billNo = cb.billNo  join
     customerPieces cp
     on cb.id = cp.customerBillId outer apply
     (select top (1) oh.*
      from OnHold oh
      where oh.billNo = cb.billNo
     ) oh
where cw.statusid <> 3
group by cb.id;

select max(t.id) TrackingID , max(vb.billNo) billNo, cb.id  ,
max(case when vb.[count] > 1 then 1 else 0 end) isMultiple ,
max(case when t.TrackingID = 31 then 1 else 0 end) IsCancelled,
max(case when oh.billNo IS NOT NULL then 1 else 0 end) IsHold

from viewBills vb
join tracking t on vb.billNo = t.billNo
join customerBills cb on vb.billNo = cb.billNo 
join customerPieces cp on  cb.id = cp.customerBillId
LEFT OUTER JOIN OnHold oh ON oh.billNo = vb.billNo
where cb.statusid <> 3
group by cb.id

上一个：SQL 问题 - 我的 WHERE 子句有什么问题

下一个：SQL 根据列值条件返回行

在另一个表中添加行存在标志，并使用 group by

Add flag for row existence in another table with group by

评论

评论