我有桌子seller_id, transaction_type, sub_total, total_commission_fee, refund_fee, cancellation_fee3 transaction_typepayment, cancel, refund我想獲得每個(gè)賣家 ID 的 sub_total、total_commission_fee、refund_fee、cancellation_fee 的總和sampleseller_id transaction_type sub_total total_commission_fee refund_fee cancellation_fee3 order 40 0 0 04 order 10 0 0 0 3 cancel 0 0 0 3 3 refund 28 0 2 0我想要這樣的結(jié)果seller_id payment_total(sum of all sub_total transaction_type order) cancel_total(sum of all cancellation_fee transaction_type cancel) refund_total (sum of all refund_fee transaction_type refund)我可以在沒(méi)有交易類型的情況下獲得總計(jì)。Transaction::groupBy('seller_id') ->selectRaw('sum(sub_total) as total, seller_id')有沒(méi)有辦法得到我想要的結(jié)果。否則我必須做一個(gè)獲取請(qǐng)求并遍歷每個(gè)請(qǐng)求。當(dāng)表變大時(shí)，這可能會(huì)導(dǎo)致問(wèn)題這種操作叫什么？
2 回答
慕萊塢森
TA貢獻(xiàn)1810條經(jīng)驗(yàn) 獲得超4個(gè)贊
您可以使用IF(condition, value_if_true, value_if_false)對(duì)值求和：
Transaction::groupBy('seller_id')
->selectRaw('SUM(IF(transaction_type = "order", sub_total, 0)) AS payment_total,
SUM(IF(transaction_type = "cancel", cancellation_fee, 0)) AS cancel_total,
SUM(IF(transaction_type = "refund", refund_fee, 0)) AS refund_total,
seller_id')
繁花不似錦
TA貢獻(xiàn)1851條經(jīng)驗(yàn) 獲得超4個(gè)贊
您可以通過(guò)賣家 ID（seller_id）直接與 group 一起使用。請(qǐng)嘗試使用以下查詢
Transaction::groupBy('seller_id')
->selectRaw('SUM(sub_total) AS payment_total,
SUM(cancellation_fee) AS cancel_total,
SUM(refund_fee) AS refund_total,
seller_id')- 2 回答
- 0 關(guān)注
- 86 瀏覽
添加回答
舉報(bào)
0/150
提交
取消