如何添加一个字段来计算我的查询检索到的记录的数量？为什么我要获得这条错误消息？

Question

我对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

这意味着返回两个这样的记录，这些记录代表了某个特定商品的销售市场，如下所示：

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

为什么？怎么啦？我遗漏了什么？如何解决这个问题并取得预期的结果？

Answer 1

当您想要创建一个包含诸如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

Answer 2

当使用聚合函数时，组by是强制性的。尝尝这个

SELECT  count(market_id) AS market_total_number,
    market_id, 
    market_name,
    market_total_number,
    commodity_details_id,
    commodity_name_en,
    commodity_name, 
    image_link,    
    description,
    price_serie_id,  
    today_avg_price, 
    yesterday_avg_price,
    currency ,
    measure_units
FROM (  
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
) base
group by    market_id, 
        market_name,
        market_total_number,
        commodity_details_id,
        commodity_name_en,
        commodity_name, 
        image_link,    
        description,
        price_serie_id,  
        today_avg_price, 
        yesterday_avg_price,
        currency ,
        measure_units