我不太喜欢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

10-07 15:54
查看更多