请问下,有个表叫随便TIMETEST吧,有3个时间字段:AT,BT,CT。怎么select出一个伪列NEWT,让它的值等于这3个字段中的最大值?
AT BT CT NEWT
2010-01-01 2010-01-02 2010-01-03 2010-01-03
2010-01-04 2010-01-02 2010-01-03 2010-01-04
2010-01-01 null null 2010-01-05
2010-01-01 2010-01-02 null 2010-01-02谢谢各位了
AT BT CT NEWT
2010-01-01 2010-01-02 2010-01-03 2010-01-03
2010-01-04 2010-01-02 2010-01-03 2010-01-04
2010-01-01 null null 2010-01-05
2010-01-01 2010-01-02 null 2010-01-02谢谢各位了
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:16if 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
gocreate 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:16if 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
Goif 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 個資料列受到影響)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/16/4682241.aspx
select AT, BT, CT
,case when AT >= BT and AT >= CT and AT is not null then AT
when BT >= CT and BT >= AT and BT is not null then BT
when CT >= BT and CT >= AT and CT is not null then CT
else null
end as NEWT
from timetest
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2010-03-25 16:05:13
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (AT DATETIME,BT DATETIME,CT DATETIME)
INSERT INTO [tb]
SELECT '2010-01-01','2010-01-02','2010-01-03' UNION ALL
SELECT '2010-01-04','2010-01-02','2010-01-03' UNION ALL
SELECT '2010-01-01',null,null UNION ALL
SELECT '2010-01-01','2010-01-02',nullselect * from [tb]
select at,bt,ct,newt=case when isnull(ct,'')>isnull(newt,'') then ct else newt end
from
(select *,newt=case when isnull(at,'')>isnull(bt,'') then at else bt end
from tb)Kat bt ct newt
2010-01-01 00:00:00.000 2010-01-02 00:00:00.000 2010-01-03 00:00:00.000 2010-01-03 00:00:00.000
2010-01-04 00:00:00.000 2010-01-02 00:00:00.000 2010-01-03 00:00:00.000 2010-01-04 00:00:00.000
2010-01-01 00:00:00.000 NULL NULL 2010-01-01 00:00:00.000
2010-01-01 00:00:00.000 2010-01-02 00:00:00.000 NULL 2010-01-02 00:00:00.000
貌似我犯错了,我JT太快了。不好意思啊。还是很感谢你。