up有分，请大家帮帮忙如何实现！！！
数据表如下
cate values dates
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-04-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-04-01
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-04-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-04-01
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-05-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-05-01
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-05-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-05-01
不用存储过程实现如下数据统计
cate T1 dates
A1 24.63,31.2,29.0,89.0,156.24,46.9 2006-04-01
A1 24.63,31.2,29.0,89.0,156.24,46.9 2006-05-01
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-04-01
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-05-01
==========================================================================
就是 values 值之间用,隔开的,当cate和dates 相同时 算values中各个以,隔开的数值的累加,要求是不用存储过程实现
[解决办法]
up?
[解决办法]
up有分
[解决办法]
select
distinct t.*
from
表 t
where
not exists(select 1 from 表 where cate=t.cate and dates=t.dates and values> t.values)
[解决办法]
declare @t table(cate varchar(10),[values] varchar(40),dates varchar(10))
insert into @t select 'A1 ', '12.32,15.6,14.5,45.00,78.12,23.45 ', '2006-04-01 '
insert into @t select 'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-04-01 '
insert into @t select 'A1 ', '12.31,15.6,14.5,44.00,78.12,23.45 ', '2006-04-01 '
insert into @t select 'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-04-01 '
insert into @t select 'A1 ', '12.32,15.6,14.5,45.00,78.12,23.45 ', '2006-05-01 '
insert into @t select 'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-05-01 '
insert into @t select 'A1 ', '12.31,15.6,14.5,44.00,78.12,23.45 ', '2006-05-01 '
insert into @t select 'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-05-01 '
select
distinct t.*
from
@t t
where
not exists(select 1 from @t where cate=t.cate and dates=t.dates and [values] <t.[values])
order by
t.dates,t.cate
/*
cate values dates
---------- ---------------------------------------- ----------
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-04-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-04-01
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-05-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-05-01
*/
[解决办法]
学习```寒一个自己``刚开始写``人家就写出来了```
[解决办法]
libin_ftsafe(子陌红尘：TS for Banking Card),你看錯了吧，要匯總的。
[解决办法]
至少也要借用函數或者臨時表的，沒有那麼簡單的。
[解决办法]
caibaoying(为了泡妞学SQL~~~) ( ) 信誉：100 2007-07-23 10:43:02 得分: 0
学习```寒一个自己``刚开始写``人家就写出来了```
-----------------
上面的答案不正確，你還是可以寫。:)
[解决办法]
原来是要汇总，看来得用函数了，呵呵。
[解决办法]
確實
[解决办法]
要是能用存储过程就好了
函数还没想到好办法
[解决办法]
up
[解决办法]
原来都在这里
[解决办法]
--建立环境
create table up(
cate varchar(3),
[values] varchar(50),
dates datetime
)
insert up select
'A1 ', '12.32,15.6,14.5,45.00,78.12,23.45 ', '2006-04-01 '
union all select
'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-04-01 '
union all select
'A1 ', '12.31,15.6,14.5,44.00,78.12,23.45 ', '2006-04-01 '
union all select
'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-04-01 '
union all select
'A1 ', '12.32,15.6,14.5,45.00,78.12,23.45 ', '2006-05-01 '
union all select
'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-05-01 '
union all select
'A1 ', '12.31,15.6,14.5,44.00,78.12,23.45 ', '2006-05-01 '
union all select
'A2 ', '14.56,67.23,45.12,23.1,34.67,56.98 ', '2006-05-01 '
go
--加法函数
create function fn_Plus(
@s1 varchar(50),
@s2 varchar(50)
)
returns varchar(50)
as
begin
if @s1 is null or @s1= ' '
return @s2
if @s2 is null or @s2= ' '
return @s1
declare @r varchar(50)
set @r= ' '
declare @f1 float
declare @f2 float
while charindex( ', ',@s1)> 0 and charindex( ', ',@s2)> 0
begin
set @f1=left(@s1,charindex( ', ',@s1)-1)
set @f2=left(@s2,charindex( ', ',@s2)-1)
set @s1=stuff(@s1,1,charindex( ', ',@s1), ' ')
set @s2=stuff(@s2,1,charindex( ', ',@s2), ' ')
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
end
if @s1 <> ' ' and @s2 <> ' '
set @f1=@s1
set @f2=@s2
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
if @r <> ' '
set @r=stuff(@r,1,1, ' ')
return @r
end
go
--求值函数
create function fn_Values(
@cate varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
select @r=dbo.fn_Plus(@r,[values]) from up where cate=@cate and dates=@dates
return @r
end
go
--查询
select cate,dbo.fn_values(cate,dates) as T,dates
from up
group by cate,dates
--结果
cate T dates
---- -------------------------------------------------- ------------------------------------------------------
A1 24.63,31.2,29,89,156.24,46.9 2006-04-01 00:00:00.000
A1 24.63,31.2,29,89,156.24,46.9 2006-05-01 00:00:00.000
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-04-01 00:00:00.000
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-05-01 00:00:00.000
（所影响的行数为 4 行）
[解决办法]
两个函数可以搞定
[解决办法]
比较了下结果
区别只在于
29.0,89.0
--〉
29,89
[解决办法]
平均值？？
现在你的例子每个cate,dates组合都只有两条记录，算平均值是可以的，但是你会不会出现超过两条的记录或者只有一条记录的？
[解决办法]
学习
[解决办法]
还是改算法吧，适应不定条数记录
--求平均值函数
create function fn_ValuesAvg(
@cate varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
declare @t table (
[values] varchar(50)
)
insert @t select [values] from up where cate=@cate and dates=@dates
declare @t1 table (
No int,
[values] float
)
declare @No int
set @No=1
while exists (select 1 from @t where charindex( ', ',[values])> 0)
begin
insert @t1 select @No,left([values],charindex( ', ',[values])-1) from @t
update @t set [values]=stuff([values],1,charindex( ', ',[values]), ' ')
set @No=@No+1
end
insert @t1 select @No,[values] from @t
select @r=@r+ ', '+cast(avg([Values]) as varchar) from @t1 group by No order by No
return stuff(@r,1,1, ' ')
end
go
--查询
select cate,dbo.fn_valuesavg(cate,dates) as T,dates
from up
group by cate,dates
--结果
cate T dates
---- -------------------------------------------------- ------------------------------------------------------
A1 12.315,15.6,14.5,44.5,78.12,23.45 2006-04-01 00:00:00.000
A1 12.315,15.6,14.5,44.5,78.12,23.45 2006-05-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-04-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-05-01 00:00:00.000
（所影响的行数为 4 行）
[解决办法]
注意这些函数度没有错误检测功能,默认数据的个数是相同的,如果出现数据个数不同,如
cate values dates
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-04-01
A1 12.31,15.6,14.5,44.00,78.12 2006-04-01
可能有预想不到的错误
[解决办法]
来学习了，sql还真难，以前光insert,update了，寒一个
[解决办法]
先UP再看内容
[解决办法]
改，加判断是否数字
--加法函数
alter function fn_Plus(
@s1 varchar(50),
@s2 varchar(50)
)
returns varchar(50)
as
begin
if @s1 is null or @s1= ' '
return @s2
if @s2 is null or @s2= ' '
return @s1
declare @r varchar(50)
set @r= ' '
declare @f1 float
declare @f2 float
while charindex( ', ',@s1)> 0 and charindex( ', ',@s2)> 0
begin
set @f1=case when isnumeric(left(@s1,charindex( ', ',@s1)-1))=1 then left(@s1,charindex( ', ',@s1)-1) else 0 end
set @f2=case when isnumeric(left(@s2,charindex( ', ',@s2)-1))=1 then left(@s2,charindex( ', ',@s2)-1) else 0 end
set @s1=stuff(@s1,1,charindex( ', ',@s1), ' ')
set @s2=stuff(@s2,1,charindex( ', ',@s2), ' ')
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
end
if @s1 <> ' ' and @s2 <> ' '
set @f1=case when isnumeric(@s1)=1 then 0 end
set @f2=case when isnumeric(@s2)=1 then 0 end
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
if @r <> ' '
set @r=stuff(@r,1,1, ' ')
return @r
end
go
--求平均值函数
alter function fn_ValuesAvg(
@cate varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
declare @t table (
[values] varchar(50)
)
insert @t select [values] from up where cate=@cate and dates=@dates
declare @t1 table (
No int,
[values] float
)
declare @No int
set @No=1
while exists (select 1 from @t where charindex( ', ',[values])> 0)
begin
insert @t1 select @No,
case when isnumeric(left([values],charindex( ', ',[values])-1))=1 then left([values],charindex( ', ',[values])-1) else 0 end
from @t
update @t set [values]=stuff([values],1,charindex( ', ',[values]), ' ')
set @No=@No+1
end
insert @t1 select @No,case when isnumeric([values])=1 then [values] else 0 end from @t
select @r=@r+ ', '+cast(avg([Values]) as varchar) from @t1 group by No order by No
return stuff(@r,1,1, ' ')
end
go
[解决办法]
create table #a(A varchar(10),b1 numeric(8,2),b2 numeric(8,2),b3 numeric(8,2),b4 numeric(8,2),b5 numeric(8,2),b6 numeric(8,2),c datetime)
-----------------------------
declare cur_a cursor for
select 'insert #a select ' ' '+cate+ ' ' ', '+[values]+ ', ' ' '+convert(varchar(10),dates,120)+ ' ' ' ' from tb_a
open cur_a
declare @a varchar(200)
fetch next from cur_a into @a
while(@@fetch_status=0)
begin
exec (@a)
fetch next from cur_a into @a
end
close cur_a
deallocate cur_a
-------------------------------
select a,b=rtrim(sum(b1))+rtrim(sum(b2))+rtrim(sum(b3))+rtrim(sum(b4))+rtrim(sum(b5))+rtrim(sum(b6)),c from #a group by a,c
-------
A1 24.6331.2029.0089.00156.2446.90 2006-04-01 00:00:00.000
A2 29.12134.4690.2446.2069.34113.96 2006-04-01 00:00:00.000
A1 24.6331.2029.0089.00156.2446.90 2006-05-01 00:00:00.000
A2 29.12134.4690.2446.2069.34113.96 2006-05-01 00:00:00.000
[解决办法]
如果只有 '无 '小改一下就能满足要求
+[values]+ --------->
+replace([values], '无 ',0)+
[解决办法]
可以使用MSSQL自带的函数了ISNUMERIC(variable)判断下是否为数据,若不是等于0就是了
可以写成IIF(isnumeric(variale)=1,variable,0)
[解决办法]
学习中。。。
[解决办法]
负数应该一样的
[解决办法]
UP
[解决办法]
上面的修改都错了，重新贴
--加法函数
alter function fn_Plus(
@s1 varchar(50),
@s2 varchar(50)
)
returns varchar(50)
as
begin
if @s1 is null or @s1= ' '
return @s2
if @s2 is null or @s2= ' '
return @s1
declare @r varchar(50)
set @r= ' '
declare @f1 float
declare @f2 float
while charindex( ', ',@s1)> 0 and charindex( ', ',@s2)> 0
begin
set @f1=case when isnumeric(left(@s1,charindex( ', ',@s1)-1))=1 then cast(left(@s1,charindex( ', ',@s1)-1) as float) else 0 end
set @f2=case when isnumeric(left(@s2,charindex( ', ',@s2)-1))=1 then cast(left(@s2,charindex( ', ',@s2)-1) as float) else 0 end
set @s1=stuff(@s1,1,charindex( ', ',@s1), ' ')
set @s2=stuff(@s2,1,charindex( ', ',@s2), ' ')
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
end
if @s1 <> ' ' and @s2 <> ' '
set @f1=case when isnumeric(@s1)=1 then cast(@s1 as float) else 0 end
set @f2=case when isnumeric(@s2)=1 then cast(@s2 as float) else 0 end
set @r=@r+ ', '+cast(@f1+@f2 as varchar)
if @r <> ' '
set @r=stuff(@r,1,1, ' ')
return @r
end
go
--求平均值函数
alter function fn_ValuesAvg(
@cate varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
declare @t table (
[values] varchar(50)
)
insert @t select [values] from up where cate=@cate and dates=@dates
declare @t1 table (
No int,
[values] float
)
declare @No int
set @No=1
while exists (select 1 from @t where charindex( ', ',[values])> 0)
begin
insert @t1 select @No,
case when isnumeric(left([values],charindex( ', ',[values])-1))=1 then cast(left([values],charindex( ', ',[values])-1) as float) else 0 end
from @t
update @t set [values]=stuff([values],1,charindex( ', ',[values]), ' ')
set @No=@No+1
end
insert @t1 select @No,case when isnumeric([values])=1 then cast([values] as float) else 0 end from @t
select @r=@r+ ', '+cast(avg([Values]) as varchar) from @t1 group by No order by No
return stuff(@r,1,1, ' ')
end
go
[解决办法]
学习！
[解决办法]
学习学习 ！！！
[解决办法]
jf
[解决办法]
jf
[解决办法]
顶下关注
