怎么取中间数值，好难，想好几天了，在线等
declare @t table(a int,b int,c int)
insert into @t select 1,2,3
insert into @t select 3,8,2
insert into @t select 8,5,4
select * from @t
想要的结果：
2
3
4
------解决方案--------------------
第三行应该是5吧？
Go
if object_id('UF_avg')is not null drop function UF_avg
go
create function UF_avg
(@col1 int,@col2 int,@col3 int)
returns int
as
begin
declare @t table(col int)
insert @t select @col1 union all
select @col2 union all
select @col3
return(select col from @t WHERE col <(SELECT MAX(col) FROM @t) AND col>(SELECT MIN(col) FROM @t))
end
go
declare @t table(a int,b int,c INT)
insert into @t select 1,2,3
insert into @t select 3,8,2
insert into @t select 8,5,4
--select * from @t
select *,[中值]=dbo.UF_avg(a,b,c)
from @t
/*
a b c 中值
----------- ----------- ----------- -----------
1 2 3 2
3 8 2 3
8 5 4 5
*/
------解决方案--------------------
三个数的和减最大值减最小值
with t as(
select * from(
values(1,2,3),(3,8,2),(8,5,4)) t(a,b,c))
select a,b,c,a+b+c-case when a>b then case when a>c then a else c end else case when b>c then b else c end end
-case when a<b then case when a<c then a else c end else case when b<c then b else c end end
from t
第三个4怎么来的？
------解决方案--------------------
是每行的中间吗？如果是每行的中间应该是2,3,5 啊。
把逻辑用case when实现就好了
select case
when (
(a<b and b<c)
or (a>b and b>c)
or (a<b and a<c and b<c)
or (c<a and c<b and a>b)
) then b
when (
(b<a and a<c)
or(b>a and b>c)
or(b<a and b<c and a<c)
or(c<a and c<b and a<b)
) then a
when(
(b<c and c<a)
or(b>c and c>a)
or(b<a and b<c and a>c)
or(a<b and a<c and b>c)
) then c
end
from @t
------解决方案--------------------
SELECT tb.col.query('for $i in //row/* order by number($i) return $i').value('/*[2]', 'int')
FROM @t AS ta
CROSS APPLY (
SELECT col = (SELECT ta.* FOR XML PATH ('row'), TYPE)
) AS tb
------解决方案--------------------
declare @t table(a int,b int,c int)
insert into @t select 1,2,3
insert into @t select 3,8,2
insert into @t select 8,5,4;
with tb as(
select *,row=row_number()over(order by getdate()) from @t
)
select a,b,c,col as '中间' from(
select a,b,c,col,row,row_2=row_number()over(partition by row order by col desc) from(
select a,b,c,a as col,row from tb
union all select a,b,c,b,row from tb
union all select a,b,c,c,row from tb)t)tt where row_2=2
------解决方案--------------------