需要实现从SQL数据表中读取X行Y列中最新录入的数据?如果x行y列数据为空,需要显示x-1行中y列的数据,而且要不断的刷新数据,以保证读取的数据都是列中最新录入的?
如:数据表为
col1 col2 col3 col4 col5 col6
9 8 7 null 5 4
3 null 2 1 9 null
7 6 null 5 null 4
null 3 null null null null
怎样查询可以得到列中相对最新而且不为空的数据,上例中为7 3 2 5 9 4 ,也就是说如何运用查询能从上例表中查询得到一行相对列中最新的数据7 3 2 5 9 4的数据结果?而且能不能实现数据的刷新,当数据表的数据改变时,结果中的数据能随之更新?
相信这是一个值得大家探讨的问题,请帮帮忙,谢谢。
如:数据表为
col1 col2 col3 col4 col5 col6
9 8 7 null 5 4
3 null 2 1 9 null
7 6 null 5 null 4
null 3 null null null null
怎样查询可以得到列中相对最新而且不为空的数据,上例中为7 3 2 5 9 4 ,也就是说如何运用查询能从上例表中查询得到一行相对列中最新的数据7 3 2 5 9 4的数据结果?而且能不能实现数据的刷新,当数据表的数据改变时,结果中的数据能随之更新?
相信这是一个值得大家探讨的问题,请帮帮忙,谢谢。
declare @col1 int,@col2 int,@col3 int,@col4 int, @col5 int,@col6 int
select @col1=case when col1 is not null then col1 else @col1 end,
@col2=case when col2 is not null then col2 else @col2 end,
@col3=case when col3 is not null then col3 else @col3 end,
@col4=case when col4 is not null then col4 else @col4 end,
@col5=case when col5 is not null then col5 else @col5 end,
@col6=case when col6 is not null then col6 else @col6 end
from (
select
9 as col1, 8 as col2, 7 as col3, cast(null as int) as col4, 5 as col5, 4 as col6
union all select
3, null, 2 , 1 , 9 , null
union all select
7 , 6 , null, 5 , null, 4
union all select
null, 3 , null, null, null, null
) as tselect @col1 as col1,@col2 as col2,@col3 as col3,@col4 as col4, @col5 as col5,@col6 as col6--结果
col1 col2 col3 col4 col5 col6
----------- ----------- ----------- ----------- ----------- -----------
7 3 2 5 9 4(所影响的行数为 1 行)
insert tb select 9, 8, 7, null, 5, 4
union all select 3, null, 2, 1, 9, null
union all select 7, 6, null, 5, null, 4
union all select null, 3, null, null, null, nullselect ID=identity(int, 1, 1), * into #t from tbselect col1=(select top 1 col1 from #t where col1 is not null order by ID desc),
col2=(select top 1 col2 from #t where col2 is not null order by ID desc),
col3=(select top 1 col3 from #t where col3 is not null order by ID desc),
col4=(select top 1 col4 from #t where col4 is not null order by ID desc),
col5=(select top 1 col5 from #t where col5 is not null order by ID desc),
col6=(select top 1 col6 from #t where col6 is not null order by ID desc)drop table #t
drop table tb--result
col1 col2 col3 col4 col5 col6
----------- ----------- ----------- ----------- ----------- -----------
7 3 2 5 9 4(1 row(s) affected)