求存储过程

[raymond123456]

表中字段：area,datetime,company,(city1,city2,city3)
实现：根据指定area，datetime,company 下查询avg(city1)或者avg(city2)或者avg(city3)的平均值
条件：(city1,city2,city3)是表中的列即字段

也就是说city1,city2,city3 是做为参数传到存储过程中的。

请问这样的存储过程怎么写？

[raymond123456]

我对存储过程不是很了解，可以给出SQL语句吗

[--小F--]

create procdure pro_city
(
@city1 varchar(10),
@city2 varchar(10),
@city3 varchar(10)
)
select area,datetime,company,avg(..) from tb where @city1='xxx'

[昵称被占用了]

create proc pr_query
@city int
as

if @city = 1
   select area,datetime,company,avg(city1) as avgdata
   from tablename
   group by area,datetime,company
if @city = 2
   select area,datetime,company,avg(city2) as avgdata
   from tablename
   group by area,datetime,company

if @city = 3
   select area,datetime,company,avg(city3) as avgdata
   from tablename
   group by area,datetime,company
go

[--小F--]

[Quote=引用 5 楼 haiwer 的回复:]
SQL code
create proc pr_query
@city int
as

if @city = 1
select area,datetime,company,avg(city1) as avgdata
from tablename
group by area,datetime,company
if @city = 2
select area……
[/Quote]
丫的 我想错了？

[raymond123456]

不仅仅是3个哦 这个我举个例子而已，实际表中有很多字段

[Mr_Nice]

[Quote=引用 6 楼 fredrickhu 的回复:]

引用 5 楼 haiwer 的回复:
SQL code
create proc pr_query
@city int
as

if @city = 1
select area,datetime,company,avg(city1) as avgdata
from tablename
group by area,datetime,company
if @city = 2
se……
[/Quote]

我想你的没错！

[AcHerat]

create proc sp_get(@col nvarchar(20))
as
begin
declare @sql varchar(max)
set @sql = N'select area,[datetime],[company],avg(' + @col + ') as av_col from tb'
exec(@sql)
end
go

exec sp_get 'city1'

[raymond123456]

area，datetime,company 是作为查询条件的

[昵称被占用了]

搂主说不清就举例吧，列出数据及需要的结果
别浪费大家时间

[AcHerat]

create proc sp_get(@col nvarchar(20))
as
begin
declare @sql varchar(max)
set @sql = N'select [其他字段可以加在这],avg(' + @col + ') as av_col from tb where area = .. and ...'
exec(@sql)
end
go

exec sp_get 'city1'

[AcHerat]

create proc sp_get(@col nvarchar(20),@area nvarchar(20),@datetime datetime,@company nvarchar(20))
as
begin
declare @sql varchar(max)
set @sql = N'select [其他字段可以加在这],avg(' + @col + ') as av_col from tb where area = ' + @area + ' and convert(varchar(10),[datetime],120) = ' + convert(varchar(10),@datetime,120) + ' and company = ' + @company
exec(@sql)
end
go

exec sp_get 'city1'

[AcHerat]

[Quote=引用 13 楼 acherat 的回复:]

SQL code

create proc sp_get(@col nvarchar(20),@area nvarchar(20),@datetime datetime,@company nvarchar(20))
as
begin
declare @sql varchar(max)
set @sql = N'select [其他字段可以加在这],avg(' + @col + ') as av……
[/Quote]


exec sp_get 'city1',N'老家','2011-01-01','火星'

[raymond123456]

我先去测试，成功就结贴！

[王向飞]

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?

[raymond123456]

CREATE PROCEDURE sp_get 
@col nvarchar(20),
@area nvarchar(20),
@datetime datetime,
@company nvarchar(20)
AS
BEGIN
	declare @sql varchar(max)
set @sql = N'select avg(' + @col + ') as av_col from Basic where area = ' + @area + ' and convert(varchar(10),[datetime],120) = ' + convert(varchar(10),@datetime,120) + ' and company = ' + @company
exec(@sql)
end
go

exec sp_get 'city_first',N'上海区','2011-03-01','EMS'

报错：消息 207，级别 16，状态 1，第 1 行
列名 '上海区' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'datetime' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'EMS' 无效。

[AcHerat]

CREATE PROCEDURE sp_get 
@col nvarchar(20),
@area nvarchar(20),
@datetime datetime,
@company nvarchar(20)
AS
BEGIN
    declare @sql varchar(max)
set @sql = N'select avg(' + @col + ') as av_col from Basic where area = ''' + @area + ''' and convert(varchar(10),[datetime],120) = ''' 
            + convert(varchar(10),@datetime,120) + ''' and company = ''' + @company + ''
exec(@sql)
end
go

exec sp_get 'city_first',N'上海区','2011-03-01','EMS'

[raymond123456]

表结构：

表数据：

[AcHerat]

--上边不行的话，看这个

CREATE PROCEDURE sp_get 
@col nvarchar(20),
@area nvarchar(20),
@datetime datetime,
@company nvarchar(20)
AS
BEGIN
    declare @sql varchar(max)
set @sql = N'select avg(' + @col + ') as av_col from Basic where area = ''' + @area + ''' and convert(varchar(10),[datetime],120) = ''' 
            + convert(varchar(10),@datetime,120) + ''' and company = ''' + @company + ''''
exec(@sql)
end
go

exec sp_get 'city_first',N'上海区','2011-03-01','EMS'

[raymond123456]

我按照你的， 但是结果是：NULL

[raymond123456]

[Quote=引用 20 楼 acherat 的回复:]
SQL code

--上边不行的话，看这个

CREATE PROCEDURE sp_get
@col nvarchar(20),
@area nvarchar(20),
@datetime datetime,
@company nvarchar(20)
AS
BEGIN
declare @sql varchar(max)
set @sql = N'select……
[/Quote]

这个是正确的，谢谢！