22,209
社区成员
发帖
与我相关
我的任务
分享
-- 表
create table table1(name varchar,Ysum,Qsum,Msum,Dsum)
insert table1 (name,Ysum,Qsum,Msum,Dsum)
(
select d1,450,120,58,3 union all
select d2,640,180,null,null union all
select d3,null,279,null,4 union all
select d4,null,null,58,9
)
假如我想top 3
则取d1,450,120,58,3,
d2,640,180,58,4
d3,null,279,null,9
也就是说,第一行为null的就取同列中的第二行,如果第二行还为null则取同列中的第三行!
有没有此函数?
create table table1(name varchar(2),Ysum int,Qsum int ,Msum int,Dsum int)
drop table table1
insert table1 (name,Ysum,Qsum,Msum,Dsum)
(
select 'd1',450,120,58,3 union all
select 'd2',640,180,null,null union all
select 'd3',null,279,null,4 union all
select 'd4',null,null,58,9 union all
select 'd5',6,null,60,10
)
--一个笨办法,假设本条为空,最多向下面取两次,就能得到想要值,
--不止两次的化,如果确认在有限的几次内能取到就再改改,否则不适合本方法
select top 3 isnull(a.name,isnull(b.name,c.name)),
isnull(a.Ysum,isnull(b.Ysum,c.Ysum)),
isnull(a.Qsum,isnull(b.Qsum,c.Qsum)),
isnull(a.Msum,isnull(b.Msum,c.Msum)),
isnull(a.Dsum,isnull(b.Dsum,c.Dsum))
from table1 a,table1 b,table1 c
where b.name=(select min(name) from table1 where table1.name>a.name)
and c.name=(select min(name) from table1 where table1.name>b.name)
/*
---- ----------- ----------- ----------- -----------
d1 450 120 58 3
d2 640 180 58 4
d3 6 279 58 4
(所影响的行数为 3 行)
*/