create table #a
(time int,
type int,
value int)insert into #a
select 1,1,2
union all
select 1,2,3
union all
select 2,2,5
union all
select 2,1,1declare @s varchar(8000)set @s=''select @s=@s+'(select value from #a where type='+cast(type as char)+'and time=a.time) as type_'+cast(type as char)+',' from #a group by type
select @s='select time, '+left(@s,len(@s)-1)+' from #a a group by time'
select @s
exec (@s)
drop table #a/*
time type_1 type_2
----------- ----------- -----------
1 2 3
2 1 5
*/
(time int,
type int,
value int)insert into #a
select 1,1,2
union all
select 1,2,3
union all
select 2,2,5
union all
select 2,1,1declare @s varchar(8000)set @s=''select @s=@s+'(select value from #a where type='+cast(type as char)+'and time=a.time) as type_'+cast(type as char)+',' from #a group by type
select @s='select time, '+left(@s,len(@s)-1)+' from #a a group by time'
select @s
exec (@s)
drop table #a/*
time type_1 type_2
----------- ----------- -----------
1 2 3
2 1 5
*/
declare @str varchar(1000)set @str='select time'select @str=@str+',[type_'+cast(type as char)+']=sum(case type when '+cast(type as char)+' then value else 0 end)'
from #a
group by typeexec(@str+'from #a group by time')
time type value
----------- ----------- -----------
1 1 2
1 2 3
2 2 5
2 1 1
------------------------------------------
select time,
value_type1=sum(case type when 1 then value end),
value_type2=sum(case type when 2 then value end)
from a
group by time
-------------------
result:
time value_type1 value_type2
----------- ----------- -----------
1 2 3
2 1 5
value_type1=sum(case type when 1 then value end),
value_type2=sum(case type when 2 then value end)
from [table] group by time having count(*) =2
想写个存储过程,返回表为指定的其中几个字段。假如返回type_3,type_4两个字段。怎么实现?搞定了,马上给分。
CREATE PROCEDURE sp_GetHistoryUnion
@start smalldatetime,
@end smalldatetime,
@Sid tinyint
AS
begindeclare @str varchar(1000)set @str='select GetTime 'select @str=@str+',[SDid_'+cast(SDid as char)+']=sum(case SDid when '+cast(SDid as char)+' then Value else 0 end)'
from tblHistoryData
group by SDidexec(@str+'from tblHistoryData where Sid='+@Sid+' and GetTime>'''+@start+''' and GetTime<'''+@end+''' group by GetTime ')
end
GO
是可以返回Sid=@Sid的所以SDid的可能值的字段。
能不能返回指定的SDid的其实几个字段?
create table #a
(time int,
type int,
value int)insert into #a
select 1,1,2
union all
select 1,2,3
union all
select 1,3,4
union all
select 2,2,5
union all
select 2,1,1
union all
select 2,3,7
union all
select 3,1,8
union all
select 3,2,7
union all
select 3,3,9
union all
select 4,1,3
union all
select 4,2,9
union all
select 4,3,11
union all
select 5,1,20
union all
select 5,2,23
union all
select 5,3,31declare @s varchar(8000)set @s=''select @s='(select value from #a where type=1 and time=a.time) as type_1'+',(select value from #a where type=2 and time=a.time) as type_2' from #a group by type
select @s='select time, '+@s+' from #a a group by time'
select @s
exec (@s)
drop table #a
@start smalldatetime,
@end smalldatetime,
@Sid tinyint
AS
exec('select GetTime ,case SDid when '+cast(@Sid as varchar)+' then value else 0 end [SDid_'+cast(@Sid as varchar)+'] from tblHistoryData where Sid='+cast(@Sid as varchar)+' and GetTime>'''+convert(varchar,@start,110)+''' and GetTime<'''+convert(varchar,@end,110)+''' group by GetTime')
CREATE PROCEDURE sp_GetWind
@Sid tinyint,
@start smalldatetime,
@end smalldatetime
AS
begin
declare @s varchar(1000)
set @s=''select @s='(select Value from tblHistoryData where SDid=3 and GetTime=a.GetTime and Sid='+@Sid+') as SDid_3'+',(select Value from tblHistoryData where SDid=4 and GetTime=a.GetTime and Sid='+@Sid+') as SDid_4' from tblHistoryData group by SDid
select @s='select GetTime, '+@s+' from tblHistoryData a where Sid='+@Sid+' and GetTime>'+@start+' and GetTime<'+@end+' group by GetTime'
exec (@s)
end
GO
我试过这个也是不行的。可以写进去,但是运行
服务器: 消息 245,级别 16,状态 1,过程 sp_GetWind,行 10
将 varchar 值 '(select Value from tblHistoryData where SDid=3 and GetTime=a.GetTime and Sid=' 转换为数据类型为 tinyint 的列时发生语法错误。
@Sid tinyint,
@start smalldatetime,
@end smalldatetime
AS
begin
declare @s varchar(1000)
set @s=''select @s='(select Value from tblHistoryData where SDid=3 and GetTime=a.GetTime and Sid='+Rtrim(@Sid)+') as SDid_3'+',(select Value from tblHistoryData where SDid=4 and GetTime=a.GetTime and Sid='+Rtrim(@Sid)+') as SDid_4' from tblHistoryData group by SDid
select @s='select GetTime, '+@s+' from tblHistoryData a where Sid='+Rtrim(@Sid)+' and GetTime>'+Convert(Varchar,@start,120)+' and GetTime<'+Convert(Varchar,@end,120)+' group by GetTime'
exec (@s)
end
GO
你的有错误。不过我想了个办法。先写个视图。
declare @s varchar(1000)set @s=''select @s='(select Value from tblHistoryData where SDid=1 and GetTime=a.GetTime) as [UpLevel],(select Value from tblHistoryData where SDid=2 and GetTime=a.GetTime) as [DownLevel]' from tblHistoryData group by SDid
select @s='select GetTime,Sid,'+@s+' from tblHistoryData a group by GetTime,Sid'
exec (@s)然后,再写存储过程传参数进来,访问这个视图。这样的方法,合理不合理啊?
paoluo(一天到晚游泳的鱼)
你的有错误。不过我想了个办法。先写个视图。
-------------------------------------------------
啥錯誤??我是在你的語句的基礎上改的,只是加了個類型轉換。前面的我沒仔細看。
第 1 行: '00' 附近有语法错误。
存储过程: stWaterMeas.dbo.sp_GetWind
返回代码 = 0
create table t_tablename(
ftime datetime null,
ftype int null,
fvalue int null
)
-- 插入测试值
insert into t_tablename(ftime,ftype,fvalue)
values('2005-01-01',1,10)
insert into t_tablename(ftime,ftype,fvalue)
values('2005-01-01',2,20)
insert into t_tablename(ftime,ftype,fvalue)
values('2005-01-02',1,10)
insert into t_tablename(ftime,ftype,fvalue)
values('2005-01-02',2,20)
-- 获取结果
select ftime,
isnull(sum(case when ftype = 1 then fvalue end),0) as value_type1,
isnull(sum(case when ftype = 2 then fvalue end),0) as value_type2
from t_tablename
group by ftime--不知道有没有理解错楼主本意,希望能对你有所帮助