use northwind go if object_id('pRemoveTopAndBottom') is not null drop proc pRemoveTopAndBottom go create procedure pRemoveTopAndBottom(@fSumOrAvg bit, @fRemoveTop bit,@fRemoveBottom bit) as declare @sql varchar(4000), @select varchar(400) declare @part1 varchar(400), @part2 varchar(400) ,@part3 varchar(400), @part4 varchar(400) select @part1='', @part2='', @part3='', @part4='' if @fSumOrAvg=1 --1表示求avg(Quantity) set @select=' select orderid, 处理后Quantity=avg(Quantity) ' else set @select=' select orderid, 处理后Quantity=sum(Quantity) ' --第一部分赋值 set @part1=' select * from [Order Details] as ta where orderid in( select orderid from [Order Details] group by orderid having count(1)>2) ' if @fRemoveTop=1 --去掉最高值 set @part2=' and exists( select 1 from [Order Details] as tb where ta.orderid=tb.orderid and ta.quantity<tb.quantity) ' if @fRemoveBottom=1 --去掉最低值 set @part3=' and exists( select 1 from [Order Details] as tb where ta.orderid=tb.orderid and ta.quantity>tb.quantity) ' --第四部分赋值 set @part4=' union all select * from [Order Details] as ta where orderid in( select orderid from [Order Details] group by orderid having count(1)<=2) '--串联起sql语句 set @sql=@select+' from ('+@part1+@part2+@part3+@part4+')t group by orderid' --print @sql exec(@sql) go--测试 exec pRemoveTopAndBottom 0,1,1
只要在上面的朋友基础下稍修改:建一个临时表test加一个按orderid,quantity排序即可。 use northwind go if object_id('pRemoveTopAndBottom') is not null drop proc pRemoveTopAndBottom go create procedure pRemoveTopAndBottom(@fSumOrAvg bit, @fRemoveTop bit,@fRemoveBottom bit) as declare @sql varchar(4000), @select varchar(400) declare @part1 varchar(400), @part2 varchar(400) ,@part3 varchar(400), @part4 varchar(400) select @part1='', @part2='', @part3='', @part4=''if exists(select 1 from sysobjects where type='u' and name='test') drop testSELECT IDENTITY(int, 1,1) AS id,* into test from test order by orderid,Quantityif @fSumOrAvg=1 --1表示求avg(Quantity) set @select=' select orderid, 处理后Quantity=avg(Quantity) ' else set @select=' select orderid, 处理后Quantity=sum(Quantity) ' --第一部分赋值 set @part1=' select * from test as ta where orderid in( select orderid from test group by orderid having count(1)>2) ' if @fRemoveTop=1 --去掉最高值 set @part2=' and exists( select 1 from test as tb where ta.orderid=tb.orderid and ta.id<tb.id) ' if @fRemoveBottom=1 --去掉最低值 set @part3=' and exists( select 1 from test as tb where ta.orderid=tb.orderid and ta.id>tb.id) ' --第四部分赋值 set @part4=' union all select * from test as ta where orderid in( select orderid from test group by orderid having count(1)<=2) '--串联起sql语句 set @sql=@select+' from ('+@part1+@part2+@part3+@part4+')t group by orderid' --print @sql exec(@sql) go--测试 exec pRemoveTopAndBottom 0,1,1
改为
@fSumOrAvg bit, -- 0表示求sum(Quantity),1表示求avg(Quantity)
返回结果,只要求两列: orderid和处理后的Quantity值
go
if object_id('pRemoveTopAndBottom') is not null drop proc pRemoveTopAndBottom
go
create procedure pRemoveTopAndBottom(@fSumOrAvg bit, @fRemoveTop bit,@fRemoveBottom bit)
as
declare @sql varchar(4000), @select varchar(400)
declare @part1 varchar(400), @part2 varchar(400)
,@part3 varchar(400), @part4 varchar(400)
select @part1='', @part2='', @part3='', @part4=''
if @fSumOrAvg=1 --1表示求avg(Quantity)
set @select=' select orderid, 处理后Quantity=avg(Quantity) '
else
set @select=' select orderid, 处理后Quantity=sum(Quantity) '
--第一部分赋值
set @part1=' select *
from [Order Details] as ta
where orderid in(
select orderid
from [Order Details]
group by orderid
having count(1)>2) '
if @fRemoveTop=1 --去掉最高值
set @part2=' and exists(
select 1
from [Order Details] as tb
where ta.orderid=tb.orderid
and ta.quantity<tb.quantity) '
if @fRemoveBottom=1 --去掉最低值
set @part3=' and exists(
select 1
from [Order Details] as tb
where ta.orderid=tb.orderid
and ta.quantity>tb.quantity) '
--第四部分赋值
set @part4=' union all
select *
from [Order Details] as ta
where orderid in(
select orderid
from [Order Details]
group by orderid
having count(1)<=2) '--串联起sql语句
set @sql=@select+' from ('+@part1+@part2+@part3+@part4+')t group by orderid'
--print @sql
exec(@sql)
go--测试
exec pRemoveTopAndBottom 0,1,1
当一个orderid有多条记录时(>2),最高值如果有多条时,运行结果不对!
-- 0表示不去掉最高值,1表示去掉最高值(有多个最高值时只能去掉一个)
-- 0表示不去掉最低值,1表示去掉最低值(有多个最低值时只能去掉一个)
烦请再看一下!
use northwind
go
if object_id('pRemoveTopAndBottom') is not null drop proc pRemoveTopAndBottom
go
create procedure pRemoveTopAndBottom(@fSumOrAvg bit, @fRemoveTop bit,@fRemoveBottom bit)
as
declare @sql varchar(4000), @select varchar(400)
declare @part1 varchar(400), @part2 varchar(400)
,@part3 varchar(400), @part4 varchar(400)
select @part1='', @part2='', @part3='', @part4=''if exists(select 1 from sysobjects where type='u' and name='test')
drop testSELECT IDENTITY(int, 1,1) AS id,* into test from test order by orderid,Quantityif @fSumOrAvg=1 --1表示求avg(Quantity)
set @select=' select orderid, 处理后Quantity=avg(Quantity) '
else
set @select=' select orderid, 处理后Quantity=sum(Quantity) '
--第一部分赋值
set @part1=' select *
from test as ta
where orderid in(
select orderid
from test
group by orderid
having count(1)>2) '
if @fRemoveTop=1 --去掉最高值
set @part2=' and exists(
select 1
from test as tb
where ta.orderid=tb.orderid
and ta.id<tb.id) '
if @fRemoveBottom=1 --去掉最低值
set @part3=' and exists(
select 1
from test as tb
where ta.orderid=tb.orderid
and ta.id>tb.id) '
--第四部分赋值
set @part4=' union all
select *
from test as ta
where orderid in(
select orderid
from test
group by orderid
having count(1)<=2) '--串联起sql语句
set @sql=@select+' from ('+@part1+@part2+@part3+@part4+')t group by orderid'
--print @sql
exec(@sql)
go--测试
exec pRemoveTopAndBottom 0,1,1
服务器: 消息 170,级别 15,状态 1,过程 pRemoveTopAndBottom_2,行 9
第 9 行: 'test' 附近有语法错误。
服务器: 消息 2812,级别 16,状态 62,行 3
未能找到存储过程 'pRemoveTopAndBottom'。
drop table test