表达式＃6选择列表不在group by子句中，并且包含非聚合列 [英] Expression #6 select list is not in group by clause and contains nonaggregated column

问题描述

我创建了这个视图来计算每个商店中每件商品的总库存数：

i created this view to calculate the total stocks of every item in every store as :

CREATE OR REPLACE VIEW `view_items_stocks_smallest_unit_prices_noserials` as
							  SELECT items.stitems_ID,
							  items.stitems_Status,
							  items.stitems_Name,
							  items.stitems_Type,
							  items.stitems_Code,
                              unitsdet.DetunitID,
							  unitsnamesdet.UnitName,
                              ca.NOSStoreID,
							  COALESCE(ca.Amount, 0) AS Stock,
                              ca.MaxPurchasesPrice,
                              ca.purchases_price_withoutDiscOrXtra
					   FROM st_items items
					   LEFT JOIN (SELECT sns.stitems_ID,
                                         sns.StoreID AS NOSStoreID,
										 SUM(sns.StockQnty) AS Amount,
                                         MIN(sns.purchases_price) AS MaxPurchasesPrice,
                                         MAX(sns.purchases_price_withoutDiscOrXtra) AS purchases_price_withoutDiscOrXtra
								  FROM stock_noserials sns
								  GROUP BY sns.stitems_ID , NOSStoreID) ca
					   ON ca.stitems_ID = items.stitems_ID
                       LEFT JOIN (SELECT units.stitemsu_ID as DetunitID,
										 units.stitemsu_UnitName as unitID,
                                         units.stitems_ID as ItemIDa
								  FROM st_items_units units
								  WHERE units.stitemsu_UnitNum = 1
								  GROUP BY units.stitemsu_ID) unitsdet
					   ON items.stitems_ID = ItemIDa
                       LEFT JOIN (SELECT unitsnames.ID as UnameID,
										 unitsnames.Unit as UnitName
								  FROM st_units_name unitsnames
								  GROUP BY unitsnames.ID) unitsnamesdet
					   ON unitsdet.unitID = UnameID
					GROUP BY items.stitems_ID, ca.NOSStoreID;

i现在试试o使用EF6从中选择：

i try now to select from it using EF6 as :

var StocksData = DB1.view_items_stocks_smallest_unit_prices_noserials.Where(u => u.stitems_ID == itemID && u.NOSStoreID == storeID);
                            int sstock = Convert.ToInt32( StocksData.FirstOrDefault().Stock);

但出现此错误：

表达式＃6选择列表不在group by子句中，并且包含非聚合列'unitsdet.DetunitID'，它在colmuns group by子句中没有功能上的depond，这与Sql_mode = only_full_group by



我的尝试：



- 我删除了这一行

but this error appear :
expression #6 select list is not in group by clause and contains nonaggregated column 'unitsdet.DetunitID' which is not functionally depond on colmuns group by clause this is comptible with Sql_mode = only_full_group by

What I have tried:

- i deleted this line

GROUP BY units.stitemsu_ID

但出现同样的错误。

- 我更换了这行

but same error appear.
- i replaced this line

GROUP BY sns.stitems_ID , NOSStoreID

此行

GROUP BY sns.stitems_ID

。并且错误消失但我需要将结果与商店ID分组。

. and the error disappeared but i need to group the results with store id also.

推荐答案

请参阅此处： [ ^ ]

这篇关于表达式＃6选择列表不在group by子句中，并且包含非聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！