Go
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@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 avg(col)from @t)
end
godeclare @t table(a int,b int,c INT,[中值] int)
insert into @t select 1,2,3,null
insert into @t select 3,8,2 ,null
insert into @t select 8,5,4 ,NULL
--select * from @tupdate t set [中值]=dbo.UF_minget(a,b,c)
from @t t
select * from @t/*
a b c 中值
----------- ----------- ----------- -----------
1 2 3 2
3 8 2 4
8 5 4 5
*/
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@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 avg(col)from @t)
end
godeclare @t table(a int,b int,c INT,[中值] int)
insert into @t select 1,2,3,null
insert into @t select 3,8,2 ,null
insert into @t select 8,5,4 ,NULL
--select * from @tupdate t set [中值]=dbo.UF_minget(a,b,c)
from @t t
select * from @t/*
a b c 中值
----------- ----------- ----------- -----------
1 2 3 2
3 8 2 4
8 5 4 5
*/
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
*/
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怎么来的?
把逻辑用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
a>b>c =bb<a<c =a
b>a>c =ab<c<a =c
b>c>a =ca<b a<c b>c =c
a<b a<c b<c =bb<a b<c a>c =c
b<a b<c a<c =ac<a c<b a>b =b
c<a c<b a<b =a
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
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,4select (select top 1 x from
(select x,row_number() over(order by x) 'rn'
from (select a 'x' union all
select b 'x' union all
select c 'x') t) u
where rn=2) 'M'
from @t/*
M
-----------
2
3
5(3 row(s) affected)
*/
WITH a1 (a,b,c) AS
(
select 1,2,3 UNION ALL
select 3,8,2 UNION ALL
select 8,5,4
)
SELECT *,
CASE
WHEN a BETWEEN b AND c OR a BETWEEN c AND b THEN a
WHEN b BETWEEN a AND c OR b BETWEEN c AND a THEN b
ELSE c
end
FROM a1
4# sdhp 这逻辑我真没看懂,看着结果是对的
6# hwhmh2010 是好孩子
9# Beirut 屌丝,喜欢高大上
15# wyj23114 也是row_number思路,写法有点复杂,没看太懂
16# ap0405140 不错,构思很好
17# lzw_0736 简单粗暴感谢大家。
速度转八千分过来
楼主这个这个我只能说:“#18 perfectaction 的评价非常的屌丝!!”
好孩子是什么意思啊?
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 mu as (
select *
from (
select ROW_NUMBER() over(order by getdate()) as id,a,b,c
from @t
) t
),mu2 as (
select 'a' as type,id,a from mu
union all select 'b' as type,id,b from mu
union all select 'c' as type,id,c from mu
)
select *
from (
select
ROW_NUMBER() over(partition by id order by a) as num
,*
from mu2
) t where num=2
/*
2 b 1 2
2 a 2 3
2 b 3 5
*/
速度转八千分过来
8000分,你还该我20000分呢
row_number排序方法
xquery方法都可以解决问题,效率方面这种需求都要遍历,应该差不多,没测试。