时间戳

首页 > 解决方案 > #1055 - SELECT 列表的表达式 #19 不在 GROUP BY 子句中，并且包含非聚合列

mysql - #1055 - SELECT 列表的表达式 #19 不在 GROUP BY 子句中，并且包含非聚合列

问题描述

我的 sql 查询中显示此错误：

“SELECT 列表的表达式 #19 不在 GROUP BY 子句中，并且包含非聚合列 'auctionsystems.auction_watches.watchId'，它在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_byDatabase 选择查询失败不兼容。”

我试图从拍卖列表中获取用户正在观看的拍卖数量，但是当用户选择多个拍卖来观看错误显示时。有人可以告诉我查询有什么问题：

SELECT auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId, COUNT(DISTINCT (bids.bidId)) AS numBids,
        MAX(bids.bidPrice) AS highestBid,
        case
            when MAX(bids.bidPrice)is not null THEN MAX(bids.bidPrice)
            else startPrice
        end as currentPrice,
        case
            when MAX(bids.bidPrice) > auctions.reservePrice AND auctions.endTime < now() then 1
            else 0
        end as sold


        FROM auctions
            LEFT OUTER JOIN bids ON bids.auctionId = auctions.auctionId
            JOIN auction_watches ON auction_watches.auctionId = auctions.auctionId
            JOIN items ON items.itemId = auctions.itemId
            JOIN users ON items.userId = users.userId
            JOIN item_categories ON items.categoryId = item_categories.categoryId
            JOIN super_item_categories ON  item_categories.superCategoryId = super_item_categories.superCategoryId
            JOIN item_conditions ON items.conditionId = item_conditions.conditionId
            JOIN countries ON users.countryId = countries.countryId

        WHERE auction_watches.watchId IN( 19706, 19707 )

        GROUP BY auctions.auctionId

        ORDER BY CASE WHEN auctions.endTime > now() THEN 0 ELSE 1 END ASC, auctions.endTime ASC

标签： mysqlsql

解决方案

您需要在 group by 子句中添加除聚合列之外的所有其他列

SELECT auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId, COUNT(DISTINCT (bids.bidId)) AS numBids,
        MAX(bids.bidPrice) AS highestBid,
        case
            when MAX(bids.bidPrice)is not null THEN MAX(bids.bidPrice)
            else startPrice
        end as currentPrice,
        case
            when MAX(bids.bidPrice) > auctions.reservePrice AND auctions.endTime < now() then 1
            else 0
        end as sold


        FROM auctions
            LEFT OUTER JOIN bids ON bids.auctionId = auctions.auctionId
            JOIN auction_watches ON auction_watches.auctionId = auctions.auctionId
            JOIN items ON items.itemId = auctions.itemId
            JOIN users ON items.userId = users.userId
            JOIN item_categories ON items.categoryId = item_categories.categoryId
            JOIN super_item_categories ON  item_categories.superCategoryId = super_item_categories.superCategoryId
            JOIN item_conditions ON items.conditionId = item_conditions.conditionId
            JOIN countries ON users.countryId = countries.countryId

        WHERE auction_watches.watchId IN( 19706, 19707 )

        GROUP BY auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId

        ORDER BY CASE WHEN auctions.endTime > now() THEN 0 ELSE 1 END ASC, auctions.endTime ASC

推荐阅读