比较三个数的取最小值

[zhenshz]

我现在有三个字段进行比较要取最小只，有没有什么好的方法，比较简便的
我写的代码是 select case when a>B and B>c then c
when A>c and c>B then B
when B>a and a>c then c
when B>c and c>a then a
when c>a and a>b then b
when c>B and B>a then a
、、
end from table1

有没有什么办法简便一下

[中国风]

select
(
select min(a) 
from 
(select a  union all
select b union all
select c
)t)
from table1

[中国风]

select ID as 主健,min(a)
from (select ID,a from table1
union all
select ID,b from table1
union all
select ID,c from table1
)t
group by ID

有主健時也可這樣用

[zhenshz]

[Quote=引用 2 楼 roy_88 的回复:]
SQL code

select ID as 主健,min(a)
from (select ID,a from table1
union all
select ID,b from table1
union all
select ID,c from table1
)t
group by ID


有主健時也可這樣用
[/Quote]

没有主键，也不能用union ，好像是有一个两个差还是两个和相比较的，具体也忘了，有没有只用case when 但是不要那么多的？

[中国风]

沒主健用方法1

或
簡化一下你的代碼

select 
	case when a>=C and B>=c then c
	  when c>=b and a>=b  then B 
	  ELSE a end

[koumingjie]

create function getMin(@x int,@y int,@z int)
returns int
as
begin

 declare @temp int
 declare @min int 
        
    if(@x<@y) 
        set @temp=@x
    else 
        set @temp=@y
    
    if(@temp<@z) 
       set @min= @temp
    else 
       set @min=@z
       
 return @min

end

go



create table tb
(a int,b int,c int)

insert into tb 
select 1,2,3 union all
select 6,5,4 union all
select 8,7,9

select dbo.getMin(a,b,c) from tb

[q465897859]

[Quote=引用 4 楼 roy_88 的回复:]
沒主健用方法1

或
簡化一下你的代碼

SQL code

select
case when a>=C and B>=c then c
when c>=b and a>=b then B
ELSE a end
[/Quote]+1

[q465897859]

大版V5 lz愚笨

[zhenshz]

楼上的拖出去砍了



还有谢谢版主的，问题解决了

[--小F--]

/*

lvl1  lvl2    lvl3    lvl4    lvl 
4      3      4      1        
3      2      2      1    
2      2      3      4 
4      4      3      4 
3      1      2      2 
怎么写代码 去比较lvl1、lvl2、lvl3、lvl4 对应每行的值，取其中最小的，将其值添加到lvl列里 
运行结果应该是 
lvl 
1 
1 
2 
3 
1

*/

--方法(一) 函數法

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16

if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@col1 int,@col2 int,@col3 int,@col4 int)
returns int
as
  begin
     declare @t table(col int)
     insert @t select @col1 union all
               select @col2 union all
               select @col3 union all
               select @col4
     return(select min(col)from @t)
  end
go
update t set [lvl]=dbo.UF_minget([lvl1],[lvl2],[lvl3],[lvl4])
from #t t
select * from #t
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1

(5 個資料列受到影響)


*/

--方法二  MSSQL2005 XML PATH

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-10-16 09:57:38
-------------------------------------

--> 生成测试数据: @T
DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)
INSERT INTO @T
SELECT 4,3,4,1,null UNION ALL
SELECT 3,2,2,1,null UNION ALL
SELECT 2,2,3,4,null UNION ALL
SELECT 4,4,3,4,null UNION ALL
SELECT 3,1,2,2,null

--SQL查询如下:

UPDATE A SET
    lvl = B.x.value('min(//row/*)','int')
FROM @T AS A
    CROSS APPLY (SELECT x = (SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
    
SELECT * FROM @T;

/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1

(5 行受影响)

*/
--方法(三) 作者 (四方城) 

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
insert [tb]
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
go

create function getmin(@a varchar(8000))   
  returns int   
  as   
  begin declare @ table (id int identity,a char(1))   
      declare @t int   
      insert @ select top 8000 null from sysobjects a,sysobjects b   
      select @t=min(cast(substring(','+@a,id+1,charindex(',',','+@a+',',id+1)-id-1) as int))   
      from @ where substring(','+@a,id,8000) like ',_%'   
      return @t   
  end   
go

-->查询
select 
  lvl1,
  lvl2,
  lvl3,
  lvl4,
  lvl=dbo.getmin(ltrim(lvl1)+','+ltrim(lvl2)+','+ltrim(lvl3)+','+ltrim(lvl4))
from tb

/**
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1

(5 行受影响)
**/

--方法(四)

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16

if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go

if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
  begin
  return(
    select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
    from master..spt_values
    where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
  end
go
select 
  [lvl1],
  [lvl2],
  [lvl3],
  [lvl4],
  [lvl]=dbo.UF_minget(ltrim([lvl1])+','+ltrim([lvl2])+','+ltrim([lvl3])+','+ltrim([lvl4]))
from #T
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1

*/

--方法(五)

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
select [lvl1],
       [lvl2],
       [lvl3],
       [lvl4],
       [lvl]=(select min([lvl1])
              from (select [lvl1] 
                  union all select [lvl2] 
                  union all select [lvl3] 
                  union all select [lvl4])T)
from #t
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1

(5 個資料列受到影響)
*/