SQL 查询 - 子查询返回多于一行 [英] SQL Query - Subquery returns more than one row
本文介绍了SQL 查询 - 子查询返回多于一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表格:
laterecords
-----------
studentid - varchar
latetime - datetime
reason - varchar
我的查询:
SELECT laterecords.studentid,
laterecords.latetime,
laterecords.reason,
( SELECT Count(laterecords.studentid) FROM laterecords
GROUP BY laterecords.studentid ) AS late_count
FROM laterecords
我收到MySQL 子查询返回多于一行"错误.
I'm getting " MySQL Subquery Returns more than one row" error.
我知道此查询使用以下查询的解决方法:
I know a workaround for this query to use the following query:
SELECT laterecords.studentid,
laterecords.latetime,
laterecords.reason
FROM laterecords
然后使用 php 循环来处理结果并执行以下查询以获取 late_count
并将其回显:
Then using php loop to though the results and do below query to get the late_count
and echo it out:
SELECT Count(laterecords.studentid) AS late_count FROM laterecords
但我认为可能有更好的解决方案?
But i think there might be a better solution ?
推荐答案
简单的解决方法是在您的子查询中添加一个 WHERE
子句:
The simple fix is to add a WHERE
clause in your subquery:
SELECT
studentid,
latetime,
reason,
(SELECT COUNT(*)
FROM laterecords AS B
WHERE A.studentid = B.student.id) AS late_count
FROM laterecords AS A
更好的选择(就性能而言)是使用连接:
A better option (in terms of performance) is to use a join:
SELECT
A.studentid,
A.latetime,
A.reason,
B.total
FROM laterecords AS A
JOIN
(
SELECT studentid, COUNT(*) AS total
FROM laterecords
GROUP BY studentid
) AS B
ON A.studentid = B.studentid
这篇关于SQL 查询 - 子查询返回多于一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文