mysql - 使用 roll up、order by 和 group by 内连接两个表

Question

最近几天我被困住了，真的不知道该怎么办。
我有一个包含三个表的数据库来获取数据。我无法将汇总与内部联接和排序依据一起使用。
我有以下返回结果的查询。

SELECT  bb_catg.catg_name,
        bi_item_name,
        sum(bi_item_qty) as TotalQty,
        sum(bi_item_total) as Grandtotal
FROM bb_bill_items 
    inner join bb_items
    on  bb_bill_items.item_id = bb_items.item_id 
    inner join bb_catg 
    on bb_items.catg_id  = bb_catg.catg_id
    inner join bb_bill
    on bb_bill_items.bil_id  = bb_bill.bil_id
where dateandtime = '$date' and paymenttype = '$Status'
group by bb_bill_items.item_id
order by bb_catg.catg_name

结果

catg_name | bi_item_name | TotalQty | Grandtotal
------    | ------------ |--------- |-----------
PASTRIES  |Cupcake       |1         |270
PASTRIES  |Eclair        |1         |150
PASTRIES  |Mousse        |1         |350
           ----------
             total       |3         |770

任何帮助将不胜感激。

Answer 1

您可以更改以下查询来解决您的问题:

SELECT  bb_catg.catg_name,bi_item_name,
        sum(bi_item_qty) as TotalQty,sum(bi_item_total) as Grandtotal
FROM bb_bill_items 
inner join bb_items
    on  bb_bill_items.item_id = bb_items.item_id 
inner join bb_catg 
on bb_items.catg_id  = bb_catg.catg_id
inner join bb_bill
    on bb_bill_items.bil_id  = bb_bill.bil_id
where dateandtime = '$date' and paymenttype = '$Status'
group by bb_catg.catg_name,bb_items.bi_item_name
order by bb_catg.catg_name

可以在 Group By 子句中添加与其他列的聚合函数。