Sql查询出错 [英] Error in the Sql Query

问题描述

SELECT

        LXA.AuctionID
        ,LAD.OpeningPrice
        ,AP.Description
        ,UR.Name
        ,UR.BigLeapID
        ,CONVERT(VARCHAR,LXA.Date)Date

     ,CASE WHEN LXA.IsAutoBid=1 then ISNULL(max(LXA.BidPrice),0)+LAD.BidIncrement else LXA.BidPrice End topay
  FROM  LiveXAuctionBid LXA
        INNER JOIN LiveAuctionBid LA ON LA.LiveAuctionBidID = LXA.LiveAuctionBidID
        INNER JOIN UserRegistration UR ON UR.UserRegistrationID = LA.UserID
        INNER JOIN LiveAuctionDetails LAD ON LAD.LiveAuctionDetailID = LXA.AuctionID
        INNER JOIN AuctionProduct AP ON AP.AuctionProductID = LAD.ProductID
  WHERE LAD.LiveAuctionDetailID = @LiveAuctionDetailID

  group by  LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

错误：



列'LiveXAuctionBid.IsAutoBid'在选择列表中无效b因为它不包含在聚合函数或GROUP BY子句中。



列'LiveAuctionDetails.BidIncrement'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句。



列'LiveXAuctionBid.BidPrice'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句。

Error:

Column 'LiveXAuctionBid.IsAutoBid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'LiveAuctionDetails.BidIncrement' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'LiveXAuctionBid.BidPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

推荐答案

当您使用GROUP BY子句时，它会根据指定的组聚合记录：因此，如果您按城市对客户进行分组，则会返回n记录，其中n是客户所在的不同城市的数量。在该查询中，除非使用汇总，否则无法访问个别客户函数（例如）计算它们，或将它们加在一起 ​​- 因为GROUP将它们转换为单个记录。



我无法建议你需要做什么至修复您的查询 - 因为我不知道您的数据是什么样的，或者您希望获得什么结果 - 但您无法访问GROUP中的出价状态，出价或增量。最有可能的是，您需要使用某种形式的子查询而不是组，但正如我所说，我可以看到您的数据。



返回绘图板，并准确地确定您要返回的内容 - 在Excel或纸上构建虚拟数据集 - 然后查看如何提取数据。我认为你现在正朝着错误的道路前进。

When you use a GROUP BY clause, it aggregates records according to the specified groups: so if you group your customers by city, you get back "n" records, where "n" is the number of different cities your customers are based in. Within that query, you can't access individual customers except by using an aggregate function which (for example) counts them, or add them together - because the GROUP converts them to a single record.

I can't suggest what you need to do to fix you query - because I have no idea what your data looks like, or what result you expect to get - but you can't access the bid status, bid price or increment within the GROUP. Most likely, you need to use a sub query of some form instead of a group, but as I say, I can;t see your data.

Go back to the drawing board, and work out exactly what you are trying to return - build a "dummy data set" in Excel or on paper - and then look at how you extract that data. I think you are heading down the wrong road at the moment.

'have'用于逐个功能....并且还使用聚合函数和字段除外的字段LXA.AuctionID，LAD.OpeningPrice，AP.Description，UR.Name，UR.ID，Date

'having ' is used with group by function ....and also use aggregate function with fields except the group by fields like LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

看看这个：

Look ate this:

WITH cte AS (
SELECT 
    LXA.AuctionID
    , ISNULL(MAX(LXA.BidPrice), 0) AS MaxBidPrice
FROM LiveXAuctionBid LXA 
GROUP BY LXA.AuctionID
)
SELECT
    LXA.AuctionID
    ,LAD.OpeningPrice
    ,AP.Description
    ,UR.Name
    ,UR.BigLeapID
    ,CONVERT(VARCHAR,LXA.Date)Date
    ,CASE WHEN LXA.IsAutoBid=1 then cte.MaxBidPrice +LAD.BidIncrement else LXA.BidPrice End topay
FROM  LiveXAuctionBid LXA
INNER JOIN cte ON LXA.AuctionID = cte.AuctionID 
INNER JOIN LiveAuctionBid LA ON LA.LiveAuctionBidID = LXA.LiveAuctionBidID
INNER JOIN UserRegistration UR ON UR.UserRegistrationID = LA.UserID
INNER JOIN LiveAuctionDetails LAD ON LAD.LiveAuctionDetailID = LXA.AuctionID
INNER JOIN AuctionProduct AP ON AP.AuctionProductID = LAD.ProductID
WHERE LAD.LiveAuctionDetailID = @LiveAuctionDetailID
GROUP BY LXA.AuctionID,LAD.OpeningPrice,AP.Description,UR.Name,UR.ID,Date

这篇关于Sql查询出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！