SQL'COUNT'函数不工作 [英] SQL 'COUNT' function not working

问题描述

我想要一个SQL查询返回我的总行数。以下是我的查询。

  SELECT COUNT（*）AS tot_std 
 FROM`student_main` AS sm，
`student_subjects` AS stds，
`sub_subjects` AS ss 
 WHERE stds.`student_id`= sm.`studen。 t_id` 
 AND stds.`subs_id` = ss.`subs_id` 
 AND stds.`subs_id` = 1 
 AND sm.`std_postcode` LIKE'％HA02AN％'
 GROUP BY stds.`student_id` 
  

查询连接三个表：
1） student_main 这是主表并存储学生信息。
2） sub_subjects 这是存储主题/课程信息的主表
3） student_subjects 此表由student_id和subs_id作为外键组成，并显示学生正在学习的主题。

以下是 student_subjects 表。

现在当我运行查询时应该在输出中显示3，因为总共3个学生正在学习id为1的主题，而是查询给我这样的输出：

<

删除 GROUP BY 。并学习正确的显式 JOIN 语法：

  SELECT COUNT tot_std 
 FROM`student_main` sm JOIN 
`student_subjects` stds 
 ON stds.`student_id`= sm.`studen。 t_id` JOIN 
`sub_subjects` ss 
 ON stds.`subs_id` = ss.`subs_id` 
 WHERE stds.`subs_id` = 1 AND sm.`std_postcode` LIKE'％HA02AN％ '; 
 

简单的规则：从不在 FROM 子句。 *总是使用 ON 子句明确 JOIN 语法。

I want an SQL Query to return me the total number of rows. Following is my query.

SELECT COUNT(*) AS tot_std  
FROM `student_main` AS sm,
     `student_subjects` AS stds,
     `sub_subjects` AS ss
WHERE stds.`student_id` = sm.`studen. t_id`  
  AND stds.`subs_id` = ss.`subs_id`  
  AND stds.`subs_id` = 1  
  AND sm.`std_postcode` LIKE '%HA02AN%'
GROUP BY stds.`student_id`

The query joins three tables: 1) student_main which is the main table and stores student info. 2) sub_subjects which is the main table to store info about subjects/courses 3) student_subjects this table consists of both student_id and subs_id as foreign keys and shows the subjects which the student is taking.

Following is the screen shot of the student_subjects table.

Now when I run the Query, it is supposed to display 3 in the output because a total of 3 students are studying the subject with id 1. but instead the query gives me an output like this:

Please Help.

解决方案

Remove the GROUP BY. And learn proper explicit JOIN syntax:

SELECT COUNT(*) AS tot_std  
FROM `student_main` sm JOIN
     `student_subjects` stds
     ON stds.`student_id` = sm.`studen. t_id` JOIN
     `sub_subjects` ss
     ON stds.`subs_id` = ss.`subs_id`  
WHERE stds.`subs_id` = 1  AND sm.`std_postcode` LIKE '%HA02AN%';

Simple rule: Never use commas in the FROM clause. *Always use explicit JOIN syntax with an ON clause.

这篇关于SQL'COUNT'函数不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！