我不太喜欢SQL,并且在尝试更改查询时遇到以下疑问(我将MySql作为DB使用)。
所以我有这个查询:
SELECT
MD.id AS market_id,
MD.market_name AS market_name,
CN.id AS commodity_details_id,
CD.commodity_name_en AS commodity_name_en,
CN.commodity_name AS commodity_name,
CD.image_link AS image_link,
CN.description AS description,
MD_CD.id AS price_serie_id,
MCPS.avg_price AS today_avg_price,
YMCPS.avg_price AS yesterday_avg_price,
CASE WHEN MCPS.market_commodity_details_id IS NOT NULL
THEN
MCPS.currency
ELSE
YMCPS.currency
END AS currency,
CASE WHEN MCPS.market_commodity_details_id IS NOT NULL
THEN
MCPS.measure_unit
ELSE
YMCPS.measure_unit
END AS measure_unit
FROM MarketDetails_CommodityDetails AS MD_CD
INNER JOIN MarketDetails AS MD
ON MD_CD.market_details_id = MD.id
INNER JOIN CommodityDetails as CD
ON MD_CD.commodity_details_id = CD.id
INNER JOIN CommodityName AS CN
ON CD.id=CN.commodity_details_id
LEFT JOIN
(
SELECT
Market_Commodity_Price_Series.id AS price_series_id,
market_commodity_details_id
, avg_price
, CU.ISO_4217_cod AS currency
, MU.unit_name AS measure_unit
FROM Market_Commodity_Price_Series
INNER JOIN MeasureUnit AS MU
ON Market_Commodity_Price_Series.measure_unit_id = MU.id
INNER JOIN Currency AS CU
ON Market_Commodity_Price_Series.currency_id = CU.id
WHERE price_date = CURDATE()
) AS MCPS
ON MD_CD.id = MCPS.market_commodity_details_id
LEFT JOIN
(
SELECT
Market_Commodity_Price_Series.id AS price_series_id,
market_commodity_details_id
, avg_price
, CU.ISO_4217_cod AS currency
, MU.unit_name AS measure_unit
FROM Market_Commodity_Price_Series
INNER JOIN MeasureUnit AS MU
ON Market_Commodity_Price_Series.measure_unit_id = MU.id
INNER JOIN Currency AS CU
ON Market_Commodity_Price_Series.currency_id = CU.id
WHERE price_date = ADDDATE(CURDATE(), -1)
) AS YMCPS
ON MD_CD.id = YMCPS.market_commodity_details_id
WHERE
MD_CD.commodity_details_id = 4
AND
MD.localization_id = 2
这将返回2条类似的记录,这些记录代表销售特定商品的市场,如下所示:
market_id market_name commodity_details_id commodity_name_en commodity_name image_link description price_serie_id today_avg_price yesterday_avg_price currency measure_unit
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 Tambacounda Market N1 4 Red onion Red onion https://firebasestorage.googleapis.com/v0/b/blablabla.appspot.com/o/img%2Ficons%2Fagrimarket%2Fcommodity%2Fonion-red.png?alt=media&token=4ec9de4e-6e5a-4198-9bcd-21b692760618 Red onion 24 20,15 18,94 XOF kilogram
5 Tambacounda Market N2 4 Red onion Red onion https://firebasestorage.googleapis.com/v0/b/blablabla.appspot.com/o/img%2Ficons%2Fagrimarket%2Fcommodity%2Fonion-red.png?alt=media&token=4ec9de4e-6e5a-4198-9bcd-21b692760618 Red onion 28 13,7 16,2 XOF kilogram
好的,我想添加一个新字段,该字段代表可使用该商品的市场数量(基本上是检索到的记录的数量),因此我尝试执行以下操作:
SELECT
MD.id AS market_id,
MD.market_name AS market_name,
count(MD.id) AS market_total_number,
CN.id AS commodity_details_id,
CD.commodity_name_en AS commodity_name_en,
CN.commodity_name AS commodity_name,
CD.image_link AS image_link,
CN.description AS description,
MD_CD.id AS price_serie_id,
MCPS.avg_price AS today_avg_price,
YMCPS.avg_price AS yesterday_avg_price,
CASE WHEN MCPS.market_commodity_details_id IS NOT NULL
THEN
MCPS.currency
ELSE
YMCPS.currency
END AS currency,
CASE WHEN MCPS.market_commodity_details_id IS NOT NULL
THEN
MCPS.measure_unit
ELSE
YMCPS.measure_unit
END AS measure_unit
FROM MarketDetails_CommodityDetails AS MD_CD
INNER JOIN MarketDetails AS MD
ON MD_CD.market_details_id = MD.id
INNER JOIN CommodityDetails as CD
ON MD_CD.commodity_details_id = CD.id
INNER JOIN CommodityName AS CN
ON CD.id=CN.commodity_details_id
LEFT JOIN
(
SELECT
Market_Commodity_Price_Series.id AS price_series_id,
market_commodity_details_id
, avg_price
, CU.ISO_4217_cod AS currency
, MU.unit_name AS measure_unit
FROM Market_Commodity_Price_Series
INNER JOIN MeasureUnit AS MU
ON Market_Commodity_Price_Series.measure_unit_id = MU.id
INNER JOIN Currency AS CU
ON Market_Commodity_Price_Series.currency_id = CU.id
WHERE price_date = CURDATE()
) AS MCPS
ON MD_CD.id = MCPS.market_commodity_details_id
LEFT JOIN
(
SELECT
Market_Commodity_Price_Series.id AS price_series_id,
market_commodity_details_id
, avg_price
, CU.ISO_4217_cod AS currency
, MU.unit_name AS measure_unit
FROM Market_Commodity_Price_Series
INNER JOIN MeasureUnit AS MU
ON Market_Commodity_Price_Series.measure_unit_id = MU.id
INNER JOIN Currency AS CU
ON Market_Commodity_Price_Series.currency_id = CU.id
WHERE price_date = ADDDATE(CURDATE(), -1)
) AS YMCPS
ON MD_CD.id = YMCPS.market_commodity_details_id
WHERE
MD_CD.commodity_details_id = 4
AND
MD.localization_id = 2
我只是将此SELECT添加到检索字段列表中:
count(MD.id) AS market_total_number,
因为我的想法是对检索到的记录进行计数,但是现在执行我的查询却收到以下错误消息:
#42000In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'digital_services_DB.MD.id'; this is incompatible with sql_mode=only_full_group_by
为什么?怎么了?我想念什么?如何解决此问题并获得所需的结果?
最佳答案
当您要创建具有诸如count之类的聚合函数的select并检索任何字段时,必须使用group by。
您可以在以下网站中了解更多信息:
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
因此,如果您在查询末尾添加它,它将起作用。
GROUP BY
MD.id
,MD.market_name
,CN.id
,CD.commodity_name_en
,CN.commodity_name
,CD.image_link
,CN.description
,MD_CD.id
,MCPS.avg_price
,YMCPS.avg_price