例如:
a b c d
1 2 0 4
0.5 0 0.8 1.6
0 7 4 5
1.5 2.1 3.4 0.9
0 0 5.1 3.9
4 0 0 0
0 0 0 0
其中每一列都可能存在0 并且有可能出现 每行中 2 列、3列、4列数据都为0 的情况。请问,利用SQL语句通过什么方法能够做到???如何做到?
a b c d
1 2 0 4
0.5 0 0.8 1.6
0 7 4 5
1.5 2.1 3.4 0.9
0 0 5.1 3.9
4 0 0 0
0 0 0 0
其中每一列都可能存在0 并且有可能出现 每行中 2 列、3列、4列数据都为0 的情况。请问,利用SQL语句通过什么方法能够做到???如何做到?
select min(a) as a,
min(b) as b,
min(c) as c,
min(d) as d
from tb所有列最小:
select min(val) from
(
select a as val from tb
union all
select b as val from tb
union all
select c as val from tb
union all
select d as val from tb
union all
) t
(
select id , a as val from tb where a <> 0
union all
select id , b as val from tb where b <> 0
union all
select id , c as val from tb where c <> 0
union all
select id , d as val from tb where d <> 0
) t
group by id
然后select maxFrom4(a,b,b,d) from yourtable可以得到每列的做大值
drop table tb
gocreate table tb
(
a decimal(18,1),
b decimal(18,1),
c decimal(18,1),
d decimal(18,1)
)insert into tb(a,b,c,d) values(1 ,2 ,0 ,4)
insert into tb(a,b,c,d) values(0.5 ,0 ,0.8 ,1.6)
insert into tb(a,b,c,d) values(0 ,7 ,4 ,5)
insert into tb(a,b,c,d) values(1.5 ,2.1 ,3.4 ,0.9)
insert into tb(a,b,c,d) values(0 ,0 ,5.1 ,3.9)
insert into tb(a,b,c,d) values(4 ,0 ,0 ,0 )
insert into tb(a,b,c,d) values(0 ,0 ,0 ,0)select id=identity(int,1,1),* into #tmp from tbselect id as 行 , min(val) as 最小值 from
(
select id , a as val from #tmp where a <> 0
union all
select id , b as val from #tmp where b <> 0
union all
select id , c as val from #tmp where c <> 0
union all
select id , d as val from #tmp where d <> 0
) t
group by iddrop table tb,#tmp行 最小值
----------- --------------------
1 1.0
2 .5
3 4.0
4 .9
5 3.9
6 4.0(所影响的行数为 6 行)
有4个列 a、b、c、d
如下:
a b c d
1 2 0 4
0.5 0 0.8 1.6
0 7 4 5
1.5 2.1 3.4 0.9
0 0 5.1 3.9
4 0 0 0
0 0 0 0 通过SQL语句查询得到如下结果:
a b c d 最小值
1 2 0 4 1
0.5 0 0.8 1.6 0.5
0 7 4 5 4
1.5 2.1 3.4 0.9 0.9
0 0 5.1 3.9 3.9
4 0 0 0 4
0 0 0 0 0
drop table tb
gocreate table tb
(
a decimal(18,1),
b decimal(18,1),
c decimal(18,1),
d decimal(18,1)
)insert into tb(a,b,c,d) values(1 ,2 ,0 ,4)
insert into tb(a,b,c,d) values(0.5 ,0 ,0.8 ,1.6)
insert into tb(a,b,c,d) values(0 ,7 ,4 ,5)
insert into tb(a,b,c,d) values(1.5 ,2.1 ,3.4 ,0.9)
insert into tb(a,b,c,d) values(0 ,0 ,5.1 ,3.9)
insert into tb(a,b,c,d) values(4 ,0 ,0 ,0 )
insert into tb(a,b,c,d) values(0 ,0 ,0 ,0)select id=identity(int,1,1),* into #tmp from tbselect id as 行 , min(val) as 最小值 from
(
select id , a as val from #tmp where a <> 0
union all
select id , b as val from #tmp where b <> 0
union all
select id , c as val from #tmp where c <> 0
union all
select id , d as val from #tmp where d <> 0
) t
group by id
union all
select id as 行, 最小值 = 0 from #tmp where a=0 and b=0 and c=0 and d=0drop table tb,#tmp行 最小值
----------- --------------------
1 1.0
2 .5
3 4.0
4 .9
5 3.9
6 4.0
7 .0(所影响的行数为 7 行)
if exists(select [name] from sysobjects where [name] = 'tb')
drop table tb
gocreate table tb
(
a decimal(9,1),
b decimal(9,1),
c decimal(9,1),
d decimal(9,1)
)insert into tb(a,b,c,d)
select 1 ,2 ,0 ,4
union all
select 0.5 ,0 ,0.8 ,1.6
union all
select 0 ,7 ,4 ,5
union all
select 1.5 ,2.1 ,3.4 ,0.9
union all
select 0 ,0 ,5.1 ,3.9union all
select 4 ,0 ,0 ,0
union all
select 0 ,0 ,0 ,0select id = identity(int,1,1),* into #temp from tb
select id as 行, 'min_a' + convert(varchar,a) as 最小值, a, b, c, d from #temp where a = (select min(a) from #temp where a<>0)union all
select id as 行, 'min_b' + convert(varchar,b) as 最小值, a, b, c, d from #temp where b = (select min(b) from #temp where b<>0)union all
select id as 行, 'min_c' + convert(varchar,c) as 最小值, a, b, c, d from #temp where c = (select min(c) from #temp where c<>0)
union all
select id as 行, 'min_d' + convert(varchar,d) as 最小值, a, b, c, d from #temp where d = (select min(d) from #temp where d<>0)order by iddrop table #temp
if exists(select [name] from sysobjects where [name] = 'tb')
drop table tb
gocreate table tb
(
a decimal(9,1),
b decimal(9,1),
c decimal(9,1),
d decimal(9,1)
)insert into tb
select 1 ,2 ,0 ,4
union all
select 0.5 ,0 ,0.8 ,1.6
union all
select 0 ,7 ,4 ,5
union all
select 1.5 ,2.1 ,3.4 ,0.9
union all
select 0 ,0 ,5.1 ,3.9
union all
select 4 ,0 ,0 ,0
union all
select 0 ,0 ,0 ,0select id = identity(int,1,1),* into #temp from tb
select id as 行, 'min_a is ' + convert(varchar, a) as 最小值, a, b, c, d from #temp where a = (select min(a) from #temp where a<>0)
union all
select id as 行, 'min_b is ' + convert(varchar, b) as 最小值, a, b, c, d from #temp where b = (select min(b) from #temp where b<>0)
union all
select id as 行, 'min_c is ' + convert(varchar, c) as 最小值, a, b, c, d from #temp where c = (select min(c) from #temp where c<>0)
union all
select id as 行, 'min_d is ' + convert(varchar, d) as 最小值, a, b, c, d from #temp where d = (select min(d) from #temp where d<>0)
union all
select id as 行, convert(varchar, 0) as 最小值, a, b, c, d from #temp where a = 0 and b = 0 and c = 0 and d = 0
order by iddrop table #temp
a.a,
a.b,
a.c,
a.d,
最小值 = select isnull(min(key),0)
from
( select a as key from 表 b
where b.a=a.a and b.b=a.b and b.c=a.c and b.d=a.d and b.a<>0
union
select b as key from 表 b
where b.a=a.a and b.b=a.b and b.c=a.c and b.d=a.d and b.b<>0
union
select c as key from 表 b
where b.a=a.a and b.b=a.b and b.c=a.c and b.d=a.d and b.c<>0
union
select d as key from 表 b
where b.a=a.a and b.b=a.b and b.c=a.c and b.d=a.d and b.d<>0) c
from
表 a
select 1,2,0,4
union all select 0.5,0,0.8,1.6
union all select 0,7,4,5
union all select 1.5,2.1,3.4,0.9
union all select 0,0,5.1,3.9
union all select 4,0,0,0
union all select 0,0,0,0
CREATE FUNCTION fun_compare(@first decimal(18,2),@second decimal(18,2))
RETURNS decimal(18,2) AS
BEGIN
declare @min decimal(18,2)
if (@first = 0 and @second = 0)
begin
set @min = 0
end
if (@first < @second)
begin
set @min = @first
end
else
begin
set @min = @second
end
return (@min)
ENDCREATE FUNCTION fun_getmin(@a decimal(18,2),@b decimal(18,2),@c decimal(18,2),@d decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
declare @min decimal(18,2)
if (@a = 0 and @b = 0 and @c = 0 and @d = 0)
begin
set @min = 0
end
else
begin
if (@a = 0 and @b <> 0) set @min = @b
if (@a <> 0 and @b = 0) set @min = @a
if (@a <> 0 and @b <> 0) set @min = dbo.fun_compare(@a,@b)
if (@c <> 0 and @min = 0) set @min = @c
if (@c <> 0 and @min <> 0) set @min = dbo.fun_compare(@min,@c)
if (@d <> 0 and @min = 0) set @min = @d
if (@d <> 0 and @min <> 0) set @min = dbo.fun_compare(@min,@d)
--set @min = 1
end
RETURN(@min)
END
select a,b,c,d,dbo.fun_getmin(a,b,c,d) as c_min from T3