参照表1中的UID对表2的实体进行计数 - Count Table 2 entities with reference to the UID in Table 1

Count Table 2 entities with reference to the UID in Table 1

本文关键字：实体对表中的 UID | 更新日期: 2023-09-27

我正试图通过引用另一个表中的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 ;