php返回多行查询结果,PHP-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子查询返回多个行”错误.

我知道此查询可以使用以下查询的解决方法：

SELECT laterecords.studentid,

laterecords.latetime,

laterecords.reason

FROM laterecords

然后使用php循环遍历结果,并在以下查询中进行操作以获取late_count并将其回显：

SELECT Count(laterecords.studentid) AS late_count FROM laterecords

但是我认为可能会有更好的解决方案？

解决方法:

简单的解决方法是在子查询中添加WHERE子句：

SELECT

studentid,

latetime,

reason,

(SELECT COUNT(*)

FROM laterecords AS B

WHERE A.studentid = B.student.id) AS late_count

FROM laterecords AS A

一个更好的选择(就性能而言)是使用联接：

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,mysql,php

来源： https://codeday.me/bug/20191201/2080337.html