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

Question

我有一个名为“bills”的表格，如下所示

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;

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

Answer 1

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