Sql示例说明怎样分组后求中间值.docxVIP

这里所谓的分组后求中间值是个什么概念呢？ 我举个例子来说明一下： 假设我们现在有下面这样一个表： ? type????????name price ----------- ---- ------------ 2???????????A????5.4 2???????????A????3.7 2???????????B????4.3 1???????????B????4.7 2???????????B????6.7 2???????????B????9.8 3???????????a????3.0 3???????????a????4.0 3???????????a????10.0 3???????????a????20.0 ? 我们要得到什么样的效果呢？ 按type和name进行分组后求中间值： ? 是不是就是这样呢： ? select?[type],name,AVG(price)?from?@table group?by?type,name ? /* type????????name ----------- ---- --------------------------------------- 2???????????A????4.550000 3???????????a????9.250000 1???????????B????4.700000 2???????????B????6.933333 ? */ 不是的，我所说的中间值和平均数还不太一样，平均数就是均值，但是中间值是说分组如果有奇数个就是中间那个，如果有偶数个就是中间两个值的均值。 例如上面数据中3，a分组后有3，4，10，20四个数，那中间值就是4和10个均值。 例如上面按2，B分组后有4.3 和6.7 及9.8三个值，那中间值就是6.7 ? 我们如何能达到这样效果呢，写个简单的例子： ? declare?@table?table?([type]?int,name?varchar(1),price?numeric(3,1)) insert?into?@table select?2,A,5.4?union?all select?2,A,3.7?union?all select?2,B,4.3?union?all select?1,B,4.7?union?all select?2,B,6.7?union?all select?2,B,9.8?union?all select?3,a,3?union?all select?3,a,4?union?all select?3,a,10?union?all select?3,a,20 ? --sql查询如下: ? ;with?maco?as ( ????select ????????[type],name,price, ????????m_asc=row_number()?over(partition?by?type,name?order?by?price), ????????m_desc=row_number()?over(partition?by?type,name?order?by?price?desc) ????from?@table ) --select * from liang select?[type],name,avg(price)?as?price from?maco where?m_asc?in(m_desc,m_desc+1,m_desc-1) group?by?type,name order?by?type,name ? /* type????????name price ----------- ---- ----------- 1???????????B????4.700000 2???????????A????4.550000 2???????????B????6.700000 3???????????a????7.000000 */