我正在尝试参考另一个表中的ID来找出一个表中某些条目的计数。不幸的是，由于我对PHP还是很陌生，所以我没有专业知识来实际找到解决方案。请参考以下内容，因为我认为这将比我对其解释更为清楚：
表1 ：（重复表）（这是它在网页上的外观）
Unique ID | product name | Bid Count | Min Bid
-------------------------------------------------
01 | Product A | (4) | $1000
02 | Product B | (6) | $500
表2 ：（此第二表收集了买家的出价，但唯一ID与表1相同）
-------------------------------------------------
Unique ID | product name | Bid ($)
-------------------------------------------------
01 | Product A | $2000
01 | Product A | $1500
01 | Product A | $1200
01 | Product A | $1000 <<Lowest Bid | 4 bids count >>
--------------------------------------------------
02 | Product B | $1500
02 | Product B | $1000
02 | Product B | $700
02 | Product B | $800
02 | Product B | $600
02 | Product B | $500 <<Lowest Bid | 6 bids count >>
请帮助解决这个挑战
最佳答案
考虑以下 -
mysql> create table table1 (id int, product_name varchar(100));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into table1 values (1,'Prod A'),(2,'Prod B');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table table2 (id int, bid int);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into table2 values (1,2000),(1,1500),(1,1200),(1,1000),(2,1500),(2,1000),(2,700),(2,800),(2,600),(2,500);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from table1;
+------+--------------+
| id | product_name |
+------+--------------+
| 1 | Prod A |
| 2 | Prod B |
+------+--------------+
2 rows in set (0.00 sec)
mysql> select * from table2;
+------+------+
| id | bid |
+------+------+
| 1 | 2000 |
| 1 | 1500 |
| 1 | 1200 |
| 1 | 1000 |
| 2 | 1500 |
| 2 | 1000 |
| 2 | 700 |
| 2 | 800 |
| 2 | 600 |
| 2 | 500 |
+------+------+
10 rows in set (0.00 sec)
现在，您可以使用
join和聚合函数count，min以及最后的group by获得所需的结果select t1.id ,
t1.product_name,
count(t2.id) as `Bid Count`,
min(t2.bid) as `Min Bid`
from table1 t1 join table2 t2 on t1.id = t2.id
group by t1.id ;