MySQL 计数未针对特定 ID 返回 0

发布于 2024-12-21 00:43:40 字数 412 浏览 2 评论 0 原文

我有以下代码：

SELECT c.c_id, c_name, count(h.h_id) AS count 
FROM c
LEFT JOIN h ON h.h_id = c.c_id
WHERE h.info_id = 25
AND c.c_id = {$sqlrow['c_id']}
GROUP BY c.c_id

我使用 PHP 在 WHERE 语句中选择 c_id ，不幸的是，如果 c_id 的 h_id 计数为 0 则不会选择任何行。有没有办法让 SQL 将没有选择的行表示为 0 计数？

因为 PHP 中的 WHERE 循环没有为特定 c_id 选择任何行，所以似乎会跳过该行。

原文

I have the following code:

SELECT c.c_id, c_name, count(h.h_id) AS count 
FROM c
LEFT JOIN h ON h.h_id = c.c_id
WHERE h.info_id = 25
AND c.c_id = {$sqlrow['c_id']}
GROUP BY c.c_id

I am using PHP to select the c_id in the WHERE statement, unfortunately if there is a c_id with a count of 0 for the h_id then no rows are selected. Is there a way to get SQL to represent no rows selected as a count of 0?

Because no rows are selected for a specific c_id by WHERE loop in PHP appears to skip the row.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无法回应 2024-12-28 00:43:40

怎么样：

SELECT
  c.c_id,
  c_name,
  (
    SELECT COUNT(h.h_id)
    FROM h
    WHERE h.h_id = c.c_id
    AND h.info_id = 25
  ) AS count 
FROM c
WHERE c.c_id = {$sqlrow['c_id']}

What about:

SELECT
  c.c_id,
  c_name,
  (
    SELECT COUNT(h.h_id)
    FROM h
    WHERE h.h_id = c.c_id
    AND h.info_id = 25
  ) AS count 
FROM c
WHERE c.c_id = {$sqlrow['c_id']}

回复收藏 0 原文

冷月断魂刀 2024-12-28 00:43:40

使用 PHP 和 mySQL，当我可能遇到这种情况时，我会直接在查询下使用一个简单的 IF 语句，就像

$sql = "SELECT your, stuff, here";
$qry = mysql_query($sql) or die("ERROR: ".mysql_error());
if($mysql_num_rows($qry) == 0) {
    # DO YOUR STUFF HERE FOR NO RESULTS
} else {
    # DO YOUR STUFF HERE FOR RESULTS
}

我希望这能让您走上正确的道路一样，是的，我知道 SQL 是无效的，它与您已经拥有的无关您的 SQL - 它仅用于演示目的。

With PHP and mySQL when I may run into this type of situation I use a simple IF statement directly under the query like

$sql = "SELECT your, stuff, here";
$qry = mysql_query($sql) or die("ERROR: ".mysql_error());
if($mysql_num_rows($qry) == 0) {
    # DO YOUR STUFF HERE FOR NO RESULTS
} else {
    # DO YOUR STUFF HERE FOR RESULTS
}

I hope this gets you on the right path, and yeah, I know the SQL is invalid, it's irrelevant as you already have your SQL - it is just for demonstration purposes.

回复收藏 0 原文

贪恋 2024-12-28 00:43:40

SELECT c.c_id, c_name, count(h.h_id) AS count 
FROM c
LEFT JOIN h ON h.h_id = c.c_id
and h.info_id = 25
AND c.c_id = {$sqlrow['c_id']}
GROUP BY c.c_id

通过将 h 表上的条件放入 where 子句，它将左连接转换为内连接。因为如果左连接中 h.info 的列值为 null，则 where 子句条件永远不会为 true，因此该行将被过滤掉。

SELECT c.c_id, c_name, count(h.h_id) AS count 
FROM c
LEFT JOIN h ON h.h_id = c.c_id
and h.info_id = 25
AND c.c_id = {$sqlrow['c_id']}
GROUP BY c.c_id

by putting a condition on the h table into the where clause, it converts left join to inner join. Because the where clause condition will never be true if the column value for h.info is null from the left join, so the row is filtered out.

回复收藏 0 原文

~没有更多了~