我在数据库中使用了这个sql语句,它使用count和union all
SELECT tem.book_id, COUNT( * )
FROM(SELECT book_id FROM borrowdetails
WHERE borrowdetails.borrow_status = 'returned'
UNION ALL
SELECT book_id from reserve) as tem
GROUP BY book_id
ORDER BY book_id DESC
然后我用这段代码命名php表中的id
while($row=mysql_fetch_array($user_query)){
$id=$row['book_id'];
$book_query = mysql_query("select * from book where book_id = '$id'")or die(mysql_error());
}
它工作得很好,但是它没有用相同的名称统一数据,因为它有不同的id,就像这样
+------+------+-------+
| id | name | count |
+------+------+-------+
|1 |A | 3 | the id here is not shown at the table in PHP just
+------+------+-------+ inside the database but the A is duplicate.
|2 |A | 1 |
+------+------+-------+
|3 |B | 2 |
+------+------+-------+
我想要的输出应该是这样的,而表中没有显示id。
+------+-------+
| name | count |
+------+-------+
|A | 4 |
+------+-------+ this is the table that should be shown in PHP
|B | 2 |
+------+-------+
正如您所看到的,名称a的count变为4,因为我还要加上两个a的count(*)。
为了达到预期的结果,我该怎么做?
顺便说一句,这是使用的数据库表。
+------+------+ +---------+---------+ +-------+-------+
| book table | |borrowdetails table| | reserve table |
+------+------+ +---------+---------+ +-------+-------+
| id | name | |brw_dt_id| book_id | |res_id |book_id|
+------+------+ +---------+---------+ +-------+-------+
|1 |A | | 1 | 2 | | 1 | 1 |
+------+------+ +---------+---------+ +-------+-------+
|2 |A | | 2 | 3 | | 2 | 1 |
+------+------+ +---------+---------+ +-------+-------+
|3 |B | | 3 | 3 | | 3 | 1 |
+------+------+ +---------+---------+ +-------+-------+
2条答案
按热度按时间l7mqbcuq1#
我们通常是这样写的
bxfogqkk2#
你可能想用书名而不是书号。我没试过,但应该可以