在SQL存储过程中分组 [英] Group by in SQL store procedure

问题描述

我只想要那些AucNumber和最高出价与日期时间相关的行会在我的Store rocedure中更改，我只想要价格最高的AucNumber



我想得到这样的结果

1 LE-16 2500 500000000 2016-09-22 12：13：13.000

2 LE-16 2000 60000 2016-09-20 15:40 ：16.000

50000 LE-16 2000 NULL NULL

800000 LE-16 2000 NULL NULL

 SELECT AuctionsSelectedNumber.AucNumber，
 AuctionsSelectedNumber.Auc_Series，AuctionsSelectedNumber.Price，
 AuctionsSelectedNumber.BiddingPrice，MAX（AuctionsSelectedNumber.BiddingDateTime）
 FROM AuctionsSelectedNumber 
 GROUP BY AucNumber，IsNonAction，Auc_Series，Price，BiddingPrice 

结果

 AucNumber Auc_Series价格竞价价格（无专栏名称）
 1 LE-16 1500 800000 2016-09-20 20：59：49.000 
 1 LE-16 2500 NULL NULL 
 1 LE-16 2500 200000 NULL 
 1 LE-16 2500 90000000 NULL 
 1 LE-16 2500 500000000 2016-09-22 12：13：13.000 
 2 LE-16 2000 60000 2016-09-20 15：40：16.000 
 2 LE-16 3000 NULL NULL 
 50000 LE-16 2000 NULL NULL 
 800000 LE-16 2000 NULL NULL 

我尝试了什么：



我只想要那些价格最高的AucNumber

解决方案

< blockquote>从查看GROUP BY开始 - 你有太多，太多的子句，这就是为什么你得到的行多于你想要的。 （这可能有助于您理解原因： SQL GROUP By和列'名称'在选择列表中无效，因为......错误 [ ^ ]）



所以从更简单的GROUP开始：

 SELECT AucNumber，MAX（BiddingPrice）AS MaxBP 
来自AuctionsSelectedNumber 
 GROUP BY AucNumber 

这不会返回你想要的所有信息，但是它应该把它带到你想要的行。

然后，使用JOIN将其与其他信息结合起来如果数据：

  SELECT  a。*  FROM  AuctionsSelectedNumber 
  JOIN （ SELECT  AucNumber，MAX（BiddingPrice） AS  MaxBP 
  FROM  AuctionsSelectedNumber 
  GROUP   BY  AucNumber）b 
  ON  a.AucNumber = b.AucNumber  AND  a.MaxBP = b.MaxBP 

我没有你的测试数据，所以我无法检查它 - 但它应该做你想要的。

如果没有，显示输入和输出数据，并解释输出错误的位置。

I want only those rows where AucNumber and highest bid price with datetime what will be changed in my Store rocedure, I want only AucNumber where price is highest

I want result like this
1 LE-16 2500 500000000 2016-09-22 12:13:13.000
2 LE-16 2000 60000 2016-09-20 15:40:16.000
50000 LE-16 2000 NULL NULL
800000 LE-16 2000 NULL NULL

SELECT AuctionsSelectedNumber.AucNumber,
       AuctionsSelectedNumber.Auc_Series,AuctionsSelectedNumber.Price,
	   AuctionsSelectedNumber.BiddingPrice,MAX(AuctionsSelectedNumber.BiddingDateTime)
FROM   AuctionsSelectedNumber
GROUP  BY AucNumber,IsNonAction,Auc_Series,Price,BiddingPrice

RESULT

AucNumber Auc_Series	Price	BiddingPrice	(No column name)
1	LE-16	1500	800000	2016-09-20 20:59:49.000
1	LE-16	2500	NULL	NULL
1	LE-16	2500	200000	NULL
1	LE-16	2500	90000000	NULL
1	LE-16	2500	500000000	2016-09-22 12:13:13.000
2	LE-16	2000	60000	2016-09-20 15:40:16.000
2	LE-16	3000	NULL	NULL
50000	LE-16	2000	NULL	NULL
800000	LE-16	2000	NULL	NULL

What I have tried:

I want only those AucNumber where price is highest

解决方案

Start by looking at the GROUP BY - you have far, far too many clauses in there, which is why you get more rows than you want. (This may help you understand why: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^])

So start with a simpler GROUP By:

SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
FROM AuctionsSelectedNumber
GROUP BY AucNumber

That won't return you all the info you want, but it should bring it down the just the rows you want.
Then, use a JOIN to combine that with the rest if the data:

SELECT a.* FROM AuctionsSelectedNumber
JOIN (SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
      FROM AuctionsSelectedNumber
      GROUP BY AucNumber) b
ON a.AucNumber = b.AucNumber AND a.MaxBP = b.MaxBP

I don't have your test data, so I can't check it - but that should do what you want.
If it doesn't, show your input and output data, and explain where the output is wrong.

这篇关于在SQL存储过程中分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！