积极答复者
每列的最后一个值查出!!!

问题
-
CREATE TABLE [dbo].[abc]( [id] [int] IDENTITY(1,1) NOT NULL, [D_Temp1] [nchar](10) NULL, [D_Temp2] [nchar](10) NULL, [D_Temp3] [nchar](10) NULL, [D_Temp4] [nchar](10) NULL, CONSTRAINT [PK_abc] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY], insert into abc values(N'森罗万/象尖7',null,null,null) insert into abc values(N'森罗万/象尖6','sdf',null,null) insert into abc values(N'森罗万/象尖5',null,'ddd',null) insert into abc values(N'森罗万/象尖4',null,null,'ddd') insert into abc values(N'森罗万/象尖3',null,null,null) insert into abc values(N'森罗万/象尖2',null,null,null) insert into abc values(N'森罗万/象尖1',null,null,null)
我想查出每列的最后一个值,但是我写的只能一列一列的查,
select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null)
select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null)
select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null)
select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)请问要怎么才可以将所有列的最后个个值查出
- 已编辑 张凯 2010年10月15日 0:51
答案
-
SQL2005以上版本可以row_number
;WITH Cte AS ( select * , row1=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp1 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC), row2=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp2 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC), row4=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp4 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC) from abc ) SELECT max(CASE WHEN row1=1 THEN [D_Temp1] END) AS [D_Temp1], max(CASE WHEN row2=1 THEN [D_Temp2] END) AS [D_Temp2], max(CASE WHEN row4=1 THEN [D_Temp4] END) AS [D_Temp4] FROM Cte WHERE 1 IN(row1,row2,row4) /* D_Temp1 D_Temp2 D_Temp4 森罗万/象尖1 sdf ddd */
SQL2000樓主要一個語句實現,把條件放到select 后用case when 處理
ROY WU(吳熹 )- 已标记为答案 张凯 2010年10月15日 6:42
全部回复
-
自己还是用
select * from (
select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null) ) a ,
(select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null) ) b,
( select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null) ) c ,
(select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)) d不是知道各位有没有更好的方法。
-
自己还是用
select * from (
select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null) ) a ,
(select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null) ) b,
( select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null) ) c ,
(select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)) d不是知道各位有没有更好的方法。
既然可以用这个, 那就可以用这个select D_Temp1 as a, D_Temp2 as b, D_Temp3 as c, D_Temp4 as d
from abc
where id = (select max(id) from abc where D_Temp1 is not null) -
SQL2005以上版本可以row_number
;WITH Cte AS ( select * , row1=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp1 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC), row2=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp2 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC), row4=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp4 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC) from abc ) SELECT max(CASE WHEN row1=1 THEN [D_Temp1] END) AS [D_Temp1], max(CASE WHEN row2=1 THEN [D_Temp2] END) AS [D_Temp2], max(CASE WHEN row4=1 THEN [D_Temp4] END) AS [D_Temp4] FROM Cte WHERE 1 IN(row1,row2,row4) /* D_Temp1 D_Temp2 D_Temp4 森罗万/象尖1 sdf ddd */
SQL2000樓主要一個語句實現,把條件放到select 后用case when 處理
ROY WU(吳熹 )- 已标记为答案 张凯 2010年10月15日 6:42