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屋!