SELECT
a.business_id,
a.depot_pos,
GROUP_CONCAT(stock_code) AS concat_stock_code
FROM
dpb_wms_depot_pos AS a
LEFT JOIN dpb_wms_goods AS c ON a.depot_pos = c.depot_pos
WHERE
a.business_id = 8
GROUP BY
a.depot_pos
现在这样时只查询三个字段的值是能够查询到数据的,如果我还想再查a表中的dppos_id,添加到select后边就会报错,报错信息:SELECT list is not in GROUP BY clause and contains nonaggregated column 'dpb.a.dppos_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by;他说选择列表不在GROUPBY子句中;
哪位大佬知道这个问题怎么解决,我还想获取其他字段的值的问题?
表结构和报错的sql
你好,表结构
根据这两张表查询同一仓位上的多个商品编码,基本格式如下
如果我在想获取表中的其他值就会报错,报错信息:
就这样完美的报错了。麻烦帮看下,谢谢!
@红玫瑰1688: 还真没看出什么错误来 不好意思
@思念断了线: 没事,不存在的。
这是因为你的sql_mode包含了'only_full_group_by' 这种模式,大概就是出现在select中的列,也要出现在group by中。所以你需要:
1.改一下sql:
SELECT
a.business_id,
a.depot_pos,
GROUP_CONCAT(stock_code) AS concat_stock_code
FROM
dpb_wms_depot_pos AS a
LEFT JOIN dpb_wms_goods AS c ON a.depot_pos = c.depot_pos
WHERE
a.business_id = 8
GROUP BY
a.business_id,a.depot_pos;
或者
2.改sql_mode
额,,,我也不太会。请参考:
https://blog.csdn.net/wangyunfeis/article/details/77911704
自己已经解决,由于时间关系,解决方案稍后补充。