不幸的是，我必须在MySQL/php中执行此操作。我找了三天，大约有 10.000 次这种植物，但没有（我重复一遍）对我有用。我都试过了。我不得不问，对不起。
我有两个表 - 文章和控件。
table "articles"
------------------
art_id | name |
------------------
1 | aaa |
2 | bbb |
3 | ccc |
4 | ddd |
table "control"
--------------------------------------------
con_id | art_id | data |
--------------------------------------------
1 | 1 | something-a |
2 | 2 | something-b |
3 | 1 | something-a |
4 | 2 | something-c |
5 | 3 | something-f |
两个表中都存在art_id。现在我想要什么 - 用于查询： "select * from articles order by art_id ASC"显示在表格中还有一个单元格显示表 CONTROL 中每个art_id的计数...所以我尝试加入、左连接、内部连接 - 我收到错误......我也尝试了每个只得到一个结果（例如 2 个所有结果）......这是半正确的，但它显示正确结果的数组，甚至没有加入!!:
$query = "SELECT art_id, count(*) as counting
FROM control GROUP BY art_id ORDER BY con_id ASC";
$result = mysql_query($query);
while($row=mysql_fetch_array($result)) {
echo $row['counting'];
}
这将显示 221 -
-------------------------------------------------
art_id | name | count (this one from control) |
-------------------------------------------------
1 | aaa | 221 |
2 | bbb | 221 |
3 | ccc | 221 |
它应该是：
for art_id(value1)=2,
for art_id(2)=2,
for art_id(3)=1
它应该很简单 - 就像查询中显示的有关"文章"表的 CONTROL 表中的值计数......
表项目页面上的结果查询应为： "select * from articles order by art_id ASC"
-------------------------------------------------
art_id | name | count (this one from control) |
-------------------------------------------------
1 | aaa | 2 |
2 | bbb | 2 |
3 | ccc | 1 |
所以也许我应该为每个加入或加入加号......也试过了，但后来我不确定回声的正确方法是什么......总而言之，我完全迷失在这里。请帮忙。谢谢。
因此，
请分两步想象：
- 从
control表中获取每art_id计数 - 使用
articles表，选取步骤 1 中的计数
这将为您提供如下所示的查询：
SELECT a.art_id, a.name, b.control_count
FROM articles a
INNER JOIN
(
SELECT art_id, COUNT(*) AS control_count
FROM control
GROUP BY art_id
) b
ON a.art_id = b.art_id;
这将为您提供所需的结果。
但是，您可以一次性完成所有操作，而不是使用子查询：
SELECT a.art_id, a.name, COUNT(b.art_id) AS control_count
FROM articles a
INNER JOIN control b
ON a.art_id = b.art_id
GROUP BY a.art_id, a.name;
SQL 小提琴演示
SELECT *, (SELECT COUNT(control.con_id) FROM control WHERE control.art_id = articles.art_id) AS count_from_con FROM articles ORDER BY art_id DESC;
如果我理解正确您的问题，则此查询应该可以解决问题。
编辑：创建了您描述的表，并且可以工作。
SELECT * FROM articles;
+--------+------+
| art_id | name |
+--------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+--------+------+
4 rows in set (0.00 sec)
SELECT * FROM control;
+--------+--------+------+
| con_id | art_id | data |
+--------+--------+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 1 | NULL |
| 4 | 2 | NULL |
| 5 | 3 | NULL |
+--------+--------+------+
5 rows in set (0.00 sec)
SELECT *, (SELECT COUNT(control.con_id) FROM control WHERE control.art_id = articles.art_id) AS count_from_con FROM articles ORDER BY art_id ASC;
+--------+------+----------------+
| art_id | name | count_from_con |
+--------+------+----------------+
| 1 | aaa | 2 |
| 2 | bbb | 2 |
| 3 | ccc | 1 |
| 4 | ddd | 0 |
+--------+------+----------------+
你还没有完全解释你想用打印输出完成什么，但这里有一个PHP的例子：（使用PDO而不是mysql_）
$pdo = new PDO(); // Make your connection here
$stm = $pdo->query('SELECT *, (SELECT COUNT(control.con_id) FROM control WHERE control.art_id = articles.art_id) AS count_from_con FROM articles ORDER BY art_id ASC');
while( $row = $stm->fetch(PDO::FETCH_ASSOC) )
{
echo "Article with id: ".$row['art_id']. " has " .$row['count_from_con'].' connected rows in control.';
}
或者使用mysql_扩展名：
$result = mysql_query('SELECT *, (SELECT COUNT(control.con_id) FROM control WHERE control.art_id = articles.art_id) AS count_from_con FROM articles ORDER BY art_id ASC');
while( $row = mysql_fetch_assoc($result) )
{
echo "Article with id: ".$row['art_id']. " has " .$row['count_from_con'].' connected rows in control.';
}
这应该是足够的示例来帮助您完成所需的工作。