mysql - 使用 group by 计算行数并显示所有行 MYSQL PHP

Question

我有一个这样的sql表:

id     fname     cat                  address        status

1      Bash      Wedding venue        ABC            a
2      Bash      Wedding venue        BCD            a
3      jash      Wedding venue        ABC            a
4      hash      Wedding venue        BCD            a   
5      Rash      Wedding card         BCD            a

我想获取所有具有 cat 值 Wedding venue 的结果并计算重复的 fname。我正在使用的查询是这样的，它工作正常。

SELECT *, count(*) as counts
from table
where cat='Wedding venue' AND status='a'
Group by fname;

输出:

id     fname   count(*)     cat                address      status
1      Bash    2            Wedding Venue      ABC          a
3      jash    1            Wedding Venue      ABC          a
4      hash    1            Wedding Venue      BCD          a

是否有任何可能的方式来显示这样的输出:

id     fname   count(*)     cat                address      status
1      Bash    2            Wedding Venue      ABC          a
2      Bash    2            Wedding Venue      BCD          a
3      jash    1            Wedding Venue      ABC          a
4      hash    1            Wedding Venue      BCD          a

Answer 1

我们对“工作正常”有不同的定义，所以我可能有点难以提供帮助。

但是这样的查询可能会得到您想要的结果。

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,fname VARCHAR(12) NOT NULL
,cat VARCHAR(20) NOT NULL
,address VARCHAR(12) NOT NULL
,status CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1,'Bash','Wedding venue','ABC','a'),
(2,'Bash','Wedding venue','BCD','a'),
(3,'jash','Wedding venue','ABC','a'),
(4,'hash','Wedding venue','BCD','a'),
(5,'Rash','Wedding card','BCD','a');

SELECT x.*
     , y.count 
  FROM my_table x 
  JOIN 
     ( SELECT fname
            , cat
            , status
            , COUNT(*) count 
         FROM my_table 
        GROUP 
           BY fname
            , cat
            , status
     ) y 
    ON y.fname = x.fname 
   AND y.cat = x.cat 
   AND y.status = x.status 
 WHERE x.cat = 'Wedding venue' 
   AND x.status = 'a';
+----+-------+---------------+---------+--------+-------+
| id | fname | cat           | address | status | count |
+----+-------+---------------+---------+--------+-------+
|  1 | Bash  | Wedding venue | ABC     | a      |     2 |
|  2 | Bash  | Wedding venue | BCD     | a      |     2 |
|  3 | jash  | Wedding venue | ABC     | a      |     1 |
|  4 | hash  | Wedding venue | BCD     | a      |     1 |
+----+-------+---------------+---------+--------+-------+