我试图从我的数据库中获取一些JSON数据,但我无法编写正确的SQL查询。
我试过了:
$sql = "SELECT arrondissement AS tName, ("SELECT count(*) FROM fcr_table WHERE arrondissement = 'tName';) as tLength FROM fcr_table GROUP BY name";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result['tName']->fetch_assoc()) {
echo "name: " .$row['tName']. " " .$row['tLength']. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
为了解释更多,这里是我想要实现的一个示例:
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 0 | test1 | 1000 |
| 1 | test2 | 2000 |
| 2 | test1 | 3000 |
| 3 | test1 | 5000 |
| 4 | test1 | 1000 |
| 5 | test2 | 3000 |
| 6 | test1 | 7000 |
+----+-------+-------+
我想像这样获取JSON数据:
test1 : count of test1
test2 : count of test2
发布于 2018-07-19 04:47:59
你可以使用类似这样的东西:
SELECT name AS tName, (SELECT count(*) FROM tests WHERE name=tName) as tLength FROM tests GROUP BY name
如果你不介意化名..。它将允许您在子查询中使用当前名称,从而获得计数。最终,Group by
将只留下一个结果,而不是许多重写。如果你进行PHP查询,你将能够访问像$result['tName']
和$result['tLength']
这样的数据
https://stackoverflow.com/questions/51410525
复制相似问题