java - 使用group by在mysql中选择count并在每一行中重复计数

我的桌子叫做“账单”，如下所示

Bill         item          totalprice

BILL_1       Fossil Watch  9000
BILL_1       Fastrack      -
BILL_1       Fastrack      -
BILL_2       Woodlands     7000
BILL_2       Woodlands     -
BILL_3       Denim Shirt   9000
BILL_3       Levis Jean    -

现在我需要类似以下结构的结果

Bill         item          totalprice  bill_count

BILL_1       Fossil Watch  9000         3
BILL_1       Fastrack      -            3
BILL_1       Fastrack      -            3
BILL_2       Woodlands     7000         2
BILL_2       Woodlands     -            2
BILL_3       Denim Shirt   9000         2
BILL_3       Levis Jean    -            2

我尝试了以下查询，

SELECT bill,item,totalPrice,COUNT(bill) FROM bills GROUP BY bill;

但我没有得到预期的结果。

最佳答案

SELECT b.bill, b.item, b.totalPrice, tmp.cnt
FROM bills b
JOIN
(
   select bill, COUNT(bill) as cnt
   from bills
   group by bill
) tmp on tmp.bill = b.bill