select sname,max(v1) from (
select Sname , V1 from tb
union all
select Sname , V2 from tb
union all
select Sname , V3 from tb
union all
select Sname , V4 from tb
union all
select Sname , V5 from tb
union all
select Sname , V6 from tb
)t group by sname
select Sname , V1 from tb
union all
select Sname , V2 from tb
union all
select Sname , V3 from tb
union all
select Sname , V4 from tb
union all
select Sname , V5 from tb
union all
select Sname , V6 from tb
)t group by sname
解决方案 »
- 要怎样查询两个不同条件的Sum(金额)结果
- 纯新手,写个触发器,变量定义报错
- sql6.5 还原数据时候报:single-user mode 错误.提供解决方法者再送100 分
- 如何置换列名!
- 我想根据银行对帐单中的汇款单位追加原库中没有的单位名称,并根据对帐单制作凭证,谁能帮忙。我现在用的是用友通精算版
- 为什么在删除表中字段的时候出现这样的提示 对象 df_shouquan_L1003_0A9D95DB 依赖于列 L1003
- 關于后台資料庫一問?
- 在SQL 7.0 中 :create table #temp select * from works 错在那里?
- SQL语句
- 搜索算法问题
- 相当有难度的问题,求每行记录的最大值
- 100分求:sql server 2005 复制 订阅服务器的权限怎么配置?
insert @t select N'张三', 0.11 , 0.21 , 0.29, 0.32 , 0.11, 0.08
insert @t select N'李四', 0.01 , 0.61 , 0.21, 0.73 , 0.21, 0.12
insert @t select N'张五', 0.31 , 0.21 , 0.23, 0.33 , 0.91, 0.65
insert @t select N'张六', 0.59 , 0.11, 0.26, 0.13, 0.01, 0.15
select sname,max(v1) from (
select Sname , V1 from @t
union all
select Sname , V2 from @t
union all
select Sname , V3 from @t
union all
select Sname , V4 from @t
union all
select Sname , V5 from @t
union all
select Sname , V6 from @t
)t group by sname
/*
sname
----- -----------------------------------------------------
李四 0.72999999999999998
张三 0.32000000000000001
张五 0.91000000000000003
张六 0.58999999999999997
*/
create table tb
(
Sname nvarchar(50),
V1 decimal(10,2),
V2 decimal(10,2),
V3 decimal(10,2),
V4 decimal(10,2),
V5 decimal(10,2),
V6 decimal(10,2)
)insert into tb
select '张三', 0.11, 0.21, 0.29, 0.32, 0.11, 0.08 union all
select '李四', 0.01, 0.61, 0.21, 0.73, 0.21, 0.12 union all
select '张五', 0.31, 0.21, 0.23, 0.33, 0.91, 0.65 union all
select '张六', 0.59, 0.11, 0.26, 0.13, 0.01, 0.15 select
sname,max(v1) as 最大值 from (
select Sname , V1 from tb union all
select Sname , V2 from tb union all
select Sname , V3 from tb union all
select Sname , V4 from tb union all
select Sname , V5 from tb union all
select Sname , V6 from tb
)t group by snamedrop table tb
结果:sname 最大值
--------------
李四 0.73
张六 0.59
张三 0.32
张五 0.91
returns float
as
begin
declare @a table(v float)
insert @a select @v1 union all select @v2 union all select @v3 union all select @v4 union all select @v5 union all select @v6
return (SELECT max(v) FROM @a)
ENDgo
select Sname,dbo.getF(v1,v2,v3,v4,v5,v6) from [Table]
GO
--建立自定义函数IIF
CREATE FUNCTION IIF
(
@NUM1 FLOAT,
@NUM2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @MAXNUM FLOAT
SET @MAXNUM=(CASE WHEN @NUM1 IS NULL AND @NUM2 IS NULL THEN NULL
WHEN @NUM1 IS NULL AND @NUM2 IS NOT NULL THEN @NUM2
WHEN @NUM1 IS NOT NULL AND @NUM2 IS NULL THEN @NUM1
WHEN @NUM1 IS NOT NULL AND @NUM2 IS NOT NULL THEN (CASE WHEN @NUM1>@NUM2 THEN @NUM1 ELSE @NUM2 END )
END)
RETURN @MAXNUM
END
GO--建立测试数据
CREATE TABLE TB(Sname NVARCHAR(10),V1 FLOAT,V2 FLOAT,V3 FLOAT,V4 FLOAT,V5 FLOAT,V6 FLOAT)
INSERT INTO TB
SELECT N'张三',0.11,0.21,0.29,0.32,0.11,0.08 UNION ALL
SELECT N'李四',0.01,0.61,0.21,0.73,0.21,0.12 UNION ALL
SELECT N'张五',0.31,0.21,0.23,0.33,0.91,0.65 UNION ALL
SELECT N'张六',0.59,0.11,0.26,0.13,0.01,0.15 --查询数据
SELECT Sname,VMAX=[Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] (V1,V2),V3),V4),V5),V6)
FROM TB--删除测试数据
DROP TABLE TB--测试结果:
/*(4 個資料列受到影響)
Sname VMAX
---------- ----------------------
张三 0.32
李四 0.73
张五 0.91
张六 0.59(4 個資料列受到影響)*/