declare @t table(id int,S1 int,S2 int,S3 int,S4 int,S5 int,max_s int)
insert into @t select 1,23,25,24,26,25,null
insert into @t select 2,23,22,24,23,22,null
insert into @t select 3,20,21,20,24,23,nulldeclare @max_s int
update @t
set
@max_s=S1,
@max_s=case when S2>@max_s then S2 else @max_s end,
@max_s=case when S2>@max_s then S3 else @max_s end,
@max_s=case when S2>@max_s then S4 else @max_s end,
@max_s=case when S2>@max_s then S5 else @max_s end,
max_s =@max_sselect * from @t/*
id S1 S2 S3 S4 S5 max_s
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 23 25 24 26 25 26
2 23 22 24 23 22 24
3 20 21 20 24 23 24
*/
insert into @t select 1,23,25,24,26,25,null
insert into @t select 2,23,22,24,23,22,null
insert into @t select 3,20,21,20,24,23,nulldeclare @max_s int
update @t
set
@max_s=S1,
@max_s=case when S2>@max_s then S2 else @max_s end,
@max_s=case when S2>@max_s then S3 else @max_s end,
@max_s=case when S2>@max_s then S4 else @max_s end,
@max_s=case when S2>@max_s then S5 else @max_s end,
max_s =@max_sselect * from @t/*
id S1 S2 S3 S4 S5 max_s
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 23 25 24 26 25 26
2 23 22 24 23 22 24
3 20 21 20 24 23 24
*/
------------------------------------------------------------------------------------------------------------------------------
declare @t table(id int,S1 int,S2 int,S3 int,S4 int,S5 int,max_s int)
insert into @t select 1,23,25,24,26,25,null
insert into @t select 2,23,22,24,23,22,null
insert into @t select 3,20,21,20,24,23,nulldeclare @max_s int
update @t
set
@max_s=S1,
@max_s=case when S2>@max_s then S2 else @max_s end,
@max_s=case when S3>@max_s then S3 else @max_s end,
@max_s=case when S4>@max_s then S4 else @max_s end,
@max_s=case when S5>@max_s then S5 else @max_s end,
max_s =@max_sselect * from @t/*
id S1 S2 S3 S4 S5 max_s
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 23 25 24 26 25 26
2 23 22 24 23 22 24
3 20 21 20 24 23 24
*/
(id int,S1 int,S2 int,S3 int,S4 int,S5 int,max_s int)
insert @tselect 1,23,25,24,26,25,null union all
select 2,23,22,24,23,22,null union all
select 3,20,21,20,24,23,nullupdate @t set max_s=
(select max(s) from
(select id,s1 as s from @t
union all
select id,s2 from @t
union all
select id,s3 from @t
union all
select id,s4 from @t
union all
select id,s5 from @t) a where a.id=b.id)
from @t bselect * from @tid S1 S2 S3 S4 S5 max_s
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 23 25 24 26 25 26
2 23 22 24 23 22 24
3 20 21 20 24 23 24(所影响的行数为 3 行)
insert tt(id,s1,s2,s3,s4,s5)
select 1, 23, 25 , 24 ,26 ,25 union all
select 2, 23, 22 , 24 , 23, 22
go
create function dd(
@id int)returns int
as
begin
declare @a int
declare @t table(a int)
insert @t
select s1 from tt where id=@id
union all
select s2 from tt where id=@id
union all
select s3 from tt where id=@id
union all
select s4 from tt where id=@id
union all
select s5 from tt where id=@idselect @a=max(a) from @t
return(@a)
end
goupdate tt set max_s=dbo.dd(id)select * from tt
我想利用触发器实现,当S1-S5之间任一个值更改或是插入一条记录里,R跟差变.
insert into @t select 1,23,25,24,26,25,null
insert into @t select 2,23,22,24,23,22,null
insert into @t select 3,20,21,20,24,23,nullselect T.id,T.S1,T.S2,T.S3,T.S4,T.S5,
( select max(S)
from
(
select id, S1 as S from @t union
select id, S2 as S from @t union
select id, S3 as S from @t union
select id, S4 as S from @t union
select id, S5 as S from @t
) M where M.id=T.id
) as max_s
from @t T
如:
/*
id S1 S2 S3 S4 S5 max_s
------- ------- ------- ------- ------- ------- -------
1 23 25 24 26 25 26
2 23 22 24 23 22 24
3 20 21 20 24 23 24
*/变成如下的样式显示:
/*
id 1 2 3
----- ----- ----- -----
S1 23 23
S2 25 22
S3 24 24
S4 25 23
S5 26 22
max_s 26 24
*/
(id int,S1 int,S2 int,S3 int,S4 int,S5 int,max_s int)insert t
select 1,23,25,24,26,25,null union all
select 2,23,22,24,23,22,null union all
select 3,20,21,20,24,23,nullupdate t set max_s=
(select max(s) from
(select id,s1 as s from t
union all
select id,s2 from t
union all
select id,s3 from t
union all
select id,s4 from t
union all
select id,s5 from t) a where a.id=b.id)
from t bselect * from t/*--------------固定行方法------------*/
select id,isnull(sum(case when t_id=1 then s end),0) as [1],
isnull(sum(case when t_id=2 then s end),0) as [2],
isnull(sum(case when t_id=3 then s end),0) as [3]
from
(
select t_id=id,id='s1',s1 as s from t
union all
select t_id=id,id='s2',s2 from t
union all
select t_id=id,id='s3',s3 from t
union all
select t_id=id,id='s4',s4 from t
union all
select t_id=id,id='s5',s5 from t
union all
select t_id=id,id='max_s',max_s from t
) a group by id order by case id when 'max_s' then 2 else 1 end asc
/*----------------动态方法------------------*/
declare @sql varchar(8000),@i int
select @i=1,@sql='select id'
select @sql=@sql+',['+cast(@i as varchar)+']=isnull(sum(case when t_id='+cast(@i as varchar)+' then s end),0)'
,@i=@i+1 from t group by id
select @sql=@sql+'
from
(
select t_id=id,id=''s1'',s1 as s from t
union all
select t_id=id,id=''s2'',s2 from t
union all
select t_id=id,id=''s3'',s3 from t
union all
select t_id=id,id=''s4'',s4 from t
union all
select t_id=id,id=''s5'',s5 from t
union all
select t_id=id,id=''max_s'',max_s from t
) a group by id order by case id when ''max_s'' then 2 else 1 end asc'
exec(@sql)drop table t
id 1 2 3
----- ----------- ----------- -----------
s1 23 23 20
s2 25 22 21
s3 24 24 20
s4 26 23 24
s5 25 22 23
max_s 26 24 24警告: 聚合或其它 SET 操作消除了空值。
请问"警告: 聚合或其它 SET 操作消除了空值。"是什么意思?
我运行的时候没有错误的
最后一个@sql会不会把前面的@sql给复盖了?