我一直在试图让这个查询工作，但我现在被困住了。
问题出在与组CONCAT的联接上。我就是不知道怎么才能让它工作。
我想要的是从询价单和加入客户等获取大量数据，在我不得不更改供应商字段之前，一切都很好，这样每个询价行可以有多个供应商。我创建了新的表RFQsSuppliers，其中结合了Supplier.ID和RFQs.ID，并且有包含名称和其他内容的表Suppliers。
我想让供应商把所有的供应商都用'，'隔开。
我的问题：
$result = mysql_query("SELECT Pullero.DateAdded as DateAdded,
Customers.Name as customer,
Pullero.ID as RFQID,
Ships.Name as ship,
Pullero.CustomerRef as CustomerRef,
Contacts.FirstName as contactF,
Contacts.LastName as contactL,
Contacts.Email as contactE,
Users.tunnus as handler,
RFQStatus.Name as status,
Pullero.Description as RFQDescription,
Pullero.LastEdited as LastEdit
FROM RFQs Pullero
JOIN (SELECT RFQs.ID,
GROUP_CONCAT(Supplier.Name) AS Suppliers
FROM RFQs
LEFT JOIN RFQsSuppliers ON RFQs.ID = RFQsSuppliers.RFQID
JOIN Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
GROUP BY RFQs.ID)
RFQsSuppliers ON Pullero.ID = RFQsSuppliers.RFQID
LEFT JOIN Ships ON RFQ.ShipID=Ships.ID
LEFT JOIN Contacts ON RFQ.ContactID=Contacts.ID
LEFT JOIN Customers ON RFQ.CustomerID=Customers.idCustomers
LEFT JOIN Users ON RFQ.PriJobHandler=Users.id
LEFT JOIN RFQStatus ON RFQ.StatusID=RFQStatus.ID
WHERE RFQs.LastEdited > '$lastedited'
ORDER BY RFQs.LastEdited ASC
") or die(mysql_error());
目前，错误是：“字段列表”中的“Supplier.Name”列未知
编辑
下面是我桌子设计的一些例子：
RFQs
ID | DateAdded | CustomerID | ShipID | LastEdited | StatusID ...
/* -------------------------------------- */
Suppliers
ID | Name | CountryID
1 Sup1 2
2 Sup2 5
3 Sup3 3
4 Sup4 3
/* -------------------------------------- */
RFQsSuppliers
ID | RFQID | SupplierID
1 1 4
2 2 3
3 56 3
4 4 3
5 39 1
6 56 1
7 4 4
我试着只得到供应商以下的问题：
$result = mysql_query("SELECT Suppliers.Name as Suppliers
FROM RFQs
LEFT JOIN RFQsSuppliers ON RFQs.ID=RFQsSuppliers.SupplierID
LEFT JOIN Suppliers ON RFQsSuppliers.SupplierID=Suppliers.ID
GROUP BY RFQs.ID
") or die(mysql_error());
但每行的print_r只返回以下内容：
数组（[供应商]=>Sup1，Sup1）数组（[供应商]=>）数组（[供应商]=>Sup4，Sup4）数组（[供应商]=>）数组（[供应商]=>）数组（[供应商]=>）
有什么想法吗？
最佳答案
JOIN (
SELECT RFQs.ID,
GROUP_CONCAT(Supplier**s**.Name) AS Suppliers
FROM RFQs
LEFT JOIN RFQsSuppliers ON RFQs.ID = RFQsSuppliers.RFQID
JOIN Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
GROUP BY RFQs.ID
) RFQsSuppliers
试试看
我相信您在组CONCAT表名中漏掉了一个's'，因为您是作为Supplier.name而不是Suppliers.name
编辑
另外，您在WHERE和ORDER BY子句中引用的是RFQs.lastededited，但是您将表RFQs别名为Pullero，因此这些将需要更改为Pullero.lastededited
编辑编辑重新格式化为使用联接而不是子查询
SELECT
Pullero.DateAdded as DateAdded,
Customers.Name as customer,
Pullero.ID as RFQID,
GROUP_CONCAT(Suppliers.Name) AS Suppliers,
Ships.Name as ship,
Pullero.CustomerRef as CustomerRef,
Contacts.FirstName as contactF,
Contacts.LastName as contactL,
Contacts.Email as contactE,
Users.tunnus as handler,
RFQStatus.Name as status,
Pullero.Description as RFQDescription,
Pullero.LastEdited as LastEdit
FROM RFQs AS Pullero
LEFT JOIN RFQsSuppliers ON RFQsSuppliers.RFQID = Pullero.ID
LEFT JOIN Suppliers ON RFQSuppliers.SupplierID = Suppliers.ID
LEFT JOIN Ships ON RFQ.ShipID=Ships.ID
LEFT JOIN Contacts ON RFQ.ContactID=Contacts.ID
LEFT JOIN Customers ON RFQ.CustomerID=Customers.idCustomers
LEFT JOIN Users ON RFQ.PriJobHandler=Users.id
LEFT JOIN RFQStatus ON RFQ.StatusID=RFQStatus.ID
WHERE Pullero.LastEdited > '$lastedited'
ORDER BY Pullero.LastEdited ASC
或者举一个使用原始子查询的例子，RFQs链接看起来是超级棒的，所以我调整了它+将供应商列表添加到输出中
$result = mysql_query("SELECT Pullero.DateAdded as DateAdded,
RFQsSuppliers.Suppliers,
Customers.Name as customer,
Pullero.ID as RFQID,
Ships.Name as ship,
Pullero.CustomerRef as CustomerRef,
Contacts.FirstName as contactF,
Contacts.LastName as contactL,
Contacts.Email as contactE,
Users.tunnus as handler,
RFQStatus.Name as status,
Pullero.Description as RFQDescription,
Pullero.LastEdited as LastEdit
FROM RFQs Pullero
JOIN (
SELECT RFQsSuppliers.RFQID,
GROUP_CONCAT(Suppliers.Name) AS Suppliers
FROM RFQsSuppliers.RFQID
JOIN Suppliers ON RFQsSuppliers.SupplierID = Suppliers.ID
GROUP BY RFQsSuppliers.RFQID
) AS RFQsSuppliers ON Pullero.ID = RFQsSuppliers.RFQID
LEFT JOIN Ships ON RFQ.ShipID=Ships.ID
LEFT JOIN Contacts ON RFQ.ContactID=Contacts.ID
LEFT JOIN Customers ON RFQ.CustomerID=Customers.idCustomers
LEFT JOIN Users ON RFQ.PriJobHandler=Users.id
LEFT JOIN RFQStatus ON RFQ.StatusID=RFQStatus.ID
WHERE Pullero.LastEdited > '$lastedited'
ORDER BY Pullero.LastEdited ASC
") or die(mysql_error());
编辑问题第二部分的更新查询
$result = mysql_query("SELECT RFQs.ID, GROUP_CONCAT(Suppliers.Name) as Suppliers
FROM RFQs
LEFT JOIN RFQsSuppliers ON RFQs.ID=RFQsSuppliers.RFQID
LEFT JOIN Suppliers ON RFQsSuppliers.SupplierID=Suppliers.ID
GROUP BY RFQs.ID
") or die(mysql_error());
关于mysql - MYSQL GROUP_CONCAT和半剂量联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9329275/