MySQL的GROUP_CONCAT在加入

Question

我有一个查询，看起来像这样：

SELECT 
    *, 
    GROUP_CONCAT(DISTINCT web_order_item.od_pcode) AS items, 
    GROUP_CONCAT(DISTINCT woi_id) AS woi_ids 
    FROM web_order 
JOIN web_order_item USING(od_id) 
JOIN customer_master ON customer_master.VC_CUSTOMER_CODE = web_order.p_vendor 
LEFT JOIN production_item ON production_item.ItemID IN (GROUP_CONCAT(DISTINCT woi_id)) AND QCStatus <> 'received' 
WHERE 
    web_order.od_date >= FROM_UNIXTIME(1357257600) 
    AND web_order.od_date <= FROM_UNIXTIME(1359936000) 
    AND web_order.od_id > 0 
    AND 1 
GROUP BY web_order.od_id 
ORDER BY web_order.od_id DESC 

此查询引发错误

General error: 1111 Invalid use of group function

缺少什么我在这里？谢谢

Answer 1

您正在选择原始记录的全部内容，然后在web_order_item上执行组concat，由于DISTINCT属性，这将生成单行，如果有多条记录，那么您在那里分组，然后尝试再次分组... ...无论如何，你最后还是在分组？没有聚合，订单就足够了。取出组由...或更改组通过对包括组CONCAT之前的所有相关项目...

Answer 2

SELECT 
*, 
GROUP_CONCAT(DISTINCT web_order_item.od_pcode) AS items, 
GROUP_CONCAT(DISTINCT woi_id) AS woi_ids 
FROM web_order 
JOIN web_order_item USING(od_id) 
JOIN customer_master ON customer_master.VC_CUSTOMER_CODE = web_order.p_vendor 
LEFT JOIN production_item ON production_item.ItemID IN (GROUP_CONCAT(DISTINCT woi_id))  AND QCStatus <> 'received' 
WHERE 
web_order.od_date >= FROM_UNIXTIME(1357257600) 
AND web_order.od_date <= FROM_UNIXTIME(1359936000) 
AND web_order.od_id > 0 
AND 1 
GROUP BY items, woi_ids 
ORDER BY web_order.od_id DESC 

跳这有助于

参考http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html