子查询返回超过1行 [英] Subquery returning more than 1 row

问题描述

您好，我想做的是通过一个查询从一个表中获取一些客户详细信息，并在另一个表中获取其所有项目.我的子查询的当前问题是它将返回多于1行并导致错误.有没有解决的办法，还是应该使用2个单独的查询?

Hi what I'm trying to do is obtain some customer details from one table and also all of their items in another table through one query. The current issue with my subquery is that it will return more than 1 row and cause an error. Is there a way around this or should I use 2 separate queries?

我想在查询中使用PDO fetchAll()返回其名字和姓氏以及包含所有项目的子数组.

I'd like to use PDO fetchAll() on the query to return their first and last name and a subarray containing all of the items.

因此，可以通过$ result ['First Name']，$ result ['First Name']，$ result ['product'] [0]，$ result ['product'] [1]访问结果. $ result ['product'] [3]等.

So the results would be accessible by $result['First Name'], $result['First Name'], $result['product'][0], $result['product'][1], $result['product'][3] etc.

当前声明:

SELECT `First Name`, `Last Name`, 

(SELECT `items`.`Product Name` from items 
inner join customers on `customers`.`Customer No` = `items`.`Customer No` 
WHERE `customers`.`Customer No` = '6') 

AS product from customers where `Customer No` = '6'

推荐答案

我认为不可能在一行中返回数组，但是您可以使用group_concat将值连接到字符串中然后您可以在以后将其爆炸:

I don't think it's possible to return an array within one row, but what you can do is use group_concat to join the values in to a string which you can then explode later on:

SELECT `customers`.`First Name`, `customers`.`Last Name`,
GROUP_CONCAT(`items`.`Product Name` SEPARATOR '$^$') AS `Products` 
FROM customers 
JOIN items 
ON `customers`.`Customer No` = `items`.`Customer No` 
WHERE `customers`.`Customer No` = 6 
GROUP BY `Customer No`

$products=explode("$^$",$result[0]['products']);

group_concat的默认分隔符是,，使用爆炸时可能会很危险，因此我们使用SEPARATOR '$^$'添加了一堆不太可能出现的随机字符.

The default separator for group_concat is , which can be quite dangerous when using explode so we use SEPARATOR '$^$' to add a bunch of random characters that are not likely to turn up instead.

这篇关于子查询返回超过1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！