如果值存在，則取得記錄總和，將其視為 1，而不是精確值

Question

我需要“打開”為 2 並單擊為 1 但現在我越來越喜歡打開為 5 並單擊為 2

"SELECT 
COALESCE(sum(requested),0) as requested,
COALESCE(sum(processed),0) as processed,
COALESCE(sum(open),0) as open,
COALESCE(sum(click),0) as click,
COALESCE(sum(dropped),0) as dropped,
COALESCE(sum(delivered),0) as delivered,
COALESCE(sum(spamreport),0) as spamreport,
COALESCE(sum(deferred),0) as deferred,
COALESCE(sum(bounce),0) as bounce,
COALESCE(sum(unsubscribe),0) as unsubscribe
FROM CAMPAIGN_REPORTS WHERE _kftCampaingID='".$campaignId."'"

Answer 1

sum() 按預期工作。如果您希望將 0 以上的任何值視為 1，請編寫以下 if 條件。話雖如此，coalesce 在這裡看起來是多餘的。

sum(if(open is not null and open > 0, 1, 0)),
sum(if(click is not null and click > 0, 1, 0))